【豆知識】スタンバイ側の参照処理で発生したコンフリクトを解決したい
PostgreSQLインサイド

実現方法

ストリーミングレプリケーション構成において、スタンバイ側で参照処理（SELECT文による問い合わせの実行）を行う場合、プライマリー側で行われた更新処理やVACUUM処理などによるWAL（トランザクションログ）をスタンバイ側に適用する処理が同一資源（注1）にアクセスすることで、コンフリクト（競合）が発生することがあります。

• 注1
  アクセス対象のオブジェクト（テーブルなど）や、そのオブジェクトが格納されているページ

その際、以下のどちらかのエラーメッセージが出力され、スタンバイ側の参照処理がキャンセルされます。

エラーメッセージ

エラーによりコネクションを切断した場合のメッセージ

FATAL: terminating connection due to conflict with recovery

エラーによりクエリのみキャンセルした場合のメッセージ

ERROR: canceling statement due to conflict with recovery

PostgreSQLには、コンフリクトが発生して一定時間（デフォルトで30秒）が経過すると、同一資源をアクセスしている参照処理をキャンセルさせて、WALの適用処理を優先する仕組みがあります。そのため、コンフリクトの発生を完全に無くすことは非常に難しいですが、以下の対処により解決へと近づけることができます。

• A）参照処理において、リトライ処理を入れてキャンセルされた処理を再実行する
• B）参照処理において、トランザクション処理を分割するなどして1回あたりの実行時間を短くする
• C）PostgreSQLの設定パラメーターを調整してコンフリクトの発生を緩和する

対処例

ここでは、設定ファイルpostgresql.confのパラメーターの調整（上記のCの対処）により、コンフリクトの発生を緩和する方法について説明します。
初めに、プライマリー側のどの処理が原因でコンフリクトが発生したのかを特定します。原因となる主な処理は以下のとおりです。詳細は、ポイントの1つめ、および、2つめの項目を参考にしてください。

• 明示的な排他ロック
• DDLによる更新 / 削除
• テーブル空間の削除
• データベースの削除
• 行データの更新 / 削除後のVACUUM処理（HOT処理も含む）：通常このケースが多い

次に、コンフリクトを緩和するための2種類の方法を示します。コンフリクトの原因に応じて、必要な対処を行ってください。

スタンバイ側でのWAL適用の待ち時間を延長する

この方法は、コンフリクトを発生させるすべての原因において有効な対処です。参照処理（トランザクション）の実行に必要な時間がある程度わかっている場合には、WAL適用の待ち時間を長くすることで、参照処理がキャンセルされるまでの時間を延長します。
そのためには、スタンバイ側の設定ファイルpostgresql.confの以下のパラメーターを調整します。

max_standby_streaming_delay

ストリーミングレプリケーションからWALデータを受け取って適用する処理が待たされた際、スタンバイサーバーの参照処理をキャンセルするまでの待ち時間を設定します（デフォルトは30秒）。

max_standby_archive_delay

WALアーカイブからWALデータを読み込んで適用する処理が待たされた際、スタンバイサーバーの参照処理をキャンセルするまでの待ち時間を設定します（デフォルトは30秒）。

【例】WAL適用の待ち時間を、デフォルトの30秒から60秒に変更します。

max_standby_streaming_delay = 60s
max_standby_archive_delay = 60s

ただし、スタンバイ側でコンフリクトが発生したときのWAL適用の開始が遅れることで、以下の影響があります。そのため、システムの要件に合わせて設定値を調整する必要があります。

• プライマリー側とスタンバイ側でデータの状態が乖離する
• スタンバイ側で適用すべきWALが蓄積されることで、ディスクの圧迫やフェイルオーバーの時間が長くなる

プライマリー側のVACUUM処理を延期させる

この方法は、プライマリー側のVACUUM処理に起因するコンフリクトを緩和します。特に、テーブルデータの更新頻度が高い場合に効果があります。VACUUM処理を遅らせるためのパラメーターは次の2種類があります。

vacuum_defer_cleanup_age

プライマリー側の設定ファイルpostgresql.confのパラメーターです。プライマリーサーバーにおいて、VACUUMおよびHOT更新が不要になった行データを回収する際、指定されたトランザクションの数だけ遅延させます（デフォルトは0）。

【例1】行データのVACUUM処理を20トランザクション分、遅らせるよう設定します。

vacuum_defer_cleanup_age = 20

hot_standby_feedback

スタンバイ側の設定ファイルpostgresql.confのパラメーターです。onに設定することで、スタンバイ側で現在処理を行っているトランザクションの参照処理に関する情報が、プライマリー側（または上位サーバー）にフィードバックされます。すると、プライマリー側において、スタンバイ側でアクセス中の行データについてのVACUUM処理を延期させることができます。これにより、コンフリクトの原因となるWALの転送がすぐに行われなくなり、コンフリクトが緩和されます（デフォルトはoff）。

【例2】プライマリー側にフィードバックを送るよう設定します。

hot_standby_feedback = on

ただし、これらの設定を行うことで、プライマリー側のVACUUM処理において回収できない行データが増え、テーブルサイズが大きくなる可能性があります。特に、例2においては、スタンバイ側でトランザクションが終了せずに残存すると、プライマリー側に不要な行データが急増してしまうため、注意が必要です。また、トランザクションID周回による問題にもつながることがあります。

ポイント

コンフリクトの課題を解決する上で参考になる情報を示します。

コンフリクトが発生したときのDETAILメッセージについて

コンフリクトが発生すると、FATAL/ERRORメッセージとともにDETAILメッセージが出力され、その内容からコンフリクトの原因を特定することができます。以下にコンフリクトの原因とDETAILメッセージの対応表を示します。

• 注2
  行データの不要領域を回収すること。その結果、対象の行データは完全に削除される。
• 注3
  実行されるトランザクションのトランザクションID(XID)などを記録したもの。複数のトランザクションの同時実行を実現するため、実行されるトランザクションは、自身のXIDと行データに記録されているXIDを比較するなどして、その行データが可視（アクセス可能）であるか、不可視（アクセス不可）であるかを判断する。
• 注4
  テーブルやインデックスを格納するときの8,192バイト単位のブロック領域。テーブルやインデックスにアクセスする際には、このページ単位で共有メモリー上にキャッシュされる。

pg_stat_database_conflictsビューについて

スタンバイ側でpg_stat_database_conflictsビューを確認することで、コンフリクトのためにキャンセルされた問い合わせの回数を調べることができます。以下に、確認結果の例を示します。

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_database_conflicts where datname = 'postgres';
-[ RECORD 1 ]----+---------
datid            | 13892
datname          | postgres
confl_tablespace | 0     → テーブル空間が削除されたことによりキャンセルされた問い合わせの回数
confl_lock       | 0     → ロック時間切れのためにキャンセルされた問い合わせの回数
confl_snapshot   | 74    → 古いスナップショットのためにキャンセルされた問い合わせの回数
confl_bufferpin  | 0     → バッファに対するPIN(ロック)のためにキャンセルされた問い合わせの回数
confl_deadlock   | 0     → デッドロックのためにキャンセルされた問い合わせの回数

スタンバイ側での参照処理を優先する方法について

スタンバイ側においてmax_standby_streaming_delayやmax_standby_archive_delayに-1を指定することで、参照処理はコンフリクトが発生してもキャンセルされず優先して処理されるようになります。その反面、WALの適用処理が待たされるため、スタンバイ側のWALがあふれる可能性があるため注意が必要になります。

レプリケーションスロットの利用について

スタンバイ側で必要とするWALがプライマリー側で消失しないよう保持するための機構として、レプリケーションスロットがあります。この機構は、スタンバイ側でのWAL適用を遅らせる効果もあるため、上記の例2の代わりに利用することができます。ただし、レプリケーションスロットの作成／削除関数を使ってレプリケーションスロットの管理を行う必要性があり、かつ、プライマリー側でWALファイルを大量に保持することでディスクを圧迫することにつながるため、利用に際しては注意が必要です。

参考

PostgreSQL 14文書

• Documentation（PostgreSQLオフィシャル）
  • III. Server Administration
    • 27. High Availability, Load Balancing, and Replication
      • 27.2. Log-Shipping Standby Servers
        • 27.2.6. Replication Slots
      • 27.4. Hot Standby
        • 27.4.2. Handling Query Conflicts
• PostgreSQL日本語ドキュメント（日本PostgreSQLユーザ会）
  • III. サーバの管理
    • 27. 高可用性、負荷分散およびレプリケーション
      • 27.2. ログシッピングスタンバイサーバ
        • 27.2.6. レプリケーションスロット
      • 27.4. ホットスタンバイ
        • 27.4.2. 問い合わせコンフリクトの処理

2022年1月28日公開

こちらもおすすめ

• ストリーミングレプリケーション ～ 仕組み、構成のポイント ～
• パフォーマンスチューニング9つの技 ～「基盤」について～

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