今年も早いものでもう年末です。
大掃除を意識した時に、「普段からこまめにやっておけば...」と毎年後悔しています。
そんな私とは違って、PostgreSQLには普段からデータをこまめに掃除してくれる優秀な「VACUUM」という機能があります。
しかもゴミがでやすい時は小まめに、そうでないときは手を抜いてゆっくりやるというように調整しながら掃除をしてくれます！
ですがしっかりお世話をしないと、「あまりにも時間がかかりすぎる」「途中でやめてしまった」といったトラブルが発生します。
今回はそんなPostgreSQLのVACUUM機能を紹介したいと思います。
- PostgreSQLのレコード更新／削除のしくみと問題点
- PostgreSQLにおけるVACUUMの役割
- VACUUMの特徴
- 終わりに
- 検証環境
- vagrant + virtual box
- ubuntu20.24 + PostgreSQL 12
vagrantについてはvagrant+virtual box でDockerを動かす記事もありますので是非ご一読ください。
PostgreSQLのレコード更新／削除のしくみと問題点
PostgreSQLは、実は裏でゴミデータを削除してくれています。
が、そもそもゴミデータとは何なのでしょうか？
まずは、なぜそのゴミデータが出来上がるのか？と、それによる問題点を整理します。
1. PostgreSQLにおけるレコードの更新／削除のしくみ
PostgreSQLは、追記型アーキテクチャを採用しています。
例えば、あるレコードの更新を行った場合は直接そのレコードを書き換えるのではなく、以下のような動作をします。
- 更新対象のレコードに削除フラグ（目印）を付ける
- 更新後のレコードを追加する
この場合、グレーアウトされたレコードは使用していないにも関わらず残っています。
これが不要領域、いわゆるゴミデータです。
削除時も同様で、例えば100万件レコードを削除した場合はそれらのレコードに削除マークを付与するだけなので、100万件分のデータが残ったままになります。
またレコードだけでなく、インデックスも残ってしまいます。
2. ゴミデータが溜まることの問題点
このようなゴミデータが溜まり続けると、以下の問題が発生します。
- ゴミデータが溜まり続けることでディスク使用量が増える
- メモリ上の共有バッファにゴミデータが増え、メモリアクセスではなくディスクアクセスが発生する ⇒ 性能劣化
上記の問題点を解決する方法として「VACUUM」というPostgreSQLの機能を使いましょう！
PostgreSQLにおけるVACUUMの役割
大きく3つの役割があります。
1. 不要領域の回収／削除
VACUUMは、ゴミデータと呼ばれる不要領域を再利用可能な領域にリサイクルしてくれたり、ディスクの空き容量を増やしてくれたりします。
VACUUMを実行することで、2つのファイルが生成／更新されます。
-
- 空き領域が登録されたマップ。
- 例えばレコードを追加する場合、空いている場所がどこにあるのかを探すときにこのマップがあれば便利です。
-
- どこにゴミデータがあるかを記録したマップ。
これにより、ゴミデータがある部分のみVACUUM処理を行えばよいという判断が可能なため、vacuum処理を速く完了させることができます。
可視性マップ
2. トランザクション周回問題の防止
PostgreSQLには、有名なトランザクション周回問題という問題があります。
見えていてたはずのデータが突然見えなくなってしまうという現象が発生することを指します。
PostgreSQLではトランザクションにIDが割り振られています(トランザクションID = XID)。
例えばレコードの追加を行った場合、追加したレコードのシステムカラムにXIDが登録されます。
XIDは32bit(約42億個)で管理されています。
XIDを全て使い切った場合はトランザクションを新規で発行できなくなるため、0から再利用する仕組みとなっています。
つまりXIDは循環するようにして割り当てられます。
PostgreSQLではMVCCを使用しデータ管理を行っています。
実行中のトランザクションは、自身が持っているトランザクションよりも古いXIDは見れますが、新しいXIDは見れないしくみになっています。
そこで、新しいXIDを使い続ければ自分より古いXIDが新しいXIDに変わってしまうため見えなくなります。
例えば以下の図のようにXIDが20億進むと、過去のXIDが未来のXIDに変わってしまうため、本来見れるはずのレコードが見れなくなってしまいます。
これがトランザクション周回問題です。
下図参照
この問題は、FREEZE処理をVACUUMで行うことによって解決されます。
FREEZE処理とは、XIDに凍結されたことを示すマークを付けることです。
マークがあるXIDは全て過去のものとして扱うことができます。
具体的には、xminを「2（特殊なXID）」に書き換える処理を行っています。
また、設定値を変更することでどの程度XIDが消費されたらFREEZE処理を開始するといったコントロールができます。
その他にも、XIDが枯渇しないような仕組みがデフォルトで組み込まれています。
本記事では紹介しませんが、代わりに公式マニュアルを紹介させて頂きます。
3. プランナ用の統計情報の更新
詳細の説明は省きますが、効率良くSQLを実行するための「実行計画」を作成するプランナが利用する統計情報（データの分布などの情報）を更新してくれます。
実行計画についてはコチラの記事をぜひご参考ください。
soachr.hatenablog.com
このようにVACUUMはゴミデータを削除してくれたり、XIDを凍結状態にすることでトランザクション周回問題を防止します。
VACUUMの特徴
1. 実行手順
VACUUMは以下の7つの手順で実行されます。
| 手順 | フェーズ | 説明 |
|---|---|---|
| 1 | initializing | ヒープをスキャンし始める準備フェーズ。 |
| 2 | scanning heap | ヒープのスキャンを実行します。テーブルの先頭からゴミデータを探索します。発見したゴミデータのIDをメモリに乗せる。maintenance_work_memで設定可能。この値を越えた場合はスキャンを中断し、手順3に進みます。 |
| 3 | vacuuming indexes | インデックスのバキュームを実行します。フルスキャンするため最も時間がかかる処理。Postgres13で実装されたパラレルスキャンにより高速化を実現しています。 |
| 4 | vacuuming heap | テーブルのバキュームを実行します。VMを使って必要箇所のみバキューム実行を行います。 heap_blks_scannedがheap_blks_totalより少ない場合、システムはこのフェーズの完了後に手順2のヒープのスキャン処理に戻ります。 |
| 5 | cleaning up indexes | インデックスの整理を行います。後始末フェーズです。 |
| 6 | truncating heap | テーブルの末尾を切り詰めるなどの後始末を行います。 |
| 7 | performing final cleanup | 空き容量マップをバキュームしたり、pg_classの統計処理を更新するなどの後始末を行います。 |
最も重要なフェーズは3番目のINDEX VACUUMです。
INDEX VACUUMはフルスキャンが発生するため実行時間が他のフェーズよりもかかります。
VACUUMに時間がかかっている場合は、ログを確認してINDEX VACUUMが複数回実行されてるかどうかを確認し、必要であればmaintenance_work_memを増やす対策が必要です。
ログの確認方法については後述の情報をご参照ください。
2. VACUUMの種類
VACUUMは、AUTO VACUUMとVACUUM FULLの2パターンがあります。
それぞれの特徴について軽く触れておきます。
AUTO VACUUM
AUTO VACUUMは、設定値に従って自動的にVACUUMを実行してくれます。
基本的にデフォルトでONになっています（古いバージョンではOFFになっています！）。
VACUUM実行中であっても排他ロックを取らないため、レコードの参照等が可能です。
cronのようにVACUUMを定時で実行することができません。
デフォルト設定値もそれなりに適切なため、VACUUMは基本的にAUTO VACUUMを利用しておけば問題ないかと思います。
VACUUM FULL
VACUUM FULLは自動ではなく明示的にVACUUMを実行します。
回収した不要領域をOSに戻してくれるためHDDなどの記憶領域が増えます。
その代わり、VACUUM実行中はテーブルに強力な排他ロックをかけるためSELECTすらできなくなるので気を付ける必要があります。
実行タイミング
VACUUMが実行されるタイミングは大きく2つあります。
- 定時および設定値を越えた時
- トランザクション周回問題を防止する時
では、それぞれの特徴と実際の動作を確認してみましょう。
AUTO VACUUM
AUTO VACUUMはデフォルトでONになっていますが、明示的に指定する場合はpostgresql.confファイルから設定可能です。
設定ファイルの確認
設定ファイルは下記コマンドで探せます。
find / -name postgresql.conf 2> /dev/null
設定を確認してみましょう。
less /etc/postgresql/12/main/postgresql.conf
#autovacuum = onここのコメントアウトを外すと明示的に有効になります。
なおtrack_counts = onも必要ですが、こちらもデフォルトでONになっています。
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
# before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
これらの設定値を変更することによって、VACUUMの実行タイミングをコントロールしたりチューニングすることができます。
1. VACUUMが実行されるタイミングを知る
設定値でAUTO VACUUMの実行タイミングを調整することができます。
AUTO VACUUMはバキューム閾値を越えた場合実行されます。
バキューム閾値は以下の式で算出されます。
バキューム基礎閾値： #autovacuum_vacuum_threshold = 50 バキューム規模係数： #autovacuum_vacuum_scale_factor = 0.2 バキューム閾値 = バキューム基礎閾値 + バキューム規模係数 * タプル数
100万件のレコードを対象とした場合は バキューム閾値 = 50 + 0.2 * 1000000 = 200050件
となります。
※VACUUM実行のタイミングはautovacuum_naptimeで管理されます。
デフォルト設定値は60なので、60秒単位でAUTO VACUUMを実行するかどうかを監視しています。
実験 AUTO VACUUMの実行
実験前にVACUUMが実行されたかどうかの情報をチェックしておきましょう。
pg_stat_all_tablesで確認することができます。
postgres=# SELECT * FROM pg_stat_all_tables WHERE relname = 'vacuum_test'; -[ RECORD 1 ]-------+------------ relid | 32812 schemaname | public relname | vacuum_test seq_scan | 0 seq_tup_read | 0 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 AUTO VACUUMが実行された回数 analyze_count | 0 autoanalyze_count | 0
autovacuum_countが0回になっているので、AUTO VACUUMは実行されていないことが分かります。
ついでに実ファイルも確認しておきましょう。
relidが32812とありますので、そちらが実ファイルの場所です。
実ファイルの場所はdatid/relidです。以下のSQLでdatidを確認できます。
postgres=# SELECT datid, datname FROM pg_stat_database WHERE datname = 'postgres'; -[ RECORD 1 ]----- datid | 13461 datname | postgres
postgres@ubuntu2004:~$ ls -lh /var/lib/postgresql/12/main/base/13461/32812* -rw------- 1 postgres postgres 0★ Dec 21 01:18 /var/lib/postgresql/12/main/base/13461/32812
実ファイルのサイズが0ですね。
続いて、レコード数がバキューム閾値を越えない場合はAUTO VACUUMが効かないことを確認します。
閾値は20万50件なので、閾値を越えないよう20万件INSERTしてみます。
`INSERT INTO vacuum_test SELECT generate_series(1,200000),md5(clock_timestamp()::text);` -[ RECORD 1 ]-------+------------ relid | 32812 schemaname | public relname | vacuum_test seq_scan | 1 seq_tup_read | 200000 idx_scan | idx_tup_fetch | n_tup_ins | 200000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 200000 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | 2022-12-23 05:06:47.194957+00 vacuum_count | 0 autovacuum_count | 0 ★カウントが増えていない analyze_count | 0 autoanalyze_count | 1
autovacuum_countが0のままですね。
実ファイルを確認してみましょう。
-rw------- 1 postgres postgres 14M★ Dec 21 01:20 /var/lib/postgresql/12/main/base/13461/32812 -rw------- 1 postgres postgres 24K Dec 21 01:20 /var/lib/postgresql/12/main/base/13461/32812_fsm
実ファイルが14Mに増加しています。
FSMが生成されていることも確認できますね。
続いてゴミデータを作るために1件を残して削除します。
DELETE FROM vacuum_test WHERE id <= 199999;
`SELECT * FROM pg_stat_all_tables WHERE relname = 'vacuum_test';` -[ RECORD 1 ]-------+------------ relid | 32812 schemaname | public relname | vacuum_test seq_scan | 1 seq_tup_read | 200000 idx_scan | idx_tup_fetch | n_tup_ins | 200000 n_tup_upd | 0 n_tup_del | 199999 ★不要な行 n_tup_hot_upd | 0 n_live_tup | 1 n_dead_tup | 199999 n_mod_since_analyze | 199999 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | 2022-12-21 01:20:53.861522+00 vacuum_count | 0 autovacuum_count | 0 ★カウントが増えていない analyze_count | 0 autoanalyze_count | 1
n_tup_delが199999となっています。
こちらは不要な行を表していますので、先ほどDELETEしたレコードがゴミデータとなっています。
続いてバキューマの閾値（20万50件）を越えるレコードを追加すると、バキュームが実行されることを確認します。
あと50件以上レコードを作成すれば閾値を越えるためAUTO VACUUMが実行されるはずです。
ゆとりを持って100件追加してみましょう。
100件insert
insert into vacuum_test select generate_series(1,100),md5(clock_timestamp()::text);
auto_vacuumが実行されていることを確認
postgres=# SELECT * FROM pg_stat_all_tables WHERE relname = 'vacuum_test'; -[ RECORD 1 ]-------+------------------------------ relid | 32812 schemaname | public relname | vacuum_test seq_scan | 1 seq_tup_read | 200000 idx_scan | idx_tup_fetch | n_tup_ins | 200100 n_tup_upd | 0 n_tup_del | 199999 n_tup_hot_upd | 0 n_live_tup | 101 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | 2022-12-21 01:22:53.960128+00 last_analyze | last_autoanalyze | 2022-12-21 01:24:53.588466+00 vacuum_count | 0 autovacuum_count | 1 ★カウントが増えている analyze_count | 0 autoanalyze_count | 3
autovacuum_countが0から1に増加しています。
設定した閾値を越えたためAUTO VACUUMが実行されていますね。
実ファイルを確認してみます。
-rw------- 1 postgres postgres 14M★ Dec 21 01:27 /var/lib/postgresql/12/main/base/13461/32812 -rw------- 1 postgres postgres 24K Dec 21 01:20 /var/lib/postgresql/12/main/base/13461/32812_fsm -rw------- 1 postgres postgres 8.0K Dec 21 01:22 /var/lib/postgresql/12/main/base/13461/32812_vm
VACUUMが実行されたため可視性マップも作成されていますね。
データファイルは圧縮されていないですが、不要領域が回収できており再利用できているかどうか確認します。
さらに20万レコードを追加
insert into vacuum_test select generate_series(1,200000),md5(clock_timestamp()::text);
レコードを追加しても容量が14Mから変わっていません。
不要領域を回収して空き容量にリサイクルし、再利用出来ていることが確認できます。
-rw------- 1 postgres postgres 14M★ Dec 21 01:27 /var/lib/postgresql/12/main/base/13461/32812 -rw------- 1 postgres postgres 24K Dec 21 01:27 /var/lib/postgresql/12/main/base/13461/32812_fsm -rw------- 1 postgres postgres 8.0K Dec 21 01:27 /var/lib/postgresql/12/main/base/13461/32812_vm
さらに20万レコードを追加
postgres=# insert into vacuum_test select generate_series(1,200000),md5(clock_timestamp()::text); INSERT 0 200000 postgres=# select count(*) from vacuum_test; -[ RECORD 1 ]- count | 400101
容量を確認します。
postgres@ubuntu2004:~$ ls -lh /var/lib/postgresql/12/main/base/13461/32812* -rw------- 1 postgres postgres 27M★ Dec 21 01:34 /var/lib/postgresql/12/main/base/13461/32812 -rw------- 1 postgres postgres 24K Dec 21 01:27 /var/lib/postgresql/12/main/base/13461/32812_fsm -rw------- 1 postgres postgres 8.0K Dec 21 01:27 /var/lib/postgresql/12/main/base/13461/32812_vm
再利用可能な空き容量が無くなったため追加したレコード分実ファイルが増加しました。
今回はVACUUMが実行されましたが、ある条件では実行されないケースがあります。
ロングトランザクション
VACUUMが機能しない時、ロングトランザクションが原因になっているケースがあります。
ロングトランザクションとは、トランザクションが開始されてから、コミット／ロールバックが行われず長時間生存しているトランザクションの事です。
ロングトランザクションよりも後のトランザクション更新／削除されたレコードはバキューム処理の対象外となってしまいます。
バキュームが実行されたにも関わらずゴミデータが回収されていない場合は、ロングトランザクションがあることを疑ってみましょう。
下記SQLを実行すると、ゴミデータがいくら残っているかを確認できます。
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup*100/(n_dead_tup+n_live_tup), 2) AS dead_ratio,
pg_size_pretty(pg_relation_size(relid)) FROM pg_stat_user_tables
WHERE n_live_tup > 0 ORDER BY dead_ratio DESC;
-[ RECORD 1 ]--+------------
relname | vacuum_test
n_live_tup | 1
n_dead_tup | 1100000 ★不要行。ゴミデータ
dead_ratio | 99.00 ゴミデータが占める割合
pg_size_pretty | 72 MB
ゴミデータが多く、VACUUM処理で不要領域が回収できていないことが考えられます。
ロングトランザクションが存在しているかどうかは下記SQLで確認可能です。
SELECT pid, query, xact_start, state FROM pg_stat_activity WHERE state = 'idle in transaction'; pid | 1478 query | SELECT pid, query, xact_start, state FROM pg_stat_activity; xact_start | 2022-12-22 06:39:26.865784+00 state | idle in transaction★
stateがidle in transactionのトランザクションは、トランザクションがスタートしているにも関わらずCOMMITもROLLBACKも行われていない状況です。
このトランザクションのxact_startの日時が古い場合はロングトランザクションと考えられるため、pg_terminate_backendでプロセスの終了を実施します。
SELECT pg_terminate_backend(pid);
2. VACUUMのチューニングを実施する
頻繁にレコードが更新されるテーブルはメンテナンス負荷がかかるため、チューニングを実施するか自動バキュームの対象外にすることが望ましいです。
チューニング方法
auto_vacuumのログを確認してチューニングを行います。
auto_vacuum実行ログはデフォルトでOFFになっているため、下記SQLでログ出力を行います。
VACUUMが設定したms以上かかった場合はログを出力します。
0に設定した場合はAUTO VACUUMの結果が出力され、-1では出力が無効になります。
log_autovacuum_min_duration = 60000ms
ログは以下のように出力されます
2022-12-23 04:13:46.320 UTC [11751] LOG: automatic vacuum of table "postgres.public.vacuum_test": index scans: 1★
pages: 0 removed, 8334 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 999999 removed, 4 remain, 0 are dead but not yet removable, oldest xmin: 568
buffer usage: 44216 hits, 0 misses, 864 dirtied
avg read rate: 0.000 MB/s, avg write rate: 4.742 MB/s
system usage: CPU: user: 0.35 s, system: 0.03 s, elapsed: 1.42 s
2022-12-23 04:13:46.443 UTC [11751] LOG: automatic analyze of table "postgres.public.vacuum_test" system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.12 s
特筆すべき点は、index scansです。
これはVACUUMの手順3（vacuuming indexes）が何回行われたかを指します。
今回は値が1なので1回行われたことになり、SCAN⇒VACUUMのループが発生しなかったと考えられますのでOKです。
これが1回以上であればループが発生していますので、vacuuming indexesを早く終わらせるための施策を実施する必要があります（maintenance_work_memを増やすなど）。
AUTO VACUUMが終わらない場合はまずココを疑ってみてチューニングをしてみましょう。
バキュームの実行時間を短くしたい
システムのメンテナンス時間を短くしたいなど、できるだけ短時間でバキュームの実行を完了させたいケースになります。
また、VACUUMが実行されているにも関わらずテーブルが肥大化し続ける場合も有効です。
以下の方法がありますの状況に応じて組わせると良いかと思います。
バキュームの閾値を下げる
autovacuum_vacuum_scale_factorを下げることでバキューム閾値が下がります。
例えばautovacuum_vacuum_scale_factorを0.2から0.01に下げると、バキューム基礎閾値： #autovacuum_vacuum_threshold = 50 バキューム規模係数： #autovacuum_vacuum_scale_factor = 0.01 (100万行の場合) バキューム閾値 = 50 + 0.02 * 1000000 = 10,050件となります。
デフォルトでは200,050件を越えなければVACUUMが実行されませんでしたが、ココを調整することで改善が望めます。
なお、閾値を下げすぎた場合は不必要なVACUUMによりオーバーヘッドが発生しますので注意が必要です。バキュームの頻度を上げる
autovacuum_vacuum_cost_delay を下げる（手動vacuum実行時はデフォルトで無効）
自動バキュームは autovacuum_cost_limitの設定値を処理すると、autovacuum_vacuum_cost_delayの設定値だけ一時停止します。
つまりautovacuum_vacuum_cost_delayを下げることで、vacuumの間隔を短くすることができ、早くVACUUMを終わらせることが可能になります。なお、こちらを0にすることで全く遅延させることなく次のvacuum実行が可能になります。
maintenance_work_mem を増やす（最大１GB）
maintenance_work_memを増やすことでVACUUM実行フェーズの内最も時間がかかるインデックスのバキュームを高速化します。
autovacuum_max_workers を増やす
autovacuum_max_workersは、同時に実行することができるautovacuumプロセスの最大数です。
巨大なテーブルが存在する場合は増やすと改善が望めます。
任意のテーブルにバキュームの設定値をセットする
postgresql.confの値を修正すると、全テーブルに対して設定を反映してしまいます。
そのため、各テーブルに必要なパラメータを設定するようにしましょう。
CASE 1: 任意のテーブルのみ頻繁／緩やかにVACUUMが実行されるようにする
任意のテーブルにパラメータをセットし、バキューム閾値をコントロールできます。
ALTER TABLE vacuum_test SET (autovacuum_vacuum_threshold=50,autovacuum_vacuum_scale_factor=0.01);
CASE 2: 任意のテーブルを自動バキュームの対象外にする
頻繁に更新が走るような巨大なテーブルは、システムが利用されていない夜間に手動でバキュームを行う運用方法も考えられます。
その場合は、下記のSQLで自動バキュームの対象外とすることができます。
ALTER TABLE vacuum_test SET (autovacuum_enabled = off); ALTER TABLE -[ RECORD 1 ]-------+------------------------------ relid | 32812 schemaname | public relname | vacuum_test seq_scan | 6 seq_tup_read | 800103 idx_scan | idx_tup_fetch | n_tup_ins | 600100 n_tup_upd | 0 n_tup_del | 200099 n_tup_hot_upd | 0 n_live_tup | 400001 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | 2022-12-21 01:25:53.554516+00 last_analyze | last_autoanalyze | 2022-12-21 01:34:54.471287+00 vacuum_count | 0 autovacuum_count | 2 analyze_count | 0 autoanalyze_count | 6 postgres=# DELETE FROM vacuum_test WHERE id <= 400000; -[ RECORD 1 ]-------+------------------------------ relid | 32812 schemaname | public relname | vacuum_test seq_scan | 6 seq_tup_read | 800103 idx_scan | idx_tup_fetch | n_tup_ins | 600100 n_tup_upd | 0 n_tup_del | 200099 n_tup_hot_upd | 0 n_live_tup | 400001 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | 2022-12-21 01:43:54.840929+00 last_analyze | last_autoanalyze | 2022-12-21 01:44:54.584522+00 vacuum_count | 0 autovacuum_count | 2 analyze_count | 0 autoanalyze_count | 6
autovacuum_countに変化がありません。
AUTO VACUUMの課題
定期実行が不可能
設定値を越えた場合にバキューム処理が実行されるため、システムが動いていない夜間にバキュームを実行させたいといったケースには対応できません。
空き領域をOSに返さない
auto_vacuumでは不要領域を回収していることを確認しましたが、空いた領域をOSに返していません。
この場合、例えば、HDDの使用量によって課金請求が発生するため不要なデータを削除してHDD使用量を下げたいといったケースには対応できません。
こういったケースについては、VACUUM FULLを利用することで空き領域をOSに返すことができます。
VACUUM FULL
基本的な動きは以下の通りです。
- テーブルのレコードを取得し新しいテーブルに詰め込む
- 新しいテーブルにインデックスを作成
- テーブルを入れ替える
主な特徴は
- 不要領域を回収する
- 空き領域をOSに返す
- 実行時はテーブルロックがかかる
- テーブルの作り替えとインデックスの再作成を実施するため時間がかかる
- VACUUM実行時に古いテーブルと新しいテーブルが同時に作成されるため一時的に容量が辛くなる
利用シーンとしては、たとえばHDDの使用量によって課金請求するシステムであれば、ユーザーがシステム画面上で任意の時間にVACUUM FULLを使ったメンテナンスを実行するケースが思いつきます。
使い方
下記SQLで実行可能です。
VACUUM FULL tablename;
VACUUM FULLを行った場合は空き領域をOSに戻してくれるかどうかを確認してみましょう。
OSに空き領域を返してくれることを確認する
その前に、実験用のテーブルはAUTO VACUUMの対象外にしておく必要があります。
ALTER TABLE vacuum_test SET (autovacuum_enabled = off);
実ファイルを確認しておきます。
postgres=# select relname, relfilenode from pg_class where relname = 'vacuum_test'; relname | relfilenode -------------+------------- vacuum_test | 32777 (1 row) postgres@ubuntu2004:~$ ls -lh /var/lib/postgresql/12/main/base/13461/32777* -rw------- 1 postgres postgres 0★ Dec 19 04:35 /var/lib/postgresql/12/main/base/13461/32777
ファイル容量は0ですね。
続いて100万件レコードを追加してみます。
postgres=# insert into vacuum_test select generate_series(1,1000000),md5(clock_timestamp()::text); INSERT 0 1000000 postgres@ubuntu2004:~$ ls -lh /var/lib/postgresql/12/main/base/13461/32777* -rw------- 1 postgres postgres 66M★ Dec 19 04:36 /var/lib/postgresql/12/main/base/13461/32777 -rw------- 1 postgres postgres 40K Dec 19 04:36 /var/lib/postgresql/12/main/base/13461/32777_fsm
ファイル容量が66Mに増加しています。
大量にDELETEしてゴミデータを作成します。
postgres=# delete from vacuum_test where id < 999999; DELETE 999998 postgres@ubuntu2004:~$ ls -lh /var/lib/postgresql/12/main/base/13461/32777* -rw------- 1 postgres postgres 66M★ Dec 19 04:40 /var/lib/postgresql/12/main/base/13461/32777 -rw------- 1 postgres postgres 40K Dec 19 04:36 /var/lib/postgresql/12/main/base/13461/32777_fsm -rw------- 1 postgres postgres 8.0K Dec 19 04:40 /var/lib/postgresql/12/main/base/13461/32777_vm
ファイル容量が減っていません。
VACUUM FULLを実行してみましょう。
VACUUM FULL vacuum_test;
テーブルの再作成が行われるため、古いテーブルの実ファイルは空になっています。
postgres@ubuntu2004:~$ ls -lh /var/lib/postgresql/12/main/base/13461/32777* -rw------- 1 postgres postgres 0 Dec 19 04:46 /var/lib/postgresql/12/main/base/13461/32777
新しく作成されたテーブルの実ファイルを確認します。
postgres=# select relname, relfilenode from pg_class where relname = 'vacuum_test'; relname | relfilenode -------------+------------- vacuum_test | 32785 (1 row) postgres@ubuntu2004:~$ ls -lh /var/lib/postgresql/12/main/base/13461/32785* -rw------- 1 postgres postgres 8.0K★ Dec 19 04:46 /var/lib/postgresql/12/main/base/13461/32785
ファイル容量が減っていることが確認できます。
VACUUM FULLの課題
処理の重さ
VACUUM FULLはテーブルとインデックスの再作成を行うため処理が非常に重くなります。
そのため、AUTO VACUUMでは本当に達成できないケースなのかを検討したうえでVACUUM FULLをせ択するようにしましょう。排他ロック
VACUUM実行中は強力な排他ロックがかかるため、システムが稼働していない時間帯にVACUUMの実行を終える必要があります。
排他ロック問題に関しては、pg_repackと呼ばれる拡張があります。
こちらは、排他ロックを取らずにVACUUM FULL相当の処理を行ってくれる拡張となっていますので導入を検討してみるという手もあります。
終わりに
VACUUMの機能と特徴を紹介させて頂きました。
デフォルト設定でも運用可能ですが、「DBの寿命はアプリケーションよりも長い」と言われるように、長期間運用を続けていくうちにレコードやゴミデータが溜まることで様々な問題が発生します。
PostgreSQLとは切っても切り離せないVACUUMの深淵を少し覗くことで、普段からこまめに掃除をしてくれているその仕組みと恩恵を知ることができ、さらに興味が湧いてきました！
エンジニア中途採用サイト
ラクスでは、エンジニア・デザイナーの中途採用を積極的に行っております！
ご興味ありましたら是非ご確認をお願いします。
https://career-recruit.rakus.co.jp/career_engineer/
カジュアル面談お申込みフォーム
どの職種に応募すれば良いかわからないという方は、カジュアル面談も随時行っております。
以下フォームよりお申込みください。
rakus.hubspotpagebuilder.com
ラクスDevelopers登録フォーム
https://career-recruit.rakus.co.jp/career_engineer/form_rakusdev/
イベント情報
会社の雰囲気を知りたい方は、毎週開催しているイベントにご参加ください！
◆TECH PLAY
techplay.jp
◆connpass
rakus.connpass.com
