運用トラブルを防止するVACUUMのチューニング ～ XID周回問題と性能影響を防ぐ ～
PostgreSQLインサイド

PostgreSQLには、同時実行制御においてデータの一貫性を維持するために、各レコードにトランザクションIDを付与して管理する仕組みがあります。このトランザクションIDには上限がありサイクリックに使用されるため、VACUUM機能によって古いトランザクションIDを回収し、再利用可能とする仕組み（凍結処理）があります。VACUUMはデフォルトで自動的に実施することができます。
しかし、頻繁にレコードを更新する業務などトランザクションIDが大量に発生する場合には、凍結処理が追いつかず、システム停止を伴うトランザクションIDの周回問題（以降、XID周回問題と呼びます）に発展する可能性があるため、VACCUM処理の実行多重度や実行頻度のチューニングが必要です。また、このチューニングは運用中の業務に影響を与えないよう対策する必要があります。

本記事では、PostgreSQL内部メカニズムを踏まえ、VACUUM処理の種類や使い分け、監視方法、および効果的なチューニング方法を解説します。

• 1. XID周回問題に対するVACUUMの働き
• • 1.1 トランザクションID（XID）とXID周回問題
  • 1.2 VACUUMの種類と違い
  • 1.3 XID枯渇によるユーザー影響の事例
• 2. VACUUM運用の監視と対策
• • 2.1 VACUUM運用の監視項目
  • 2.2 監視項目の評価と対策
• 3. VACUUM運用のチューニング
• • 3.1 autovacuumのチューニング
  • 3.2 手動VACUUMの設計とチューニング

1.1 トランザクションID（XID）とXID周回問題

トランザクションID（以降、XID）とは、トラザクションごとに自動で割り振られる識別子です。
テーブルの各行にはXIDを管理する列が存在し、挿入や更新されたときのXIDが記録されます。現在のXIDの値を基準にして、それ以前を過去のXID（可視の状態）とし、未来に実行されるトランザクションのXIDは不可視の状態として識別することで、他のトランザクションからの可視化の判断に利用されます。
XIDには32ビットの符号なし整数を使用しているため、約40億個（2の32乗個）のトランザクションが稼働すると、周回して0から割り振られます。また、現在のXIDの値を基準にして、それ以前の約20億個（XID全体の半分）を過去のXID、約20億先までを未来のXIDとして識別します。このため、現在のXIDから約20億個よりも前の古いXIDを持つレコードが存在すると、そのXIDが未来のXIDとして識別されて参照できなくなり、データの整合性が損なわれるXID周回問題が発生します。
この対処法として、PostgreSQLではVACUUMによるXIDの凍結処理が用いられます。凍結処理は、定期的に古いXIDを再利用可能とします。

XID周回問題とVACUUMによる凍結処理については、以下の記事に詳しく解説しています。

• VACUUM FREEZEでトランザクションIDを凍結状態にする

XID枯渇によるシステム停止について

PostgreSQLでは、XID周回問題でデータが不正な状態になる前にシステムを停止させられるよう、安全マージンが取られています。データベース内で一番古いXIDと現在のXIDとの差（以降age）が一定数以上になると、システムが停止するため、注意が必要です。

XID周回問題が発生するまでに残り4000万トラザクション^(※)を切ると、WARNINGが出力され始めます。

WARNING: database "mydb" must be vacuumed within 39985967 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

また、XID周回問題が発生するまでに残り300万トランザクション^(※)を切ると、新しいトランザクションはエラーとなります。

ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT: Stop the postmaster and vacuum that database in single-user mode.

このエラーが発生すると、データベースレコードの更新や、リレーションを切り捨てる操作は失敗するため、対象データベースでVACUUMを実行する必要があります^(※)。
上記のような、XID枯渇によってトランザクションの新規発行ができなくなることを防ぐために、定期的なVACUUMによるXIDの凍結処理が推奨されます。

• ※
  PostgreSQLのバージョンにより値や対処方法が異なります。本書ではPostgreSQL 17を例にしています。詳細はマニュアルを参照してください。

1.2 VACUUMの種類と違い

VACUUMによるXID回収の手法には、大きく以下の2つがあります。ここでは、各手法の特徴について説明します。

• autovacuum
• 手動VACUUM（vacuumdbコマンド、またはSQL実行）

autovacuumと手動VACUUMの違い

PostgreSQLには、VACUUMを自動で実行するためのautovacuumという機構があり、定期的にVACUUM処理が実行可能です。
一方で直接SQLを実行する、またはPostgreSQLクライアントアプリケーション（vacuumdbコマンド）を使用することで、手動でVACUUM処理を実行可能です。
autovacuumと手動VACUUMでは、自動実行の他にも異なる点がありますので、注意してください。

autovacuumと手動VACUUMの機能比較表

autovacuumには2つの実行契機があります。autovacuumではデータ肥大化を防止するために実行された場合（以下、通常autovacuum）と、XID周回問題を防止するために実行された場合（以下、周回問題防止autovacuum）で動作が異なります。

• ※1
  autovacuum_vacuum_thresholdとautovacuum_vacuum_scale_factorから算出される値
• ※2
  autovacuum_freeze_max_ageの値
• ※3
  テーブルは、PostgreSQLが「実行契機」より自動で特定

1.3 XID枯渇によるユーザー影響の事例

XID枯渇によりユーザー影響が発生した事例をご紹介します。

【発生した事例】
データベースのログに「database is not accepting commands to avoid wraparound data loss in database」のメッセージが出力され、コマンドを受け付けない状態となっていた。

【原因と対策】
毎日1億トランザクション以上発行される状態であり、かつテーブル数が多い（約20万テーブル）ためVACUUM処理に時間がかかったことが原因であり、autovacuumのみの運用では利用可能なXIDが枯渇する状況^(※1)。対策として、毎日ageの高い4万テーブル^(※2)を手動VACUUMする運用に変更することで解消した。

【解説】
^※1  利用可能なXIDの枯渇状況を確認する方法について
利用可能なXIDの枯渇状況を確認するためには、PostgreSQLの統計情報を確認する必要があります。
データベースごとに状況を確認し、対処する必要があります。以下はSQLの実行例です。

SELECT datname, age(datfrozenxid) FROM pg_database;
     datname     |   age
-----------------+---------
 postgres        | 2571843
 db              | 2571843
 template1       | 2571843
 template0       | 2571843
 testdb          | 2571843
(5 rows)

^※2  ageの高いテーブルを判断する方法について
ageの高いテーブルを判断するためには、PostgreSQLの統計情報を確認する必要があります。
テーブルごとに状況を確認し、ageが高いテーブルを判断してください。以下はSQLの実行例です。

SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') ORDER BY age DESC;
                 table_name                 |   age
--------------------------------------------+---------
 pg_statistic                               | 2571843
 t1                                         | 2571836
 table_info                                 |    4874
 time_data                                  |     687
 tbl                                        |     619

2.1 VACUUM運用の監視項目

現在運用中のシステムにおいてXID周回問題が発生する可能性があるか確認するために、以下を監視してください。

SELECT datname, age(datfrozenxid) FROM pg_database;

2.2 監視項目の評価と対策

上記で監視した項目を評価し、対策が必要か確認してください。

【補足】

未凍結XID数が蓄積するケースについて
複数の周回問題防止autovacuumが実行された区間を見ると、未凍結XID数は、周回問題防止autovacuumの動作開始と終了によって、autovacuum_freeze_max_ageの設定値を基準に増減を繰り返します。
もし未凍結XID数のピークが徐々に大きくなっているならば、減少量が不十分な可能性があります。特にautovacuum_freeze_max_age以下にならなくなった場合は、周回問題防止autovacuumが動き続ける状態となり、特に注意が必要です。

以下に、本章で説明するVACUUM運用のチューニングの流れを示します。

3.1 autovacuumのチューニング

3.1.1 autovacuumのチューニング

XID回収を速くするために、多重度を上げる、またはスリープ実行頻度を下げるチューニングを検討してください。以下のパラメーターを調整し、実際に未凍結XIDが増加傾向でなくなることを確認してください。

• ※
  VACUUMではI/O回数に応じてコストを計上し、規定値に到達した場合にスリープします。このときのコスト規定値とスリープ時間をpostgresql.confで設定することにより頻度をチューニングします。

3.1.2 autovacuumのチューニングによる性能への影響

周回問題防止autovacuumは、PostgreSQLが自動でVACUUMをスケジュールするため、業務負荷の高い時間に実行される可能性があります。そのため、autovacuumのチューニングによりVACUUMの頻度が上がった場合には、業務影響を与える可能性があります。
以下に、autovacuumが与える影響についてまとめます。

【補足】

プライマリサーバーに異常が発生し切り替え事象が発生した場合、スタンバイサーバーでは、WALを全て適用した後に業務再開します。このため、WAL量が多い場合には、業務再開まで時間がかかる可能性があります。
また、スタンバイサーバー、またはWAL転送ネットワークに異常が発生し切離し事象が発生した場合、プライマリサーバーでは、スタンバイサーバーを切離すことで、実行中の業務アプリケーションのスタンバイサーバーに対してのWAL転送同期待ちを解消します。WAL量が多い場合には、DB領域の不揮発化（CHECKPOINT）を優先するため、切離しに時間がかかり、実行中の業務アプリケーションにタイムアウトが発生する可能性があります。

3.1.3 autovacuumのチューニングによる影響の監視

autovacuumの動作を変更した場合には、「2.1 VACUUM運用の監視項目」の監視に加え、以下を監視してください。監視項目と監視方法を記述します。

SELECT * FROM pg_stat_wal;

3.1.4 監視結果の評価と対策

autovacuumチューニング時の監視項目に対する評価方法と対策について、以下に記述します。

3.2 手動VACUUMの設計とチューニング

3.2.1 手動VACUUM運用の設計

手動VACUUM運用は、「vacuumdbコマンド」または「SQLコマンドのVACUUM」を使用します。ここでは、指定するオプション / パラメーターについての設計方法を記述します。
また、手動VACUUMは、一日一回実施する運用を想定とします。

【注意】

手動VACUUM運用を実施する場合には、周回問題防止autovacuumを抑止します。
この結果、周回問題防止autovacuumによる業務影響がなくなり、業務の安定化を図ることが可能となります。
周回問題防止autovacuumの抑止の方法は、postgresql.confのautovacuum_freeze_max_ageパラメーターに "2000000000"（20億）を設定することを推奨します。

3.2.2 手動VACUUM運用による性能への影響

手動VACUUM運用の実現により、周回問題防止autovacuumによる業務影響がなくなり、業務の安定化を図ることができます。一方で、手動VACUUM実行中には、ハードウェアリソース（CPU / DISK）の使用、および、手動VACUUM実行直後には、WALが大量に出力される可能性があるため、並行して動作する業務に対して性能への影響が懸念されます。
以下に、手動VACUUM運用による影響についてまとめます。

3.2.3 手動VACUUM運用の監視

手動VACUUM運用時には、以下を監視してください。監視項目と監視方法を記述します。

SELECT * FROM pg_stat_wal;

log_checkpoints = on

3.2.4 監視結果の評価と対策

手動VACUUM運用時の監視項目に対する評価方法と対策について、以下に記述します。

ここまで、PostgreSQLにおけるXID周回問題と、その対策として重要なVACUUMのチューニングについて、チューニング後に生じる可能性のある性能影響への対策も含めて解説しました。お使いのシステムに最適なVACUUMの運用を実現するために、ご参考となれば幸いです。

2024年12月27日公開

本コンテンツに関するお問い合わせ