DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

MySQLレプリケーション遅延の修正：一般的な原因と解決策

レプリカのステータス、I/O、長時間トランザクション、インデックス、並列適用設定を確認して、MySQLレプリケーション遅延を診断および修正します。

MySQLレプリケーション遅延の修正：一般的な原因と解決策

MySQLレプリケーション遅延とは、レプリカがソースより遅れている状態を指します。そのため、そのレプリカからの読み取りで古いデータが返されたり、フェイルオーバーが期待ほど最新でない可能性があります。よくある原因は単純に挙げられるものの、誤解しやすいものです：リレーログのフェッチが遅い、トランザクションの適用が遅い、ディスクの過負荷、ソースでの長時間トランザクション、スキーマのずれ、または書き込みワークロードに合わないレプリケーション設定などです。

このガイドでは、通常最初に確認すべき項目を順を追って説明します：レプリカのステータス、I/O、長時間トランザクション、スキーマのずれ、並列適用設定です。

MySQLレプリケーションの簡単な理解

トラブルシューティングに入る前に、MySQLレプリケーションの基本的な流れを理解しておくと役立ちます。

  1. ソースのバイナリログ（Binlog）: ソースサーバー上のすべてのデータ変更ステートメント（DML）およびスキーマ変更（DDL）は、そのバイナリログに記録されます。このログは、すべての変更の時系列記録として機能します。
  2. レプリカのI/Oスレッド: レプリカ上の専用のI/Oスレッドがソースサーバーに接続し、バイナリログイベントを要求します。その後、これらのイベントをレプリカ上のローカルファイルであるリレーログにコピーします。
  3. レプリカのSQLスレッド: レプリカ上のもう1つの専用スレッドであるSQLスレッドが、リレーログからイベントを読み取り、レプリカのデータベースで実行して変更を適用し、ソースと同期した状態を保ちます。

レプリケーション遅延は、I/Oスレッドがソースからのイベントのフェッチに追いつけない場合、またはより一般的には、SQLスレッドがリレーログからのイベントの適用に追いつけない場合に発生します。

レプリケーション遅延の診断

レプリケーションのステータスと遅延を確認するための主要なツールは、レプリカサーバーでのSHOW REPLICA STATUS（または古いMySQLバージョンではSHOW SLAVE STATUS）コマンドです。

SHOW REPLICA STATUS\G

出力から確認すべき主要なメトリクス：

  • Replica_IO_Running または古い Slave_IO_Running: MySQLのバージョンに応じて、Yesである必要があります。
  • Replica_SQL_Running または古い Slave_SQL_Running: Yesである必要があります。
  • Seconds_Behind_Source または古い Seconds_Behind_Master: これはイベントのタイムスタンプに基づいて遅延を秒単位で推定します。0より大きい値は遅延を示しますが、未適用のトランザクションの数ではありません。
  • Last_IO_Error: ネットワークまたはI/O関連のエラー。
  • Last_SQL_Error: イベント適用中に発生したエラー。

遅延秒数に関する重要な注意: このメトリクスは時間ベースであり、トランザクションベースではありません。ソースが古いイベントタイムスタンプを持つ大きなトランザクションをコミットした場合、レプリカはそのトランザクションを適用している間に大きな遅延値を報告する可能性があります。待機中のトランザクションの数はわかりません。そのため、リレーログサイズ、適用スレッドの状態、サーバーメトリクスと組み合わせて使用してください。

より高度な監視には、Percona Monitoring and Management（PMM）、PrometheusとGrafana、またはレプリケーションメトリクスを経時的に追跡するその他のデータベース固有の監視ソリューションの使用を検討してください。

レプリケーション遅延の一般的な原因と解決策

根本原因を特定することが重要です。以下は、レプリケーション遅延の最も頻繁な理由とその対応する解決策です。

1. ネットワークレイテンシまたは帯域幅の問題

  • 原因: ソースとレプリカ間のネットワーク接続が遅い、または不安定、あるいはバイナリログイベントを迅速に転送するための十分なネットワーク帯域幅がない。
  • 診断: レプリカI/Oスレッドが実行中であるにもかかわらず遅延秒数が高く、Relay_Log_Spaceが大幅に増加していない、またはネットワーク問題に関連するLast_IO_Errorエントリが頻繁に発生する。ping、mtr、tracerouteなどのネットワーク診断ツールを使用して、レイテンシとパケットロスを確認します。
  • 解決策:
    • ネットワークインフラの改善: サーバー間の安定した高帯域幅の接続を確保します。
    • サーバーの同一拠点配置: 理想的には、ソースとレプリカは同じデータセンターまたはクラウドリージョンに配置して、レイテンシを最小限に抑えます。
    • 圧縮: 帯域幅に制約のあるリンクの場合は、MySQLバージョンのレプリケーション接続圧縮オプションを確認してください。圧縮によりネットワークトラフィックを削減できますが、CPUオーバーヘッドが追加され、レプリカをソースの近くに配置する代わりにはなりません。

2. レプリカのI/Oボトルネック

  • 原因: レプリカのディスクサブシステムが、リレーログの書き込みやデータファイルへの変更の適用を十分な速度で行えない。これは特に、sync_binlogまたはinnodb_flush_log_at_trx_commitが1（最大永続性）に設定されている場合に顕著で、頻繁なディスクフラッシュが発生します。

  • 診断: レプリカのtopまたはvmstat出力で高いiowait、高いディスク使用率（iostat -x 1）、および遅延秒数が着実に増加している。Innodb_data_writesやInnodb_data_fsyncsなどのMySQLステータス変数も洞察を提供します。

  • 解決策:

    • より高速なストレージ: レプリカにSSDまたはNVMeドライブにアップグレードします。適切なRAID構成（例：パフォーマンス用のRAID 10）を使用します。
    • 永続性設定の調整（注意して行うこと！）:
      • innodb_flush_log_at_trx_commit: デフォルトは1（最も永続的）。2（OSキャッシュにフラッシュ）または0（1秒に1回フラッシュ）に設定すると、I/Oを大幅に削減できますが、レプリカのクラッシュ時にデータ損失のリスクがあります。レプリカが主要な信頼できる情報源ではなく、レプリカ自体のデータ損失にある程度許容できる場合にのみ、0または2を検討してください。
      • レプリカもバイナリログを書き込む場合、sync_binlogはレプリカにフラッシュオーバーヘッドを追加する可能性があります。これを緩和するとスループットが向上する可能性がありますが、サーバーがクラッシュした場合に最近のバイナリログイベントを失う可能性も高まります。
    # レプリカの /etc/my.cnf 設定例（細心の注意を払って使用）
[mysqld]
innodb_flush_log_at_trx_commit = 2 # または 0、許容範囲に応じて

3. レプリカのリソース競合（CPU、メモリ）

  • 原因: レプリカサーバーのCPUまたはメモリが、特に読み取りクエリも処理している場合に、受信トランザクションを処理および適用するのに不十分である。
  • 診断: topまたはhtopで高いCPU使用率（特にmysqldプロセス）、または高いメモリ使用率。遅延秒数が高く、レプリカSQLスレッドの状態が長時間実行ステートメントを示している可能性があります。
  • 解決策:
    • リソースの増強: レプリカサーバーにより多くのCPUコアとRAMをプロビジョニングします。
    • 専用レプリカ: 可能であれば、レプリカをレプリケーション専用にし、そこからの大量の読み取りクエリの処理を避けます。読み取りが必要な場合は、適切なインデックスを使用して十分に最適化されていることを確認します。
    • クエリの最適化: SQLスレッドとリソースを競合している可能性のある、レプリカで実行されている低速クエリを確認および最適化します。

4. ソースでの低速クエリまたは長時間トランザクション

  • 原因: ソースでの単一の非常に大規模または長時間実行トランザクション（例：ALTER TABLE、LIMITなしの大規模なUPDATE/DELETE、大規模なLOAD DATA INFILE）により、レプリカのSQLスレッドがその期間全体にわたってブロックされ、重大な遅延が発生する可能性があります。レプリカはソースでコミットされたのと同じ方法でトランザクションを適用する必要があるため、これには長い時間がかかる可能性があります。
  • 診断: 遅延秒数が、ソースでの特定の操作と相関する突然の大きなスパイクを示します。これらのイベント中に、ソースの低速クエリログまたはSHOW PROCESSLISTを確認します。
  • 解決策:
    • ソースクエリの最適化: ソースでの長時間実行クエリを特定して最適化します。適切なインデックスを追加します。
    • バッチ処理: 大規模なDELETEまたはUPDATEステートメントを、LIMIT句を使用してより小さな管理可能なバッチに分割します。
    • オンラインスキーマ変更: DDL操作には、Percona Toolkitのpt-online-schema-changeなどのツールを使用して、非ブロッキングスキーマ変更を実行し、レプリケーションへの中断を最小限に抑えます。

5. シングルスレッドレプリケーション（MySQL 5.7以前または特定の構成）

  • 原因: 古いMySQLバージョンでは、SQLスレッドは、ソースで発生した並列トランザクションの数に関係なく、すべてのトランザクションを順次適用していました。ソースが多くの同時書き込みを処理する場合、レプリカ上の単一のSQLスレッドは簡単にボトルネックになる可能性があります。

  • 診断: 遅延秒数が高く、レプリカSQLスレッドの状態が頻繁にアクティブなクエリを示している一方で、レプリカのCPUがすべてのコアで完全に飽和していない可能性があります。

  • 解決策:

    • マルチスレッドレプリケーション: 1つのレプリカSQLスレッドがソースからの同時書き込みに追いつけない場合、並列適用が役立ちます。MySQL 5.6でデータベースベースの並列処理が導入され、後のバージョンで論理クロックベースの並列適用が追加されました。新しいMySQLバージョンではreplica_parallel_workersという用語が使用され、古い構成ではslave_parallel_workersが引き続き使用される場合があります。
    # MTSのためのレプリカの /etc/my.cnf 設定例
[mysqld]
replica_parallel_workers = 4 # 控えめに開始し、その後測定
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = ON # 読み取りにコミット順序が重要な場合に便利
    • レプリケーションの再起動: MTS設定を変更した後、レプリカSQLスレッドを再起動する必要があります。
    STOP REPLICA;
START REPLICA;

6. 最適化されていないスキーマまたはレプリカのインデックス欠落

  • 原因: レプリカのスキーマがソースと異なる場合、または必須のインデックスが欠落している場合、SQLスレッドによって適用されるクエリがソースよりもはるかに遅く実行される可能性があります。これは、スキーマのずれや意図的な違い（例：レプリカ上の異なるレポート用インデックス）が原因で発生する可能性があります。
  • 診断: CPU/I/Oボトルネックと似ていますが、レプリカSQLスレッドの状態またはレプリカの低速クエリログ内の特定のクエリが問題を示している可能性があります。ソースとレプリカで同一のクエリのEXPLAIN計画を比較します。
  • 解決策:
    • スキーマの一貫性: レプリカが、必要なすべてのインデックスを含め、ソースと同一で最適化されたスキーマを持っていることを確認します。
    • インデックスの作成: レプリカから読み取るアプリケーションとSQLスレッド自体の両方にとって、クエリパフォーマンスに重要な欠落しているインデックスをレプリカに追加します。

7. バイナリログ形式（ROW vs. STATEMENT）

  • 原因: STATEMENTベースのレプリケーションは、非決定的なステートメント（例：NOW()、UUID()の使用）がレプリカで異なる結果を生成する可能性があり、複雑なコンテキスト評価が必要になったり、レプリケーションが中断されたりする可能性があるため、問題となることがあります。ROWベースのレプリケーションは実際の行の変更をログに記録するため、一般的により安全で複雑なトランザクションに対して効率的ですが、より大きなバイナリログを生成する可能性があります。

  • 診断: 非決定的なステートメント、ログ位置の欠落、または重複キーエラーに関連する頻繁なLast_SQL_Errorメッセージ。SHOW VARIABLES LIKE 'binlog_format'。

  • 解決策:

    • ROWまたはMIXEDの使用: 一般的に、信頼性と決定性の点から、ほとんどの最新アプリケーションではbinlog_format=ROWが推奨されます。MIXEDは、安全な場合はSTATEMENTを使用し、それ以外の場合はROWを使用する妥協案です。
    # ソースの /etc/my.cnf 設定例
[mysqld]
binlog_format = ROW
    • 注: binlog_formatは多くのMySQLセットアップで実行時に変更できますが、本番トポロジでレプリケーション形式を変更する場合は慎重に計画する必要があります。新しい形式に依存する前に、すべてのレプリカとアプリケーションパターンが互換性があることを確認してください。

レプリケーション遅延を防ぐためのベストプラクティス

以下の習慣を使用して、遅延の再発を減らします。

  • プロアクティブな監視: レプリケーション遅延秒数、サーバーリソース（CPU、I/O、ネットワーク）、バイナリログサイズに対する堅牢な監視を実装します。通常の動作からの逸脱に対してアラートを設定します。
  • 定期的な最適化: ソースとレプリカの両方で低速クエリを定期的に確認および最適化します。インデックスが最新で効果的であることを確認します。
  • ハードウェアのサイジング: レプリケーション負荷と処理する可能性のある読み取りワークロードの両方を見込んで、レプリカサーバーに十分なハードウェアリソース（CPU、RAM、高速ストレージ）をプロビジョニングします。
  • バッチ処理: 大規模なデータ変更に関するベストプラクティスについて開発者と管理者を教育し、バッチ処理またはオンラインスキーマ変更ツールの使用を促進します。
  • GTIDの活用: 直接的な遅延防止ではありませんが、グローバルトランザクション識別子（GTID）は、特にフェイルオーバー時やレプリケーション中断からの回復時にレプリケーション管理を簡素化し、結果として長引く遅延を引き起こす可能性のあるダウンタイムを間接的に削減できます。
  • 最新の状態を保つ: MySQLのバージョンを適度に最新に保ちます。新しいバージョンには、パフォーマンスの向上と強化されたレプリケーション機能（より高度なMTSなど）が含まれていることがよくあります。

最終的なポイント

MySQLレプリケーション遅延をキューイング問題として扱います。レプリカがイベントのフェッチに時間がかかっているのか、リレーログの書き込みに時間がかかっているのか、トランザクションの適用に時間がかかっているのかを特定します。次に、一致する原因（ネットワーク配置、ストレージ、ソースでの長時間トランザクション、欠落したインデックス、並列適用設定）を修正します。遅延とレプリカエラーに関するアラートを維持して、古い読み取りやフェイルオーバープランが古いレプリカに依存する前に、次の速度低下をキャッチします。