はじめに
自動VACUUMについてのメモ。
対象はPostgreSQL 11です。
自動VACUUM
PostgreSQLは追記型のアーキテクチャを採用しているため、不要な領域を解放し再利用できるようにするためにVACUUMという機能があります。
基本は自動VACUUM（auto vacuum）で解放されるため、手動でVACUUMを実行することは少ないです。
自動VACUUMがVACUUMを実行するかどうかは、autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factorパラメータの値を利用して決定されます。
- 不要レコード数（タプル数） ＞ autovacuum_vacuum_threshold + (レコード数 * autovacuum_vacuum_scale_factor)
autovacuum_vacuum_thresholdはデフォルトで"50"レコードです。
autovacuum_vacuum_scale_factorはデフォルトで"20"パーセントです。
レコード数が5000の場合は、以下のようになります。
- 50 + (5000 * 0.2) = 1050
つまり、1050レコードを超えると自動VACUUMが実行されることになります。
ここで"5000"（レコード数）はpg_classのreltuplesを参照します。不要レコード数はpg_stat_all_tablesのn_dead_tupを参照します。
1000万レコードだと、200万レコード以上変更が発生しないと自動VACUUMが実行されないことになるので、テーブルごとに変更した方が良いかも（今度考えてみよう）。
自動VACUUM（AUTO VACUUM）を有効にする設定
自動VACUUMはデフォルトで有効になっています。
明示的に設定するなら、postgresql.confを以下のように設定します。
autovacuum = on
設定した時間以上かかった自動VACUUMの情報をログ出力する設定
デフォルトでは-1になっており、自動VACUUMの情報はログへ出力されません。
例えば、5000ms以上かかった自動VACUUMの情報を出力するように設定するには、postgresql.confを以下のように修正します。
log_autovacuum_min_duration = 5000
ログファイルには以下のように出力されます。
2020-01-11 14:10:30.891 CET [14512] LOG: automatic vacuum of table "testdb.public.pgbench_accounts": index scans: 1
pages: 16586 removed, 0 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 1000000 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 852689
buffer usage: 72556 hits, 13013 misses, 22281 dirtied
avg read rate: 1.294 MB/s, avg write rate: 2.215 MB/s
system usage: CPU: user: 1.09 s, system: 0.18 s, elapsed: 78.59 s
VACUUMの進捗状況を確認する
VACUUMの進捗状況を確認します。VACUUM FULLはサポートされません。
# select
v.pid,
v.datname,
c.relname,
v.phase,
v.heap_blks_total,
v.heap_blks_scanned,
v.heap_blks_vacuumed,
v.index_vacuum_count,
v.max_dead_tuples,
v.num_dead_tuples
from
pg_stat_progress_vacuum as v
join
pg_class as c
on v.relid = c.relfilenode;
自動VACUUMの発生回数、発生時間を確認する
pg_stat_all_tablesビューで自動VACUUMの発生回数、発生時間を確認することができます。
- autovacuum_count: 自動VACUUMが実行された回数
- last_autovacuum: 最後に自動VACUUMが実行された時間
- autoanalyze_count: 自動VACUUMによってANALYZEが実行された回数
# SELECT * FROM pg_stat_all_tables WHERE relname = 'pgbench_accounts';
-[ RECORD 1 ]-------+------------------------------
relid | 429226
schemaname | public
relname | pgbench_accounts
seq_scan | 3
seq_tup_read | 2000000
idx_scan | 40000
idx_tup_fetch | 40000
n_tup_ins | 1000000
n_tup_upd | 20000
n_tup_del | 1000000
n_tup_hot_upd | 8366
n_live_tup | 0
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum | 2020-01-11 14:04:35.749745+01
last_autovacuum | 2020-01-11 14:10:30.89157+01
last_analyze | 2020-01-11 14:04:35.859541+01
last_autoanalyze | 2020-01-11 14:10:30.892868+01
vacuum_count | 1
autovacuum_count | 1
analyze_count | 1
autoanalyze_count | 1
テーブルごとに自動VACUUMを設定する
自動VACUUMはテーブル毎に設定をOFFにすることができます。
自動VACUUMを指定したタイミングで手動実行したいテーブルではOFFに設定することがあるかと思います。
大量更新の時にだけOFFにするという使い方もあるかも？？
testdb=# alter table pgbench_accounts set (autovacuum_enabled = false);
ALTER TABLE
testdb=# alter table pgbench_accounts set (toast.autovacuum_enabled = false);
ALTER TABLE
※ただし、pgbench_accounts はTOASTを使用していません。
上ではalter tableで変更していますが、create tableで最初に指定することもできます。
テーブル確認
testdb=# select relname, n_live_tup, n_dead_tup, round(n_dead_tup * 100 / nullif(n_live_tup + n_dead_tup, 0), 2) as ratio, last_autovacuum from pg_stat_user_tables;
relname | n_live_tup | n_dead_tup | ratio | last_autovacuum
------------------+------------+------------+-------+-------------------------------
pgbench_accounts | 100000 | 0 | 0.00 | 2020-01-18 01:36:27.898834+01
pgbench_history | 10 | 0 | 0.00 |
pgbench_branches | 10 | 10 | 50.00 |
pgbench_tellers | 100 | 10 | 9.00 |
(4 rows)
pg_statsinfoから確認
pg_statsinfoはPostgreSQLの利用統計情報を定期的に収集・蓄積するツール（拡張）です。
Autovacuumの実行結果の履歴が保存され、レポートを出力することができます。
レポート以外に以下のSQLで直接Autovacuumの実行結果を確認することができます。
# Autovacuumの実行履歴
select * from statsrepo.autovacuum where start > '2020-08-26★日付を指定'::timestamp order by start;
# Autovacuumがキャンセルされた実行履歴
select * from statsrepo.autovacuum_cancel order by timestamp;