PostgreSQL 13 自動バキューム(AUTO VACUUM)

PostgreSQLはデータの更新が発生すると、更新した場所に対して「使用済」のフラグを立て新たにデータを追記する「追記型」と呼ばる他RDBMSとは異なるアーキテクチャを採用しています。「追記型」アーキテクチャはデータ更新が早いものの、「使用済」となった不要な領域を回収するバキューム(VACUUM)と呼ばれる処理を考察しなければなりません。

このページでは自動バキューム(AUTO VACUUM)について考察します。

バキュームの目的
自動バキュームの動作確認
自動バキュームに関するパラメタ
1. バキューム頻度に関するパラメタ
2. ログ出力に関するパラメタ
手動のバキューム
VMとFSM
1. VM (Visibility Map : 可視性マップ)
2. FSM (Free Space Map)

バキュームの目的

バキュームについては公式ドキュメントの「19.10.自動Vacuum作業」と「24.1. 定常的なバキューム作業」を参照ください。公式ドキュメントによると、バキュームの目的は以下の通りと言われています。

更新、あるいは削除された行によって占められたディスク領域の復旧または再利用
PostgreSQL問い合わせプランナによって使用されるデータ統計情報の更新
可視性マップの更新。これによりインデックスオンリースキャンが高速化される
トランザクションIDの周回またはマルチトランザクションIDの周回による非常に古いデータの損失を防止

このページでは「ディスク領域の復旧または再利用」「可視性マップの更新」に関連する部分のみ説明します。

「統計情報の更新」「トランザクションIDの周回」については別の機会に説明します。

自動バキュームの動作確認

自動バキュームの実行タイミング

不要となったタプル数が「バキューム閾値」を超えた時に、自動バキュームデーモンが自動バキューム(AUTO VACUUM)を開始します。「バキューム閾値」は以下の数式によって与えられます。

バキューム閾値 = バキューム基礎閾値 + バキューム規模係数 * タプル数

「バキューム基礎閾値」「バキューム規模係数」は、それぞれpostgresql.confのautovacuum_vacuum_thresholdとautovacuum_vacuum_scale_factorでデフォルト設定は50と0.2になります。

[postgres@centos10 ~]$ psql -c "SHOW autovacuum_vacuum_threshold;"
 autovacuum_vacuum_threshold 
-----------------------------
 50
(1 row)

[postgres@centos10 ~]$ psql -c "SHOW autovacuum_vacuum_scale_factor;"
 autovacuum_vacuum_scale_factor 
--------------------------------
 0.2
(1 row)

例えば、10,000行のテーブルの場合ならば、

50 + 10,000 * 0.2 = 2,050

の計算で2,050を超えるレコードを更新すれば自動バキューム(AUTO VACUUM)が発生します。充分大きなテーブルならば、約20%が更新されれれば自動バキュームされる事になります。

自動バキュームの確認環境構築

動作確認のため、以下のようなstaffテーブルを作成します。number列にindexを作成しているのはHOTを発生させないようにするためです。

HOTについては「HOT(Heap Only Tuple)」にまとめています。

createdb test
psql test << EOF
CREATE TABLE staff(
  id      serial      PRIMARY KEY,
  number  integer     default 0,
  f_name  varchar(30) default '',
  l_name  varchar(30) default ''
);
CREATE INDEX idx_number ON staff (number);
EOF

10,000レコードをstaffテーブルにINSERTします。

for i in {1..10000}
do
  [ $((${i} % 100)) == 0 ] && echo "INSERT : ${i} records "
  psql test -c "INSERT INTO staff (number) VALUES ('${i}');" > /dev/null
done

自動バキュームの動作確認

staffテーブルの統計情報を確認します。更新や削除によって使用不能になったレコード数はn_dead_tupで確認できます。

[postgres@centos10 ~]$ psql test << EOF
> \x
> SELECT * FROM pg_stat_all_tables where relname ='staff';
> EOF
Expanded display is on.
-[ RECORD 1 ]-------+------------------------------
relid               | 16496
schemaname          | public
relname             | staff
seq_scan            | 2
seq_tup_read        | 0
idx_scan            | 0
idx_tup_fetch       | 0
n_tup_ins           | 10000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 10000
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 2454
last_vacuum         | 
last_autovacuum     | 2021-01-02 00:27:18.905966+09
last_analyze        | 
last_autoanalyze    | 2021-01-02 00:28:18.909716+09
vacuum_count        | 0
autovacuum_count    | 2
analyze_count       | 0
autoanalyze_count   | 4

まずは1,000レコードを更新してみます。2,050レコードの更新で自動バキューム(AUTO VACUUM)が発生するはずですので、この時点ではバキュームは起きない事が予想されます。

for i in {1..1000}
do
  [ $((${i} % 100)) == 0 ] && echo "UPDATE : ${i} records "
  num=`expr $RANDOM % 10000`
  psql test -c "UPDATE staff SET number=${num} WHERE id=${i}" > /dev/null
done

n_dead_tupが1,000に増えている事が分かります。また、last_autovacuumの時刻が変わっていなくautovacuum_countも増えてない事から、1,000レコードの更新処理では自動バキューム(AUTO VACUUM)が発生していない事が分かります。

[postgres@centos10 ~]$ psql test << EOF
> \x
> SELECT * FROM pg_stat_all_tables where relname ='staff';
> EOF
Expanded display is on.
-[ RECORD 1 ]-------+------------------------------
relid               | 16496
schemaname          | public
relname             | staff
seq_scan            | 2
seq_tup_read        | 0
idx_scan            | 1000
idx_tup_fetch       | 1000
n_tup_ins           | 10000
n_tup_upd           | 1000
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 10000
n_dead_tup          | 1000
n_mod_since_analyze | 1000
n_ins_since_vacuum  | 2454
last_vacuum         | 
last_autovacuum     | 2021-01-02 00:27:18.905966+09
last_analyze        | 
last_autoanalyze    | 2021-01-02 00:28:18.909716+09
vacuum_count        | 0
autovacuum_count    | 2
analyze_count       | 0
autoanalyze_count   | 4

さらに1,051レコードを更新してみます。計2,050を超えるレコードが更新されたので、自動バキューム(AUTO VACUUM)が発生する事が予想されます。

for i in {1..1051}
do
  [ $((${i} % 100)) == 0 ] && echo "UPDATE : ${i} records "
  num=`expr $RANDOM % 10000`
  psql test -c "UPDATE staff SET number=${num} WHERE id=${i}" > /dev/null
done

last_autovacuumの時刻が変わりautovacuum_countのカウントが増えた事から、自動バキューム(AUTO VACUUM)が発生した事が分かります。

[postgres@centos10 ~]$ psql test << EOF
> \x
> SELECT * FROM pg_stat_all_tables where relname ='staff';
> EOF
Expanded display is on.
-[ RECORD 1 ]-------+------------------------------
relid               | 16496
schemaname          | public
relname             | staff
seq_scan            | 2
seq_tup_read        | 0
idx_scan            | 2051
idx_tup_fetch       | 2051
n_tup_ins           | 10000
n_tup_upd           | 2051
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 10000
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         | 
last_autovacuum     | 2021-01-02 00:58:19.416502+09
last_analyze        | 
last_autoanalyze    | 2021-01-02 00:58:19.430353+09
vacuum_count        | 0
autovacuum_count    | 3
analyze_count       | 0
autoanalyze_count   | 5

自動バキュームに関するパラメタ

バキューム頻度に関するパラメタ

自動バキューム(AUTO VACUUM)を行うかどうか、どの頻度で自動バキューム(AUTO VACUUM)するかは、PostgreSQL全体に対して設定する事もできればテーブル単位で設定する事もできます。

postgresql.confに設定可能なパラメタには以下のようなものがあります。

[postgres@centos10 ~]$ psql -c "SHOW ALL" | grep vacuum
 autovacuum                             | on                                     | Starts the autovacuum subprocess.
 autovacuum_analyze_scale_factor        | 0.1                                    | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
 autovacuum_analyze_threshold           | 50                                     | Minimum number of tuple inserts, updates, or deletes prior to analyze.
 autovacuum_freeze_max_age              | 200000000                              | Age at which to autovacuum a table to prevent transaction ID wraparound.
 autovacuum_max_workers                 | 3                                      | Sets the maximum number of simultaneously running autovacuum worker processes.
 autovacuum_multixact_freeze_max_age    | 400000000                              | Multixact age at which to autovacuum a table to prevent multixact wraparound.
 autovacuum_naptime                     | 1min                                   | Time to sleep between autovacuum runs.
 autovacuum_vacuum_cost_delay           | 2ms                                    | Vacuum cost delay in milliseconds, for autovacuum.
 autovacuum_vacuum_cost_limit           | -1                                     | Vacuum cost amount available before napping, for autovacuum.
 autovacuum_vacuum_insert_scale_factor  | 0.2                                    | Number of tuple inserts prior to vacuum as a fraction of reltuples.
 autovacuum_vacuum_insert_threshold     | 1000                                   | Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums.
 autovacuum_vacuum_scale_factor         | 0.2                                    | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
 autovacuum_vacuum_threshold            | 50                                     | Minimum number of tuple updates or deletes prior to vacuum.
 autovacuum_work_mem                    | -1                                     | Sets the maximum memory to be used by each autovacuum worker process.
 log_autovacuum_min_duration            | 5ms                                    | Sets the minimum execution time above which autovacuum actions will be logged.
 vacuum_cleanup_index_scale_factor      | 0.1                                    | Number of tuple inserts prior to index cleanup as a fraction of reltuples.
 vacuum_cost_delay                      | 0                                      | Vacuum cost delay in milliseconds.
 vacuum_cost_limit                      | 200                                    | Vacuum cost amount available before napping.
 vacuum_cost_page_dirty                 | 20                                     | Vacuum cost for a page dirtied by vacuum.
 vacuum_cost_page_hit                   | 1                                      | Vacuum cost for a page found in the buffer cache.
 vacuum_cost_page_miss                  | 10                                     | Vacuum cost for a page not found in the buffer cache.
 vacuum_defer_cleanup_age               | 0                                      | Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
 vacuum_freeze_min_age                  | 50000000                               | Minimum age at which VACUUM should freeze a table row.
 vacuum_freeze_table_age                | 150000000                              | Age at which VACUUM should scan whole table to freeze tuples.
 vacuum_multixact_freeze_min_age        | 5000000                                | Minimum age at which VACUUM should freeze a MultiXactId in a table row.
 vacuum_multixact_freeze_table_age      | 150000000                              | Multixact age at which VACUUM should scan whole table to freeze tuples.

テーブルに対して設定する場合は、CREATE TABLEコマンド実行時にWITHオプションを指定してテーブルを作成しましょう。指定可能なオプションの一覧は「CREATE TABLEのシンタックス」を参照ください。

使用例は以下の通りです。

psql test << EOF
CREATE TABLE sample(
  id      serial      PRIMARY KEY,
  name    varchar(30) default ''
) WITH (autovacuum_vacuum_threshold = 0, autovacuum_vacuum_scale_factor = 0.5);
EOF

テーブル作成後に変更したい場合は、ALTER TABLE文を使用して変更します。実行例は以下の通りです。

psql test -c "ALTER TABLE sample SET ( AUTOVACUUM_ENABLED = false );"

ログ出力に関するパラメタ

PostgreSQLはバキュームに一定時間以上を要したバキュームをログ出力する事もできます。log_autovacuum_min_durationで指定した値以上の時間がかかった処理がログ出力されますが、デフォルト設定は-1でありバキュームはログ出力されない仕様になっています。

以下、動作確認のためlog_autovacuum_min_durationを0にします。0は全ての自動バキューム(AUTO VACUUM)がログ出力されます。

cat << EOF >> /var/lib/pgsql/13/data/postgresql.conf
log_autovacuum_min_duration = 0
EOF
systemctl restart postgresql-13.service

バキュームのログを確認するため、5,000レコードを更新します。

for i in {1..5000}
do
  [ $((${i} % 100)) == 0 ] && echo "UPDATE : ${i} records "
  num=`expr $RANDOM % 10000`
  psql test -c "UPDATE staff SET number=${num} WHERE id=${i}" > /dev/null
done

確かにバキュームされた旨のログが出力された事を確認します。

[postgres@centos10 ~]$ tail -n 15 13/data/log/postgresql-Sat.log 
2021-01-02 02:31:04.785 JST [111685] LOG:  IPv4アドレス"127.0.0.1"、ポート5432で待ち受けています
2021-01-02 02:31:04.789 JST [111685] LOG:  Unixソケット"/var/run/postgresql/.s.PGSQL.5432"で待ち受けています
2021-01-02 02:31:04.794 JST [111685] LOG:  Unixソケット"/tmp/.s.PGSQL.5432"で待ち受けています
2021-01-02 02:31:04.801 JST [111688] LOG:  データベースシステムは 2021-01-02 02:31:04 JST にシャットダウンしました
2021-01-02 02:31:04.806 JST [111685] LOG:  データベースシステムの接続受け付け準備が整いました
2021-01-02 02:33:35.023 JST [115595] LOG:  テーブル"test.public.staff"の自動ANALYZE システム使用状況: CPU: ユーザ: 0.02秒、システム: 0.00秒、経過時間: 0.06秒"
2021-01-02 02:34:35.045 JST [122981] LOG:  テーブル"test.public.staff"の自動VACUUM: インデックススキャン: 1
  ページ: 0を削除、63が残存、0がピンによってスキップ、0が凍結によってスキップ
  タプル: 133を削除, 9305が残存, 0が参照されていないがまだ削除できない, 最古のxmin: 95159
  バッファ使用: 260ヒット, 16失敗, 0 ダーティ化
  平均読み取り速度: 2.363 MB/s, 平均書き込み速度: 0.000 MB/s
  システム使用状況: CPU: ユーザ: 0.02秒、システム: 0.00秒、経過時間: 0.05秒
  WAL usage: 157 records, 1 full page images, 48681 bytes
2021-01-02 02:34:35.218 JST [122981] LOG:  テーブル"test.public.staff"の自動ANALYZE システム使用状況: CPU: ユーザ: 0.07秒、システム: 0.00秒、経過時間: 0.17秒"
2021-01-02 02:35:35.022 JST [126743] LOG:  テーブル"test.public.staff"の自動ANALYZE システム使用状況: CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.01秒"

手動のバキューム

バキュームは手動で実行する事もできます。syntaxは以下の通りです。

VACUUM [FULL] [VERBOSE] <テーブル名>

バキュームには通常のバキュームとフルバキューム(VACUUM FULL)の2つが存在します。「24.1. Routine Vacuuming」や「PostgreSQL日本語Wiki」などを見ると、特別な理由がない限りのVACUUM FULLは非推奨としています。

自動バキュームと手動バキュームでは実行速度が異なります。自動バキュームはI/Oに配慮し低速で実行しますが、手動バキュームは高速で実行します。自動バキュームは負荷が一定以上になった場合はautovacuum_vacuum_cost_delay(デフォルト20ミリ秒)待ちますが、手動バキュームは負荷が一定以上になった場合はvacuum_cost_delay(デフォルト0ミリ秒)待ちます。そのため、手動バキュームは高速になります。

もし、自動バキュームと同じようにI/Oに配慮して手動バキュームを実行したい場合は、vacuum_cost_delay = 20とします。実行例は以下の通りです。

[postgres@centos10 ~]$ psql test
psql (13.1)
Type "help" for help.

test=# SET vacuum_cost_delay TO 20 ;
SET
test=# VACUUM VERBOSE staff ;
INFO:  "public.staff"に対してVACUUMを実行しています
INFO:  1253行バージョンを削除するためインデックス"staff_pkey"をスキャンしました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.07秒
INFO:  1253行バージョンを削除するためインデックス"idx_number"をスキャンしました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.09秒
INFO:  "staff": 1253行バージョンを8ページから削除しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.02秒
INFO:  現在インデックス"staff_pkey"は10000行バージョンを46ページで含んでいます
DETAIL:  1253インデックス行バージョンが削除されました。
0インデックスページが削除され、0ページが現在再利用可能です。
CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒。
INFO:  現在インデックス"idx_number"は10000行バージョンを57ページで含んでいます
DETAIL:  1253インデックス行バージョンが削除されました。
0インデックスページが削除され、0ページが現在再利用可能です。
CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒。
INFO:  "staff": 全 63 ページ中の 63 ページで見つかった行バージョン: 削除可能 8 行、削除不可 10000 行
DETAIL:  0 個の不要な行バージョンがまだ削除できません、最古のxmin: 96414
3888個の使われていないアイテム識別子がありました。
バッファピンのため0ページが、凍結のため0ページがスキップされました。
0ページが完全に空です。
CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.32秒。
VACUUM

VMとFSM

VM (Visibility Map : 可視性マップ)

VM(Visibility Map)は、各ページ毎に可視性およびFREEZE処理の判断で利用する2ビットのデータ。各ビットの意味は以下の通り。

ビット	意味
上位1ビット	FREEZE処理済ならば1、未処理ならば0
下位1ビット	すべて可視ならば1、一部不可視ならば0

FSM (Free Space Map)

Free Space Mapはテーブルの空き領域の管理するデータ(参考:68.3. Free Space Map)

VM, FSMの実態は_vm, _fsmというファイルで管理される。

[postgres@centos10 ~]$ ls -l 13/data/base/1 | egrep "fsm|vm" | head
-rw-------. 1 postgres postgres  24576 Dec 30 18:12 1247_fsm
-rw-------. 1 postgres postgres   8192 Dec 30 18:12 1247_vm
-rw-------. 1 postgres postgres  24576 Dec 30 18:12 1249_fsm
-rw-------. 1 postgres postgres   8192 Dec 30 18:12 1249_vm
-rw-------. 1 postgres postgres  24576 Dec 30 18:12 1255_fsm
-rw-------. 1 postgres postgres   8192 Dec 30 18:12 1255_vm
-rw-------. 1 postgres postgres  24576 Dec 30 18:12 1259_fsm
-rw-------. 1 postgres postgres   8192 Dec 30 18:12 1259_vm
-rw-------. 1 postgres postgres  24576 Dec 30 18:12 14234_fsm
-rw-------. 1 postgres postgres   8192 Dec 30 18:12 14234_vm