Greenplum Vacuum Lock 測試

Greenplum 如何確認暫存表
2018-02-06
EDB Postgres Advanced Server 9.6 新功能
2018-02-22

本文作者: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)