Postgresqlデータベースの引越しを検討されている方や、引越し後にデータベースの肥大化に悩まされている方の参考になればと。
What happened
Live環境のPostgresqlデータベースの引越しを行って数週間後、Postgresqlのdata volumeがこれまでにない異常なペースで消費されていることに気付く。
Drill down
- record数の増加状況は移行前後で特に大きな違いはない。
- Liveのデータベースを定期的にstaging環境にrestore (+ データのクレンジング) しているが、staging環境と比較しても明らかに大きなサイズになっている。stagingにrestoreすると15GB程度のテーブルが、liveでは100GB以上。。
- 以上の２つから、vacuum周りに何か問題があるのでは？とあたりをつける。
Check auto-vacuum execution
select n_live_tup,n_dead_tup,schemaname, relname,last_vacuum,last_autovacuum from pg_stat_all_tables where n_dead_tup != 0;
このクエリの結果を見た所、データベース内で最もレコード数が多く、更新/削除も頻繁に行なわれているテーブル上で、AUTO VACUUMが実行されていないことを発見。(last_autovacuum がnullだった。)
Investigation why
- AUTO VACUUMのtriggerは
autovacuum_vacuum_thresholdとautovacuum_vacuum_scale_factorというパラメータで制御されており、以下の条件を満たす時にAUTO VACUUMが実行される。
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * NUMBER_OF_ROWS > Dead Tuple
-
autovacuum_vacuum_thresholdのdefaultは50、autovacuum_vacuum_scale_factorのdefaultは0.2。 問題となっているテーブルには2億件以上のレコードが格納されており、ざっくりいうと、4000万件以上の更新/削除が発生しないと、AUTO VACUUMがtriggerされない状況になってしまっていた。
Fix
-
autovacuum_vacuum_threshold,autovacuum_vacuum_scale_factorは両方データベースの再起動無しにqueryで上書きできるので、以下のクエリを実行
begin;
ALTER TABLE TARGET_TABLE SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE TARGET_TABLE SET (autovacuum_vacuum_threshold = 100000);
commit;
- しばらく放置し、AUTO VACUUMが実行されたことを確認！！
But...
ディスク容量の異常な増加はストップしたが、AUTO VACUUMは不要になった領域を再利用可能にするだけなので、Postgresqlとしてのディスクの利用容量は減らなかった。。
The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system,
Take away
- Postgresqlの引越しをするときは、
autovacuum_vacuum_threshold,autovacuum_vacuum_scale_factorの設定を見直す。レコード数が多い、かつ、update/deleteが頻繁に行なわれているテーブルがある時は特に注意。 - 引越し後、しばらくの間はディスクの利用状況、AUTO VACUUMの実行状況をチェックする。