PostgreSQL 13 ストリーミングレプリケーション コンフリクトの考察

2020.12.29
スポンサーリンク

PostgreSQLはストリーミングレプリケーションと呼ばれるデータコピーの仕組みがあります。レプリケーションの構成によっては、マスターサーバの更新がスタンバイサーバに反映されるのを待ってからCOMMITを返すホットスタンバイの構成を採用する事もできます。しかし、ホットスタンバイの構成は、スタンバイサーバへの参照系クエリとWALの適用がコンフリクト（競合）し待ち状態になる事もあります。

このページではコンフリクトの発生原因とその緩和方法についてまとめます。

動作確認構成

以下の環境で動作確認を行います。

+------------------+     +------------------+
| 192.168.56.10/24 |     | 192.168.56.11/24 |
|    centos10      |---->|    centos11      |
|    (primary)     |     |   (secondary)    |
+------------------+     +------------------+

事前準備

マスターサーバの構築

postgresql.confおよびpg_hba.confに以下を加筆します。

スタンバイサーバへのWAL適用時のコンフリクト（競合）を観察したいので、synchronous_commitはremote_applyにします。

cat << EOF >> /var/lib/pgsql/13/data/postgresql.conf 

listen_addresses = '0.0.0.0'
max_wal_senders = 10
synchronous_commit = 'remote_apply'
synchronous_standby_names = 'centos11'
EOF

cat << EOF >> /var/lib/pgsql/13/data/pg_hba.conf

host    replication     postgres        192.168.56.0/24  trust
EOF

再起動し設定を反映させます。

systemctl restart postgresql-13.service

スタンバイサーバの構築

pg_basebackupコマンドを使用して、プライマリサーバのデータを1台目スタンバイサーバへコピーします。

pg_basebackup -R -D ${PGDATA} -h 192.168.56.10

postgresql.auto.confのsynchronous_standby_namesにapplication_nameを加筆します。

# vi /var/lib/pgsql/13/data/postgresql.auto.conf 

primary_conninfo = 'user=postgres <omitted> application_name=centos11'

マスターサーバとしての設定をコメントアウトします。

# vi /var/lib/pgsql/13/data/postgresql.conf

#listen_addresses = '0.0.0.0'
#max_wal_senders = 10
#synchronous_commit = 'remote_apply'
#synchronous_standby_names = 'centos11'

postgresqlを起動します。

systemctl start postgresql-13.service

コンフリクトの実験

コンフリクトが発生しやすい状況の作成

マスターサーバへの排他ロックが必要な更新処理とスタンバイサーバへの参照クエリはコンフリクトします。例えば、マスターサーバへのDROP TABLEは排他ロックを取得するので、スタンバイサーバへの参照クエリ実行中はDROP TABLEを実行する事ができません。このような状況が発生すると、デフォルト設定の場合は最大で30秒の待ちが発生します。

PostgreSQL コンフリクトが発生する状況

コンフリクトを観察できるよう30秒以上かかるクエリを作ってみましょう。まずはマスターサーバにpgbenchコマンドを使って、5クライアント10,000トランザクションを更新処理を発生させます。すると、pgbench_historyテーブルに50,000レコードのデータが作成されます。

createdb test
/usr/pgsql-13/bin/pgbench -i test
/usr/pgsql-13/bin/pgbench -c 5 -t 10000 test

pgbench_historyテーブルの直積を求めるクエリを実行します。このクエリが30秒以上かかる事を確認します。

psql test -c "SELECT count(*) FROM pgbench_history t1, pgbench_history t2;"

コンフリクトの再現

スタンバイサーバにて30秒以上の時間がかかるクエリを実行します。

psql test -c "SELECT count(*) FROM pgbench_history t1, pgbench_history t2;"

スタンバイサーバの実行中に、プライマリサーバにてDROP TABLEを発行します。

psql test -c "DROP TABLE pgbench_history;"

30秒経過するとスタンバイ側のクエリがキャンセルされ、プライマリ側のDROP DATABASEが実行されます。30秒経過すると、スタンバイサーバとプライマリサーバで以下のような標準出力が得られます。

[postgres@centos10 ~]$ psql test -c "DROP TABLE pgbench_history;"
DROP TABLE
[postgres@centos10 ~]$ 

[postgres@centos11 ~]$ psql test -c "select count(*) from pgbench_history t1, pgbench_history t2;"
ERROR:  リカバリで競合が発生したためステートメントをキャンセルしています
DETAIL:  ユーザリレーションのロックを長く保持し過ぎていました
[postgres@centos11 ~]$

スタンバイサーバでpg_stat_database_conflictsテーブルを参照すると、コンフリクト（競合）が発生した回数を観察する事ができます。

testデータベースのconfl_lockがカウントアップされているのが分かります。

[postgres@centos11 ~]$ psql -c "select * from pg_stat_database_conflicts;"
 datid |  datname  | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock 
-------+-----------+------------------+------------+----------------+-----------------+----------------
 14384 | postgres  |                0 |          0 |              0 |               0 |              0
     1 | template1 |                0 |          0 |              0 |               0 |              0
 14383 | template0 |                0 |          0 |              0 |               0 |              0
 16557 | test      |                0 |          1 |              0 |               0 |              0
(4 rows)

[postgres@centos11 ~]$

コンフリクトの緩和策

コンフリクトに関するパラメタ

プライマリサーバに設定可能なパラメタは以下の通りです。

パラメタ 意味
vacuum_defer_cleanup_age VACUUMやHOTの物理削除を延期させるトランザクション数を指定。VACUUMやHOTの物理削除がコンフリクトを頻発させる場合は緩和策となりうる。

スタンバイサーバに設定可能なパラメタは以下の通りです。

パラメタ 意味
max_standby_archive_delay アーカイビングからのWALを適用する場合、スタンバイへのクエリと競合した場合にどの程度待つかを指定するパラメタ。デフォルトは30秒。-1を指定した場合は永久に待ち続ける。
max_standby_streaming_delay プライマリからのWALを適用する場合、スタンバイへのクエリと競合した場合にどの程度待つかを指定するパラメタ。デフォルトは30秒。-1を指定した場合は永久に待ち続ける。
hot_standby_feedback スタンバイサーバで実行している処理をプライマリサーバへフィードバックを行う。VACUUMやHOTの物理削除がコンフリクトを頻発させる場合は緩和策となりうる。デフォルトはoff
wal_receiver_status_interval hot_standby_feedbackをonにした場合のフィードバックを返す頻度。デフォルトは10秒間隔

max_standby_streaming_delay

スタンバイサーバのmax_standby_streaming_delayを3秒に設定します。

max_standby_streaming_delay = 3
EOF

再起動し設定を反映させます。

systemctl restart postgresql-13.service

先ほどと同様の競合（コンフリクト）を発生させます。max_standby_streaming_delayで指定した3秒でSELECTがキャンセルされた事が分かります。

[postgres@centos10 ~]$ psql test -c "DROP TABLE pgbench_history;"
DROP TABLE
[postgres@centos10 ~]$

[postgres@centos11 ~]$ psql test -c "SELECT count(*) FROM pgbench_history t1, pgbench_history t2;"
ERROR:  リカバリで競合が発生したためステートメントをキャンセルしています
DETAIL:  削除されるべきバージョンの行をユーザ問い合わせが参照しなければならなかった可能性がありました。
[postgres@centos11 ~]$
タイトルとURLをコピーしました