Greenplum Vacuum Lock 測試
本文作者:Ren
測試一
1.先進行delete 再進行 vacuum analyze
2.表單 t2
gpadmin=# select * from t2 limit 1000; i | c ----------+------- 25674464 | xxxxx 25674466 | xxxxx 25674468 | xxxxx 25674470 | xxxxx 25674472 | xxxxx 25674474 | xxxxx 25674476 | xxxxx 25674478 | xxxxx 25674480 | xxxxx 25674482 | xxxxx 25674484 | xxxxx 25674486 | xxxxx 25674488 | xxxxx 25674490 | xxxxx 25674492 | xxxxx 25674494 | xxxxx 25674497 | xxxxx 25674499 | xxxxx
3.先做delete t2
gpadmin=# delete from t2 where c='xxxxx';
4.再執行 vacuum analyze t2
gpadmin=# vacuum analyze t2;
會發現 vacuum analyze 作業等待中
gpadmin=# select * from pg_stat_activity where waiting = 't'; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start | waiting_reason -------+---------+---------+---------+----------+---------+--------------------+ 30622 | gpadmin | 13422 | 65855 | 10 | gpadmin | vacuum analyze t2; | t | 2016-04-06 20:09:09.822948+08 | 2016-04-06 20:07:06.2235+08 | | | psql | 2016-04-06 20:09:09.822948+08 | lock (1 row)
測試二 先進行 vacuum analyze,再進行 delete
結果為 delete 作業等待
gpadmin=# select * from pg_stat_activity where waiting = 't'; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start | waiting_reason -------+---------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+--------------------------------------+------------------------------------- 30622 | gpadmin | 13241 | 65834 | 10 | gpadmin | delete from t2 where c='xxxxx'; | t 2016-04-06 20:11:46.766068+08 | 2016-04-06 20:06:23.696965+08 | | -1 | psql | 2016-04-06 20:11:46.766068+08 | lock (1 row)