レプリケーション遅延の原因と監視、MySQL・PostgreSQL

読み取り用のレプリカを置いたら、更新した直後の画面に古いデータが出る。夜間のバッチのあと、レプリカが何十分も遅れている。この記事はそうしたレプリケーション遅延について、遅れを正しく測る方法、原因の見分け方、減らし方を MySQL と PostgreSQL の両方で説明します。

レプリケーション遅延とは

レプリケーションは書き込みを受ける DB (ソース、プライマリ) の変更を、別の DB (レプリカ、スタンバイ) に送って同じ状態を保つ仕組みです。MySQL はバイナリログ、PostgreSQL は WAL (先行書き込みログ) という変更の記録を送ります。

レプリケーション遅延は、ソースでコミットされた変更がレプリカに反映されるまでの遅れです。ふつうの非同期レプリケーションでは、ソースはレプリカの反映を待たずにコミットを完了するので、遅延は常に少しはあります。問題になるのは、それが業務に影響するほど大きくなったときです。

遅延が大きくなると、次のことが起きます。

読み取りをレプリカに振っているアプリで、利用者が更新した直後に古いデータが見える
ソースが壊れてレプリカに切り替えたとき、反映されていなかった分の更新が失われる
レプリカに送れていない変更の記録 (バイナリログや WAL) がソースにたまり、ディスクを圧迫することがある
集計や分析をレプリカで行っている場合、結果が古くなる

遅延は「受け取り」と「適用」の 2 段で起きる

調べる前に押さえておきたいのは、レプリケーションが 2 段に分かれていることです。

受け取り: レプリカがソースから変更の記録を受け取り、自分のディスクに書く
適用: 受け取った記録を、レプリカのデータに反映する

MySQL では、受け取りをレシーバースレッド (I/O スレッド)、適用をアプライヤースレッド (SQL スレッド) が受け持ちます。PostgreSQL では WAL レシーバーが受け取り、起動プロセスが適用します。

受け取りで遅れているなら、ネットワークやソースの側を疑います。適用で遅れているなら、レプリカの性能や、適用しにくい大きな変更を疑います。どちらで遅れているかを先に見分けると、原因を絞りやすくなります。

MySQL で遅延を確かめる

SHOW REPLICA STATUS を読む

レプリカで次を実行します。MySQL 8.0.22 より前は SHOW SLAVE STATUS という名前でした。

SHOW REPLICA STATUS\G

まず見るのは次の項目です。

項目	見ること
`Replica_IO_Running`	`Yes` なら受け取りが動いている。`Connecting` は接続できていない
`Replica_SQL_Running`	`Yes` なら適用が動いている
`Last_IO_Error`、`Last_SQL_Error`	止まった理由。空なら問題なし
`Seconds_Behind_Source`	遅延の秒数の目安
`Source_Log_File`、`Read_Source_Log_Pos`	受け取りがソースのどのファイルのどこまで進んだか
`Relay_Source_Log_File`、`Exec_Source_Log_Pos`	適用がソースのどのファイルのどこまで進んだか
`Retrieved_Gtid_Set`、`Executed_Gtid_Set`	GTID を使っている場合の、受け取った分と適用した分

Replica_IO_Running か Replica_SQL_Running が Yes でなければ、遅延ではなくレプリケーションが止まっています。Last_IO_Error や Last_SQL_Error を見て、その原因から直します。

受け取りと適用のどちらで遅れているかは、位置を比べると分かります。ソースで SHOW BINARY LOG STATUS (MySQL 8.0 では SHOW MASTER STATUS) を実行して、いまのファイルと位置を見ます。それとレプリカの Source_Log_File と Read_Source_Log_Pos がほぼ同じなら、受け取りは追いついています。そのうえで Exec_Source_Log_Pos が離れていれば、適用で遅れています。

Seconds_Behind_Source の弱点

Seconds_Behind_Source はいちばんよく見られる値ですが、意味を知らずに使うと見誤ります。

この値は、レプリカがいま適用しているイベントについて、ソースで記録された時刻とレプリカの現在時刻の差です。つまり、測っているのは「適用」が「受け取った記録」からどれだけ遅れているかです。受け取りそのものが遅れていると、適用はすぐ受け取った分に追いついてしまい、ソースから大きく遅れていても 0 と表示されることがあります。MySQL のドキュメントも、この値はネットワークが速い場合にだけ役に立つと書いています。

ほかにも次の点に気をつけてください。

適用のスレッドが止まっていると NULL になる。監視で NULL を 0 と扱うと、止まっていることに気づけない
適用するものがないときは 0 になる。ソースへの書き込みがないあいだは、受け取りが止まっていても 0 に見えることがある
ソースとレプリカの時計がずれると、値が信用できなくなる
複数のワーカーで並列に適用している場合 (MySQL 8.4 では既定で 4 ワーカー)、値は Exec_Source_Log_Pos に基づくので、最後にコミットされたトランザクションの位置を正確には表さない

ワーカーごとの状況を見る

並列に適用している場合は、Performance Schema でワーカーごとに、何をいつ適用したかを見られます。

SELECT
  WORKER_ID,
  SERVICE_STATE,
  LAST_APPLIED_TRANSACTION,
  LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,
  LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
  APPLYING_TRANSACTION,
  APPLYING_TRANSACTION_START_APPLY_TIMESTAMP,
  LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_worker;

LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP は元のソースでコミットされた時刻、LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP はこのレプリカで適用し終えた時刻です。2 つの差がそのトランザクションの遅延です。APPLYING_TRANSACTION_START_APPLY_TIMESTAMP が古いまま動かないワーカーがあれば、大きなトランザクションの適用に時間がかかっています。

PostgreSQL で遅延を確かめる

プライマリの pg_stat_replication を見る

PostgreSQL では、プライマリの pg_stat_replication に、つながっているスタンバイごとに 1 行が出ます。

SELECT
  application_name,
  client_addr,
  state,
  sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS not_sent_bytes,
  pg_wal_lsn_diff(sent_lsn, flush_lsn)            AS not_flushed_bytes,
  pg_wal_lsn_diff(flush_lsn, replay_lsn)          AS not_replayed_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

LSN は WAL の中の位置で、pg_wal_lsn_diff() で 2 つの位置の差をバイト数で出せます。上の SQL は遅れを 3 つに分けています。

列	大きいときに疑うこと
`not_sent_bytes`	プライマリがまだ送れていない。プライマリの負荷やネットワーク
`not_flushed_bytes`	送ったがスタンバイのディスクに書き終わっていない。ネットワークやスタンバイのディスク
`not_replayed_bytes`	受け取ったが適用が追いつかない。スタンバイの性能や、スタンバイ上のクエリとの衝突

write_lag、flush_lag、replay_lag は時間で表した遅れです。replay_lag は、プライマリで WAL を書いてから、スタンバイで適用し終えたという知らせが返ってくるまでの時間です。非同期のスタンバイでは、変更がスタンバイのクエリから見えるようになるまでの遅れの近似として使えます。

これらの時間の列には注意点があります。スタンバイが追いついて WAL の流れがなくなると、しばらく直前の値を表示したあとで NULL になります。また、今後追いつくまでにかかる時間の予測ではありません。監視で NULL をどう扱うか (データなしとするか、0 とするか) を決めておく必要があります。

スタンバイ側で見る

スタンバイの上では次の関数で受け取りと適用の位置を見られます。

SELECT
  pg_last_wal_receive_lsn() AS received,
  pg_last_wal_replay_lsn()  AS replayed,
  pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_pending_bytes,
  now() - pg_last_xact_replay_timestamp() AS since_last_replayed_xact;

since_last_replayed_xact は、最後に適用したトランザクションがプライマリでコミットされてからの時間です。プライマリへの書き込みがないと、遅れていなくてもこの値は増え続けます。夜間のように書き込みが少ない時間帯に誤って警報を鳴らさないよう、replay_pending_bytes と組み合わせて判断します。

どちらでも使える測り方: ハートビート表

Seconds_Behind_Source や replay_lag の弱点を避けるには、ソースに時刻を書き込み、レプリカでその時刻を読む方法があります。ハートビート表と呼ばれるやり方です。

-- ソースで 1 回だけ作る
CREATE TABLE replication_heartbeat (
  id INT PRIMARY KEY,
  ts TIMESTAMP(6) NOT NULL
);

ソースで 1 秒ごとに時刻を書き込みます (MySQL の例)。

REPLACE INTO replication_heartbeat (id, ts) VALUES (1, NOW(6));

レプリカで、書かれた時刻と現在時刻の差を見ます。

SELECT TIMESTAMPDIFF(MICROSECOND, ts, NOW(6)) / 1000000 AS lag_seconds
FROM replication_heartbeat
WHERE id = 1;

PostgreSQL なら、INSERT ... ON CONFLICT (id) DO UPDATE SET ts = now() で書き込み、スタンバイで SELECT now() - ts FROM replication_heartbeat を見ます。

この方法は受け取りと適用の両方を含めた「ソースで書いたものがレプリカで読めるまで」の遅れを測ります。書き込みのない時間帯でも 1 秒ごとに更新が流れるので、値が 0 や NULL にごまかされません。ソースとレプリカの時計が NTP などで合っていることが前提です。

レプリケーション遅延の主な原因

大きなトランザクション

数百万行を 1 つのトランザクションで更新・削除すると、レプリカはそれを受け取ってから、まとめて適用します。ソースで 10 分かかった処理は、レプリカでもおおむね同じくらいかかり、その間は後ろの変更も待たされます。夜間のバッチのあとで遅延が跳ね上がるのは、多くがこれです。

対策は大きな更新を数千行ずつのような小さな単位に分けてコミットすることです。古いデータの削除なら、DELETE ... LIMIT を繰り返す、日付で区切って消す、といった形にします。

主キーのないテーブル (MySQL)

MySQL の行ベースのレプリケーションでは、レプリカは UPDATE や DELETE の対象の行を探して書き換えます。主キーか、NOT NULL の列だけでできた一意のインデックスがあれば、それで 1 行ずつ探せます。どちらもないテーブルでは、レプリカはテーブル全体を読みながら対象の行を探すことになります。大きなテーブルでこれが起きると、ソースでは一瞬だった更新が、レプリカでは長くかかります。

遅延が特定のテーブルの更新と重なっているなら、そのテーブルに主キーがあるか確かめてください。主キーのないテーブルは次の SQL で探せます。

SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS c
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND c.TABLE_NAME = t.TABLE_NAME
 AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
  AND c.CONSTRAINT_NAME IS NULL;

レプリカの性能が足りない

レプリカのインスタンスがソースより小さい、ディスクの I/O が遅い、といった場合は、ソースの書き込みに適用が追いつきません。書き込みの量がふだんから多いシステムで遅延がじわじわ増えていくなら、これを疑います。レプリカの CPU やディスクの使用率を見て、ソースと同じ程度の性能にそろえます。

レプリカ上の重いクエリ (PostgreSQL)

PostgreSQL のスタンバイでは、スタンバイで実行中のクエリが見ている行を、プライマリから来た WAL が消そうとすることがあります。この衝突が起きると、スタンバイは WAL の適用を止めてクエリが終わるのを待ちます。待つ時間の上限が max_standby_streaming_delay (既定 30 秒) で、それを超えるとクエリが canceling statement due to conflict with recovery というエラーで取り消されます。

つまり、スタンバイで長い集計クエリを流すと、そのあいだ適用が止まって遅延が増えます。上限を -1 にするとクエリは取り消されませんが、遅延はいくらでも増えます。

hot_standby_feedback を on にすると (既定は off)、スタンバイで使っている行をプライマリの VACUUM が消さなくなるので、衝突によるクエリの取り消しが減ります。代わりに、プライマリで不要な行が残ってテーブルが肥大化することがあります。どちらを優先するかはスタンバイの用途で決めます。切り替え用の待機系なら遅延を小さく保ち、集計用なら遅延を許す、といった分け方です。取り消しの件数と理由は、スタンバイの pg_stat_database_conflicts で確かめられます。肥大化については PostgreSQL の VACUUM と autovacuum で説明しています。

ネットワーク

ソースとレプリカが別のリージョンにある、帯域が足りない、といった場合は受け取りで遅れます。MySQL なら受け取り位置の比較、PostgreSQL なら not_sent_bytes や not_flushed_bytes が大きいことで分かります。

遅延が続くとソースのディスクが埋まることがある

PostgreSQL でレプリケーションスロットを使っている場合、スロットはスタンバイがまだ受け取っていない WAL を消さずに残します。スタンバイが止まったまま放置されると、WAL がプライマリにたまり続けます。max_slot_wal_keep_size の既定は -1 (無制限) なので、放っておくとディスクがいっぱいになることがあります。

SELECT
  slot_name,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

active が false で retained_bytes が増え続けているスロットがあれば、使われていないスロットが WAL を抱えています。

Amazon RDS と Aurora のメトリクス

Amazon RDS のリードレプリカでは、CloudWatch の ReplicaLag が遅延の秒数です。MySQL、MariaDB、PostgreSQL などのリードレプリカに使えます。MySQL では SHOW REPLICA STATUS の Seconds_Behind_Source をもとにした値なので、上で書いた弱点もそのまま当てはまります。レプリケーションが動いていないときや、RDS が遅延を判定できないときは -1 になります。監視では -1 を「遅延なし」と扱わないようにしてください。

RDS for PostgreSQL では、OldestReplicationSlotLag (いちばん遅れているレプリカが受け取っていない WAL の量) と TransactionLogsDiskUsage (WAL が使っているディスク) も合わせて見ると、前の節のスロットの問題に気づけます。

Aurora のレプリカはストレージを共有する別の仕組みなので、AuroraReplicaLag (単位はミリ秒) を見ます。Aurora MySQL をバイナリログで別のクラスターに複製している場合は AuroraBinlogReplicaLag です。

閾値の決め方

どのくらいの遅延で警報を鳴らすかは、レプリカの使い方で決まります。

更新直後の画面をレプリカから読んでいるなら、数秒の遅延でも利用者に見える
切り替え用の待機系なら、遅延がそのまま障害時に失うデータの量になる
集計用なら、数分から数十分の遅延を許せることも多い

遅延はバッチの時間帯に一時的に増えることがよくあります。1 回の値ではなく、一定の時間続いたら知らせる、ふだんの同じ時間帯と比べて大きければ知らせる、といった決め方にすると、鳴りすぎを防げます。止まっている (NULL や Replica_SQL_Running = No) 場合は、遅延の大きさとは別にすぐ知らせるようにしてください。監視の閾値の考え方はアラートの閾値の決め方でも扱っています。

この確認を続けるには

ここで紹介した SHOW REPLICA STATUS や pg_stat_replication の値、ハートビート表の遅延を cron で定期的に記録する、CloudWatch の ReplicaLag や AuroraReplicaLag にアラームを設定する、といった方法で監視を続けられます。遅延の値だけでなく、止まっていないか、ディスクに変更の記録がたまっていないかも一緒に見ておくと安心です。

AI に任せる方法もあります。Scandock は、接続文字列を登録すると AI がデータベースの構成を読んで監視項目を決め、固定の閾値ではなく、値の推移や過去の通知の結末を踏まえて異常かどうかを判断するサービスです。異常と判断すると、読み取り専用のクエリで関連するデータを調べ、原因・影響・対処案をつけて Slack やメールなどに知らせます。MySQL、PostgreSQL、Amazon RDS などに対応しています。