【PostgreSQL】pg_stat_progress_vacuumでVACUUMの進捗を確認する

pg_stat_progress_vacuumとは

大きなテーブルにVACUUMを実行すると、完了まで数分から数時間かかることがある。pg_stat_progress_vacuumは、現在実行中のVACUUMがどこまで進んでいるかを確認できるビューである。手動実行のVACUUMだけでなく、【PostgreSQL】autovacuumが実行されているか確認するで扱ったautovacuumの進捗も同じビューで確認できる。

500万件のテーブルに対してUPDATEを実行し、dead tupleを大量に作った状態でVACUUMを実行しながら確認する。

SELECT pid, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, num_dead_tuples
FROM pg_stat_progress_vacuum;

 pid |     phase     | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | num_dead_tuples 
-----+---------------+-----------------+-------------------+--------------------+--------------------+-----------------
 129 | scanning heap |           54055 |              1138 |                  0 |                  0 |          210530

pg_stat_progress_vacuumには実行中のVACUUMごとに1行だけ表示される。対象のテーブルにVACUUMが実行されていない場合、このビューに行は現れない。

主要なカラム

pid: VACUUMを実行しているバックエンドプロセスID
phase: 現在の処理フェーズ
heap_blks_total: テーブルの総ページ数
heap_blks_scanned: スキャン済みのページ数
heap_blks_vacuumed: dead tupleの回収が完了したページ数
index_vacuum_count: 完了したインデックス掃除サイクルの数
num_dead_tuples: 直近のインデックス掃除サイクル以降に検出したdead tupleの数

heap_blks_scannedとheap_blks_totalから、テーブルスキャンの進捗率を計算できる。

SELECT pid, phase, heap_blks_scanned, heap_blks_total,
  round(100.0 * heap_blks_scanned / heap_blks_total, 1) AS scan_pct
FROM pg_stat_progress_vacuum;

 pid  |     phase     | heap_blks_scanned | heap_blks_total | scan_pct 
------+---------------+-------------------+-----------------+----------
 2443 | scanning heap |              2167 |           54055 |      4.0

scan_pctはテーブルスキャンの進捗であり、VACUUM全体の完了率ではない。後述するvacuuming indexesやvacuuming heapのフェーズには反映されないため、目安として使う。

phaseの遷移

VACUUMは主に以下のフェーズを順に進む。

initializing: 開始直後の初期化処理
scanning heap: テーブルをスキャンし、dead tupleを検出する
vacuuming indexes: 検出したdead tupleをもとにインデックスを掃除する
vacuuming heap: テーブル本体からdead tupleを回収する
cleaning up indexes: インデックスの最終処理を行う
truncating heap: テーブル末尾の空きページを切り詰める
performing final cleanup: 統計情報の更新など最終処理を行う

実際にVACUUMを実行しながらphaseの変化だけを抜き出すと、以下のように遷移する。

       phase       | heap_blks_scanned | index_vacuum_count | num_dead_tuples 
-------------------+-------------------+--------------------+-----------------
 scanning heap     |                  7 |                  0 |               0
 vacuuming indexes |              54055 |                  0 |         4999985
 vacuuming heap    |              54055 |                  1 |         4999985

scanning heapでテーブル全体をスキャンし終えるとvacuuming indexesに移り、インデックスの掃除が終わるとindex_vacuum_countが1増えてvacuuming heapに進む。

index_vacuum_countが複数回になる場合

num_dead_tuplesがmaintenance_work_memで決まる上限（max_dead_tuples）に達すると、テーブル全体のスキャンを終える前に一度vacuuming indexesとvacuuming heapを実行し、scanning heapに戻って続きをスキャンする。この場合index_vacuum_countは2回以上に増える。

maintenance_work_memを1MBまで下げた状態で500万件のdead tupleがあるaccountsテーブルにVACUUMを実行すると、max_dead_tuplesが174761件に制限され、周回が繰り返される。

SELECT pid, phase, index_vacuum_count, max_dead_tuples, num_dead_tuples
FROM pg_stat_progress_vacuum;

       phase       | index_vacuum_count | max_dead_tuples | num_dead_tuples 
-------------------+--------------------+-----------------+-----------------
 scanning heap     |                  0 |          174761 |          156870
 vacuuming indexes |                  0 |          174761 |          174630
 vacuuming heap    |                  1 |          174761 |          174630
 scanning heap     |                  1 |          174761 |          154845
 vacuuming indexes |                  1 |          174761 |          174640
 vacuuming heap    |                  2 |          174761 |          174640

num_dead_tuplesがmax_dead_tuplesの174761へ近づくたびにvacuuming indexesとvacuuming heapを挟んでscanning heapに戻り、index_vacuum_countが1、2と増えていく。num_dead_tuplesは累計ではなく直近のサイクル分の値であり、vacuuming heap完了後は0から数え直すため、2回目のscanning heapでは154845と1回目より小さい値から始まっている。デフォルトのmaintenance_work_mem（64MB）であれば同じ500万件のdead tupleでも上限に達しないため1回で完了するが、上限が小さいと何度もインデックス全体を読み直すことになり、その分VACUUM全体の所要時間も伸びる。dead tupleが非常に多いテーブルでindex_vacuum_countが繰り返し増えている場合は、maintenance_work_mem（autovacuum実行時はautovacuum_work_mem）を増やして周回の発生を抑えられないか検討する。

実行時間が長いVACUUMを特定する

pg_stat_progress_vacuumにはトランザクションの開始時刻が含まれないため、経過時間を確認するには【PostgreSQL】pg_stat_activityで実行中クエリとロック待ちを確認するで扱ったpg_stat_activityとpidで結合する。

SELECT a.pid, now() - a.xact_start AS duration, v.phase, v.heap_blks_scanned, v.heap_blks_total
FROM pg_stat_progress_vacuum v
JOIN pg_stat_activity a ON v.pid = a.pid;

 pid  |    duration     |     phase     | heap_blks_scanned | heap_blks_total 
------+-----------------+---------------+-------------------+-----------------
 2443 | 00:00:03.068002 | scanning heap |              2227 |           54055

durationとheap_blks_scannedの伸び方を数回サンプリングして比較すると、処理速度が極端に遅くなっていないか、完了までのおおよその時間を見積もれる。特にautovacuum workerが長時間vacuuming indexesのままになっている場合は、インデックスの数が多いか、ディスクI/Oがボトルネックになっている可能性がある。