「SAVACAN」担当のMKです。
PostgreSQLを運用する上で、重要なメンテナンス作業のひとつが「VACUUM」です。この処理は、削除や更新されたレコードを完全にデータベースから除去し、効率的な運用を支える役割を果たします。
ただし、「削除や更新されたレコードをさらに除去する」という表現には少し混乱を感じる方もいらっしゃるかもしれません。そこで今回は、PostgreSQLにおいてレコードを削除・更新するとどのような状態になるのかを最初にご説明し、VACUUMの役割を詳しく解説していきます。
本記事は、PostgreSQLの運用経験があり、「処理速度が遅くなってきたので改善したい」と感じている方に特におすすめの内容となっています。ぜひ最後までご覧ください。
PostgreSQLのレコード削除・更新と「デッドタプル」
PostgreSQLはレコードを削除・更新しても物理的なデータはすぐに削除されるものではありません。代わりに、そのレコードに削除フラグが付与され、非表示のレコードとしてデータベース内に残ります。
データベースではレコードのことをタプルと呼びますが、レコードから削除・更新されて非表示となったレコードは特に「デッドタプル」と呼ばれ、過剰に蓄積するとデータベースのパフォーマンスに悪影響を与える要因となります。
デッドタプルを除去する「VACUUM」の働き
デッドタプルをデータベースから完全に除去するためのメンテンナス処理が「VACUUM」になります。
特に更新頻度が多いテーブルではデッドタプルが増加しやすく、これがデータベースのパフォーマンスに悪影響を与えます。このため、定期的にVACUUMを実行してデッドタプルを除去し、パフォーマンスを維持する事が重要です。
VACUUMを怠るとどうなるか
VACUUMを実行しない場合、デッドタプルがデータベース内に溜まり続け、クエリ実行時の参照対象が増加します。その結果、クエリ内容は変わらなくても、徐々にパフォーマンスを低下させていきます。
毎秒膨大なクエリを処理しているような大規模システムでパフォーマンスの低下が発生すると、クエリの遅延からキューが溜まり、最悪の場合システムがダウンしてしまう事もあります。
また、デッドタプルの増加が進むと、パフォーマンスの低下だけでなく、ストレージの容量を圧迫します。ストレージ容量はコストに影響しますし、枯渇するとサーバー停止など重大な障害に繋がりますので、定期的なVACUUMの実行が不可欠です。
参考までに、MySQLやMariaDBでは、PostgreSQLのVACUUMに相当する処理として「OPTIMIZE TABLE」が用意されています。このコマンドも、削除されたレコードによる未使用スペースを整理（デフラグメント）するためのものです。これらのデータベースを運用している場合も、適切なメンテナンスを怠らないことが重要です。
VACUUM実行方法のご紹介
（１）設定値に基づいて自動でVACUUMする（autovacuum）
PostgreSQLには「autovacuum」という機能があります。
通常のクエリ実行に合わせて、設定内容に沿ったタイミングに自動でvacuumを行ってくれる機能で、インストール時にデフォルトで有効になっています。
autovacuumの設定はpostgresql.confにて行います。無効にしたい場合は [autovacuum = off] を設定します。
小、中規模のシステムであればautovacuumを有効にしておくだけで大きな問題は無いかと思います。
実行頻度が高かったり、リソースに悪影響が出ている場合は設定を調整して対応します。
関連する設定値はこちらです。設定値を基準にしてautovacuumが実行されます。
#autovacuum_work_mem = -1 # autovacuumプロセスが使用する最大のメモリ量を指定します。デフォルトは-1でmaintenance_work_memが代わりに使わます。
#autovacuum = on # autovacuumはデフォルトで有効です。
#track_counts = on # デフォルトで有効です。統計情報の収集をしてautovacuumを実行します。autovacuumを実行する際はonにする必要があります。
#log_autovacuum_min_duration = -1 # デフォルトは-1でautovacuumのログ保存が無効になります。 0で全てをログに残し、****msで指定すると指定した秒数以上かかったログを残します。
#autovacuum_max_workers = 3 # 同時に実行することができるautovacuumプロセスを指定します。デフォルトは3です。
#autovacuum_naptime = 1min # autovacuumデーモンの最小遅延を指定します。遅延時間を長くするほどautovacuumの実行頻度が低くなり、サーバーの負荷が軽減されます。
#autovacuum_vacuum_threshold = 50 # VACUUMを起動するために必要な、更新もしくは削除されたタプルの最小数を指定します。
#autovacuum_analyze_threshold = 50 # ANALYZEを起動するのに必要な、挿入、更新、もしくは削除されたタプルの最小数を指定します。
#autovacuum_vacuum_scale_factor = 0.2 # VACUUMを起動するか否かを決定するときに、autovacuum_vacuum_thresholdに足し算するテーブル容量の割合を指定します。
#autovacuum_analyze_scale_factor = 0.1 # ANALYZEを起動するか否かを決定するときに、autovacuum_analyze_thresholdに足し算するテーブル容量の割合を指定します。
#autovacuum_freeze_max_age = 200000000 # デフォルトは2億トランザクションです。データベース内で一番古いトランザクションIDと現在のトランザクションIDとの差が設定値（2億）を超えるとautovacuumが実行されます。
#autovacuum_multixact_freeze_max_age = 400000000 # トランザクションID周回を防ぐためにVACUUM操作が強制される前までにテーブルのpg_class.relminmxid フィールドが到達できる最大（マルチトランザクションにおける）年代を指定します。
#autovacuum_vacuum_cost_delay = 20ms # autovacuumのsleep時間です。設定値が大きいほど緩やかにautovacuumをします。
#autovacuum_vacuum_cost_limit = -1 # autovacuumが次のsleepまでに処理する最大数を設定します。（２）データベース全体へvacuumを手動実行
データベース全体に対して手動でVACUUMを実行する場合、下記のコマンドを実行します。
vacuumdb -a
vacuumdb -a -f -zオプションの意味は以下になります。
-a（all）
全てのデータベースに対して実行します。
-z（analyze）
統計情報を更新します。統計情報は、データベースを効率的に運用、維持するために欠かせない要素となります。
autovacuum実行時にも更新が行われます。
-f（full）
完全なバキューム（フルバキューム）を行います。
テーブル全体を再構築する事でデッドタプルを完全に排除し、ストレージ容量を物理的に削減することができます。ただし、テーブルが排他ロックされますのでクエリの実行に影響が出ます。また処理にも時間がかかるため、実行タイミングには注意が必要です。
フルバキュームについての補足事項
- 日常的なフルバキュームの実行は推奨されておりません。運用の見直しが必要です
- フルバキュームを指定しない場合、ストレージ容量は削減されませんが、デッドタプル領域は回収し、再利用されますので大きな問題はありません。
- 排他ロック中、対象テーブルに対する追加/更新/削除クエリは実行できません。メンテナンス中や業務時間外などシステム利用の無い時間に実行しましょう。
- フルバキュームではテーブルのコピーが実行されるため、一時的にストレージ使用量が増加します。巨大なテーブルに対してフルバキュームを行う場合、ストレージ容量に余裕があるか確認が必要です。
vacuumdbのオプションの詳細はPostgreSQLの公式サイトで確認できます。
https://www.postgresql.jp/docs/9.6/app-vacuumdb.html
（３）特定のテーブルに対してvacuumを手動実行
以下のコマンドで特定のテーブルに対してVACUUMを実行できます。
vacuumdb -t テーブル名 -d データベース名デッドタプルが多いテーブルを特定することで、実行すべきテーブルを絞り込む参考になります。
デッドタプルの様子は以下の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 n_dead_tup DESC;実行結果の参考例はこちらになります。
relname | n_live_tup | n_dead_tup | dead_ratio | pg_size_pretty
---------------------------------------+------------+------------+------------+----------------
user | 69936 | 950 | 1.00 | 78 MB
shop | 6344 | 329 | 4.00 | 11 MB
address | 1618067 | 100 | 0.00 | 118 MB
tel | 385802 | 83 | 0.00 | 36 MB
e-mail | 4391310 | 83 | 0.00 | 251 MB各カラムの内容は以下になります。
n_live_tup：有効なレコード数
n_dead_tup：不要なレコード数（デッドタプル）
dead_ratio：テーブル全体に対するデッドタプルの割合
pg_size_pretty：テーブルのデータサイズ
vacuum・autovacuum・fullvacuumの違いまとめ
下記にvacuum・autovacuum・fullvacuumの動作の違いをまとめました。
| vacuum | autovacuum | fullvacuum | |
|---|---|---|---|
| デッドタプルの回収 | 〇 | 〇 | 〇 |
| テーブル排他ロック | – | – | ロックされる |
| analyze | オプション指定 | 〇（デフォルト設定） | オプション指定 |
| ディスク領域返却 | 容量は減らないが テーブル内で再利用可能とする | 容量は減らないが テーブル内で再利用可能とする | 〇 |
平常時はautovacuumにて自動メンテナンスを行い、バッチ処理などで大量に更新・削除を行った場合に、必要に応じて手動によるvacuum/fullvacuumを行う形が理想的かと思います。
最後に
VACUUMはPostgreSQLの運用において欠かせない処理ですが、負荷が高い時間帯にautovacuumが実行されると、リソースを逼迫させてしまうことがあります。このような場合、通常はautovacuum設定を調整して対処しますが、システム特性によってはautovacuumを止め、夜間などシステム利用が無い時間にバッチ処理でVACUUMを実行することで、システムへの影響を最小限に抑える方法を取ることもあります。
システムに適したVACUUM設定で、安定したデータベース運用を目指しましょう。
スペース・アイでは、1日数千万PVのWebサイトや、月商3000万超ECサイトでのPostgreSQL運用に関する長年の経験があります。お困りの際は、ぜひお気軽にご相談ください！
