ablog

不器用で落着きのない技術者のメモ

VACUUM FULL 実行中のロックのとりかた

AWS PostgreSQL

Aurora PostgreSQL で VACUUM FULL 実行中のロックのとりかたを確認したメモ。

  • Writer で VACUUM FULL 実行
postgres=> vacuum full pgbench_accounts;
  • Writer で VACUUM FULL 実行中のテーブルを参照
postgres=> select count(*) from pgbench_accounts;
# VACUUM FULL 実行中のため待たされる
  • Reader で VACUUM FULL 実行中のテーブルを参照
postgres=> select count(*) from pgbench_accounts;
# VACUUM FULL 実行中のため待たされる
  • Writer でロックの保持状況を確認
postgres=> SELECT l.pid,l.granted,d.datname,l.locktype,relation,relation::regclass,transactionid,l.mode
    FROM pg_locks l  LEFT JOIN pg_database d ON l.database = d.oid
    WHERE  l.pid != pg_backend_pid()
    ORDER BY l.pid;
  pid  | granted | datname  |   locktype    | relation |     relation     | transactionid |        mode
-------+---------+----------+---------------+----------+------------------+---------------+---------------------
  1675 | t       |          | virtualxid    |          |                  |               | ExclusiveLock
  1675 | f       | writer   | relation      |    20503 | pgbench_accounts |               | AccessShareLock
 28881 | t       | rdsadmin | relation      |    16394 | 16394            |               | RowExclusiveLock
 28881 | t       |          | virtualxid    |          |                  |               | ExclusiveLock
 28881 | t       |          | transactionid |          |                  |      13607082 | ExclusiveLock
 29235 | t       |          | object        |          |                  |               | AccessShareLock
 29235 | t       |          | transactionid |          |                  |      13607075 | ExclusiveLock
 29235 | t       | writer   | relation      |    40990 | 40990            |               | AccessExclusiveLock
 29235 | t       | writer   | relation      |    20503 | pgbench_accounts |               | AccessExclusiveLock
 29235 | t       |          | virtualxid    |          |                  |               | ExclusiveLock
  • Reader でロックの保持状況を確認
postgres=> SELECT l.pid,l.granted,d.datname,l.locktype,relation,relation::regclass,transactionid,l.mode
    FROM pg_locks l  LEFT JOIN pg_database d ON l.database = d.oid
    WHERE  l.pid != pg_backend_pid()
    ORDER BY l.pid;
  pid  | granted | datname |  locktype  | relation |     relation     | transactionid |        mode
-------+---------+---------+------------+----------+------------------+---------------+---------------------
 11635 | t       |         | virtualxid |          |                  |               | ExclusiveLock
 11635 | t       | writer  | relation   |    20503 | pgbench_accounts |               | AccessExclusiveLock
 11635 | t       | writer  | relation   |    40990 | 40990            |               | AccessExclusiveLock
 11653 | t       |         | virtualxid |          |                  |               | ExclusiveLock
 14542 | t       |         | virtualxid |          |                  |               | ExclusiveLock
 14542 | f       | writer  | relation   |    20503 | pgbench_accounts |               | AccessShareLock

環境

参考