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

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

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

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

autovacuumと手動VACUUMの違い

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

autovacuumと手動VACUUMの機能比較表

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

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

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

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

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

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

【補足】

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

3.1.1 autovacuumのチューニング

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

注8：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運用の監視項目」の監視に加え、以下を監視してください。監視項目と監視方法を記述します。

3.1.4 監視結果の評価と対策

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

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運用時には、以下を監視してください。監視項目と監視方法を記述します。

3.2.4 監視結果の評価と対策

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

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

富士通のミドルウェア製品のご紹介や各種イベント・セミナーの講演内容、デモンストレーションなどの動画をご覧いただけます。