Posted inデータベース

PostgreSQL・MySQLのレプリケーション遅延を完全制御：監視・診断・同期遅延の解消法

Database tutorial - IT technology blog
Database tutorial - IT technology blog

レプリケーション遅延に悩まされた夜

6ヶ月前、チームは深刻な本番インシデントに直面しました。ユーザーが古いデータを参照しており、決済が完了したにもかかわらず注文が「保留中」のまま表示されていました。原因は何だったか？トラフィックスパイク中に、MySQLリードレプリカのレプリケーション遅延が気づかないうちに40秒以上に膨れ上がっていたのです。アラートも、可視性も、ランブックもありませんでした。

MySQL、PostgreSQL、MongoDBを使った十数件のプロジェクトを経験してきましたが、それぞれに強みがあります。ただ、レプリケーション遅延はどのデータベースを選んでも容赦しません——最も予期しない瞬間、たいてい夜11時のトラフィックスパイク中に顔を出す障害モードです。このガイドは、あのインシデントの前に手元に置いておきたかったものです。

ステップゼロ：今すぐ遅延を確認する

まず最初に、ターミナルを開いて以下のどちらかを実行してください。5分もかかりません。スキップしないでください。

MySQL：レプリカステータスの確認

レプリカサーバーにSSH接続して実行します：

SHOW REPLICA STATUS\G
-- または古いMySQLバージョンの場合：
SHOW SLAVE STATUS\G

特に注目すべき2つの値：

  • Seconds_Behind_Source（または Seconds_Behind_Master）— レプリカがプライマリより遅れている秒数。本番環境では5秒を超えたら要注意です。
  • Replica_SQL_Running — 必ず Yes である必要があります。No の場合、レプリケーションが完全に停止しています。

PostgreSQL：レプリケーション遅延の確認

プライマリサーバーで、組み込みのレプリケーションビューを照会します：

SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

レプリカ自体では、より簡単に確認できます：

SELECT
  now() - pg_last_xact_replay_timestamp() AS replication_delay;

NULL が返る場合、レプリカはまだトランザクションをリプレイしていません。高負荷なシステムでは、すぐに調査する必要があります。

レプリケーション遅延の本当の原因

間違った対処法を適用すると何時間も無駄になります。複数のシステムでこの問題を追いかけた結果、遅延の原因は3つの異なる箇所に絞られることがわかりました：

1. 書き込みスループットのスパイク

プライマリがレプリカの適用速度を超える速さで書き込みを受け付ける状態です。業務時間中に実行されるバルクインポートやバッチジョブが主な原因です。デフォルトでは、レプリカのSQLスレッドはトランザクションを1つずつ順番に適用します——このシリアルなボトルネックが高負荷時に致命的になります。

2. レプリカ上の長時間トランザクション

MySQL：レプリカ上で長時間の SELECT を実行すると、メタデータロックが取得され、SQLスレッドが更新を適用できなくなります。PostgreSQL：hot_standby_feedback = on が設定されていると、プライマリが古い行バージョンを保持し続け、スタンバイ上の長時間クエリが独自のバックログを生み出します。どちらも遅延のように見えますが、対処法は異なります。

3. ネットワーク・I/Oボトルネック

レプリカのディスクI/Oが遅いと書き込み遅延が発生します。ネットワーク回線が飽和すると、バイナリログやWALが送信段階で滞ります。PostgreSQLではこれを簡単に特定できます——pg_stat_replication の write_lag、flush_lag、replay_lag カラムが、時間が失われている箇所を正確に示してくれます。

遅延ステージの特定（PostgreSQL）

-- 各遅延ステージを比較
SELECT
  application_name,
  write_lag,    -- WALをレプリカのOSバッファに書き込む時間
  flush_lag,    -- レプリカのディスクにWALをフラッシュする時間
  replay_lag    -- 変更を適用する時間
FROM pg_stat_replication;

write_lag が高い？ → ネットワーク問題。flush_lag が高い？ → レプリカのディスクI/O問題。replay_lag が高い？ → CPUプレッシャーか、WAL適用をブロックする長時間クエリのどちらかです。

自動監視：深夜3時の手動確認は機能しない

1時間ごとにレプリカへSSH接続することはできません。代わりにアラートを受け取る方法を紹介します。

Prometheus + postgres_exporter

各レプリカに postgres_exporter をインストールします。pg_replication_lag がすぐに利用できます。

docker run -d \
  --name postgres-exporter \
  -e DATA_SOURCE_NAME="postgresql://monitor_user:password@localhost:5432/postgres?sslmode=disable" \
  -p 9187:9187 \
  quay.io/prometheuscommunity/postgres-exporter

次に、Prometheusにアラートルールを追加します：

groups:
  - name: postgres_replication
    rules:
      - alert: PostgresReplicationLagHigh
        expr: pg_replication_lag > 30
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "{{ $labels.instance }} のレプリケーション遅延が {{ $value }}秒になっています"

MySQLレプリカ遅延監視スクリプト

cronジョブとして実行したり、既存のアラートスタックに組み込んだりできるPythonスクリプトです：

import pymysql
import sys

REPLICA_DSN = {
    "host": "replica-host",
    "user": "monitor",
    "password": "secret",
    "database": "information_schema",
}
LAG_THRESHOLD_SECONDS = 10

def check_replica_lag():
    conn = pymysql.connect(**REPLICA_DSN)
    with conn.cursor(pymysql.cursors.DictCursor) as cur:
        cur.execute("SHOW REPLICA STATUS")
        row = cur.fetchone()
        if not row:
            print("ERROR: レプリカステータスなし — これは本当にレプリカですか？")
            sys.exit(2)

        sql_running = row.get("Replica_SQL_Running") or row.get("Slave_SQL_Running")
        lag = row.get("Seconds_Behind_Source") or row.get("Seconds_Behind_Master") or 0

        if sql_running != "Yes":
            print(f"CRITICAL: SQLスレッドが起動していません")
            sys.exit(2)
        if lag > LAG_THRESHOLD_SECONDS:
            print(f"WARNING: レプリケーション遅延は{lag}秒です（閾値: {LAG_THRESHOLD_SECONDS}秒）")
            sys.exit(1)

        print(f"OK: レプリケーション遅延は{lag}秒です")
        sys.exit(0)

if __name__ == "__main__":
    check_replica_lag()

MySQLで並列レプリケーションを有効化する

書き込みスループットが高い場合は、マルチスレッドレプリケーションに切り替えましょう。この1つの変更で、夜間バッチインポート時の遅延が80%削減されました——書き込み負荷の高いレプリカで最も効果的なチューニングです：

-- レプリカ側で実行（事前にSTOP REPLICAが必要）
STOP REPLICA;
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_preserve_commit_order = ON;
START REPLICA;

PostgreSQL：max_wal_senders と wal_keep_size のチューニング

レプリカが大幅に遅れると、追いつくために必要なWALセグメントを失う可能性があります——その時点でレプリケーションが壊れ、完全な再同期だけが出口となります。これを防ぐためにバッファを設定しましょう：

# プライマリの postgresql.conf
wal_keep_size = 1024          # 1GBのWALを保持。書き込み量に応じて調整
max_wal_senders = 5
wal_level = replica

レプリケーションスロットはより強力な保証を提供しますが、実際のトレードオフがあります：レプリカが数日間オフラインになると、プライマリがWALを無制限に保持してディスクが満杯になります。本番環境にスロットを導入する前に、保持WALを監視してください：

-- プライマリに物理レプリケーションスロットを作成
SELECT pg_create_physical_replication_slot('replica_slot_1');

-- スロットが保持しているWALを監視
SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

本番6ヶ月で学んだ実践的なTips

1. 遅延チェックなしにレプリカから機密データを読み取らない

決済ステータス、在庫数、ユーザー権限——これらはプライマリから取得するか、まず遅延ゲートを通過させる必要があります：

def get_replication_delay(conn):
    with conn.cursor() as cur:
        cur.execute("SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))")
        return cur.fetchone()[0] or 0

if get_replication_delay(replica_conn) < 5:
    # レプリカからの読み取りが安全
    result = replica_conn.execute(query)
else:
    # プライマリにフォールバック
    result = primary_conn.execute(query)

2. 重いバッチジョブをオフピーク時間帯にスケジュールする

バルクインサートやデータ移行は、午後2時ではなく深夜2〜4時のような低トラフィック時間帯に実行しましょう。MySQLでは、大きな LOAD DATA INFILE 操作を小さなバッチに分割し、SLEEP(0.1) を挟むことも有効です。レプリカがどんどん遅れていくのではなく、息継ぎする余裕を与えることができます。

3. レプリカ上の長時間クエリを監視する

PostgreSQLスタンバイ上のアナリティクスクエリは、WALリプレイを数分間ブロックする可能性があります。重いレポートは専用レプリカにルーティングするか、アナリティクスロールに上限を設けましょう：

ALTER ROLE analytics_user SET statement_timeout = '30s';

4. アラートだけでなくランブックを作成する

ランブックのないアラートは深夜3時のノイズに過ぎません。ページを受け取った担当者は、最初にどのクエリを実行すべきか、エスカレーションパスがどうなっているかを把握している必要があります。インシデントの後、私たちは REPLICATION_LAG_RUNBOOK.md を作成し、PagerDutyアラートの本文に直接リンクしました。もう誰も推測する必要がなくなりました。

5. 必要になる前にレプリカフェイルオーバーをテストする

レプリケーションは、プレッシャーのかかる状況でレプリカを昇格できる場合にのみ有用です。ステージング環境でフェイルオーバー訓練を実施しましょう——PostgreSQLなら pg_ctl promote、MySQLなら STOP REPLICA; RESET REPLICA ALL; を使います。オンコールローテーション全員が少なくとも1回は経験していることを確認してください。初体験が障害対応中であってはなりません。

次のスパイクが来る前にすべきこと

レプリケーション遅延は爆発するまで見えません。あの40秒インシデントで深夜にチームの半数が起こされるまで、何週間も見えていませんでした。PostgreSQLもMySQLも、早期発見に十分な内部メトリクスを提供しています——ただし、誰かが実際に監視している場合に限ります。

上記のクイックチェックコマンドから始めてください。監視の設定は次のスプリントではなく今週中に行いましょう。ランブックは必要になる前に書いておいてください。深夜3時の未来の自分が感謝するはずです。

Share:
Tags: