データベース

ホスティング運用におけるMySQLレプリケーションのタイムラグを最小化

MySQLレプリケーションラグでは、読み取りノードが古いデータを配信するため、ホスティング操作の可用性が損なわれます。 データベース sync delay 意思決定が遅れる。その原因を認識し、遅れを測定可能にし、目標とする設定やアーキテクチャの変更によって改善する方法を紹介する。 最小限に抑える.

中心点

これ以上深入りする前に、次のステップの影響をよりよく理解できるように、本質を要約しておこう。レプリケーションの待ち時間は、ネットワーク、I/O、クエリプラン、設定の相互作用によって発生します。診断が可能なのは、サーバーのメトリクスやビンログ、リレーログパスを注視している場合のみです。対策は、測定可能な小さなステップで実施し、レイテンシへの影響を継続的に監視するのが最も効果的です。読み取り分散や容量計画などのアーキテクチャの問題は、最適化で十分なのか、スケーリングが必要なのかを決定します。そこで私は、テクノロジー、モニタリング、運用プロセスを組み合わせて クリア ホスティング環境で信頼できるアクションプラン運ぶ.

原因理解するネットワーク、大規模トランザクション、主キーの欠落
診断シャープにする秒_ビハインド_マスター、IO/SQLスレッド、遅いクエリログ
最適化 待つ代わりに：並列レプリケーション、キー、より小さなバッチ
スケーリング 必要な場合：CPU/RAMの増設、リーダーのルーティング、レプリカの追加
モニター そして行動する：アラーム、メンテナンスウィンドウ、定期分析

ホスティングにおけるレプリケーション遅延の原因は何ですか？

典型的なブレーキブロックから始めるが、ほとんどの遅れはいくつかの原因を取り除くことで大幅に減らすことができるからだ。 下げる を残す。ネットワークの待ち時間が長いと、プライマリサーバーからビンログイベントを収集するIOスレッドの動作が遅くなり、その結果、次のような不規則な動作が発生します。 残留物. .しかし、SQLスレッドで最大の遅延が発生するのは、適切な主キーや一意キーがないまま一行ずつ変更を適用しなければならない場合である。これらのキーがない場合、更新や削除は高価なテーブルスキャンを強いることになり、リレーログが詰まってしまいます。行数の多い長いトランザクションは、コミットが完了するまで次のイベントの適用をブロックします。ALTER TABLEのようなDDLオペレーションは、一貫性を維持するために他のレプリケーションプロセスも停止させ、タイムラグにピークを生じさせます。.

ハードウェアと構成も一役買うので、私はいつもCPU、メモリ、I/Oサブシステムを最初にチェックする。SSDが遅かったりフルに使われていたり、InnoDBのバッファ・プールが小さすぎたり、積極的な同期（プライマリ・サーバーでsync_binlog=1など）を行ったりすると、I/Oコストが著しく上昇します。高い. .サイズの小さいレプリカは、次のような問題がある。 ホスティング 読み込み要求や並列書き込みのピークが多くなると、スケーリングが遅れます。ランダムな書き込みが多いワークロードでは、バッファプールへの打撃が大きくなり、チェックポイント作業が発生します。これにレプリカ上で競合するクエリが加わると、SQLスレッドの速度は低下し続けます。.

遅れを診断する：メトリクス、ログ、シグナル

Seconds_Behind_Master（マスターの背後）は時に欺瞞的であったり、遅れたりすることがあるので、私は診断のために単一のシグナルを当てにしない。 ディスプレイ. .SHOW SLAVE STATUSから始めて、Seconds_Behind_Master、Relay_Log_Space、Master_Log_FileとRead_Master_Log_Pos、Slave_IO_RunningとSlave_SQL_Runningフラグを見て、IOスレッドとSQLスレッドを明確に識別する。 セパレート. .Master_Log_FileとRelay_Logファイルに大きな差異がある場合は、ネットワークまたは永続性に問題があることを示しています。SQLスレッドが遅延している場合、レプリカのスロー・クエリ・ログがアプリケーションをブロックしているクエリに関する情報を提供します。また、InnoDBのメトリクスであるrow_lock_waits、履歴リストの長さ、バッファ・プールのヒット・レートをチェックし、メモリとロックのプレッシャーを可視化します。.

オペレーションレベルでの時系列カウント：レプリケーションのラグ、CPU、IOPS、ネットワークのレイテンシー、実行中のDDLの数などを相関させます。バックアップやバッチジョブ、大規模なインポートと並行してラグのピークが見られる場合、その原因を明確に特定することができます。 より速く. .Percona Toolkitのようなツールや、一般的なクラウドのプラットフォーム・メトリクスを使えば、IO/SQLのラグやログの詰まりを簡単に調べることができます。また、アプリケーションがレプリカ上で長い読み取りクエリーを実行し、それがSQLスレッドの不調の原因になっていないかどうかもチェックします。 ブロック. .IOかSQLか、方向性が明確になって初めて、的を絞った対策から始める価値がある。.

MySQLレプリケーションの遅延に対する当面の対策

数秒が刻一刻と過ぎていくとき、私は効果的な小刻みなステップを踏む。滝. .私はレプリカ上で長いクエリーを一時停止し、DDLのメンテナンスウィンドウを設定し、ラグが追いつくまで大規模なバッチ更新を停止する。SQLスレッドが常に更新されるように、バルク操作を小さなパッケージ、例えば1コミットあたり1,000～5,000行に分割します。 走り抜ける. .主キーがない場合は、書き込みの多いテーブルを優先してキーを作成する。IOボトルネックが発生した場合は、InnoDBのバッファプールを増やし、ログファイルをクリーンアップし、SSDに十分な空きブロックがあることを確認して、一定の書き込みレートを実現する。.

ネットワークに明らかなブレーキがある場合は、ノードを近づけるか、レイテンシーの低い接続に最適化します。slave_compressed_protocolによるレプリケーショントラフィックの圧縮は、帯域幅を削減し、タイトな回線に役立ちます。 目立つ. .バイナリロギングが必要なくレプリカ上で実行される場合は、書き込み作業を減らすために一時的に停止します。 チェック).クリティカルな局面では、私は読み込みトラフィックを、あまり混雑していないレプリカで特別に実行したり、ビジネスロジックが許すのであれば一時的にプライマリー・サーバーにルーティングしたりする。目的は常にSQLスレッドを継続的に動作させ、ボトルネックを素早く緩和することです。.

MySQLの重要なパラメータの比較

定期的なセットアップの場合、私は小さなパラメーターのプレイブックを用意しておき、ワークロードとハードウェアに合わせて調整する。 イコライズ. .以下の値は、厳密なデフォルト値ではなく、出発点としての役割を果たすものである。プライマリサーバとレプリカの違いは、セキュリティとクラッシュリカバリが異なることに注意してください。 優先順位 を設定することができる。特にBinlog同期とInnoDBフラッシュ戦略の目的は異なります。コミット・グルーピングの選択もアプリケーションの一貫性と一致しなければなりません。.

パラメータ	目的	代表値プライマリ	典型的なレプリカ値	ヒント
innodb_buffer_pool_size	ホットデータをRAMに保持	60-75% RAM	60-80% RAM	読み取りが多いレプリカほど大きくなる
sync_binlog	ビンログの耐久性	1-100	オフ（ビンログがない場合）または100	1 = 最大限の安全性、より遅い
innodb_flush_log_at_trx_commit	ログフラッシングのやり直し	1	2	2はレプリカを大幅に加速させる
レプリカ並列ワーカー	パラレル・アプリケーション	-	= vCPU 数	作業負荷が並列化できるかどうかをテストする
binlog_group_commit_sync_delay	コミット・バッチ	0-5000 µs	0	レイテンシー／バッチでのみ有効
スレーブ圧縮プロトコル	ネットワーク負荷の軽減	-	オン	限られた帯域幅に対応

これらのパラメーターを設定したら、すぐに2番目の値、コミットレートとIOPSを見て方向性を決める。検証. .新たなタイムラグが発生することなく読み取り性能が向上すれば、その変更は維持される。調整によってコミット時間が長くなったり、タイムアウトが発生したりした場合は、一歩下がって微調整を行う。 調整する ディレイ値やフラッシュ値コンフィギュレーションは一回限りの作業ではなく、テレメトリーによる反復プロセスである。この訓練は、データ量が増大するにつれて、長期的に実を結ぶ。.

ビンログのフォーマット、イベントサイズ、コミット順序

ラグに対する重要な手段は、ビンログフォーマットにある。ROWは決定論的で、確実に複製されるが、より多くのイベントを生成する。量を減らすために、私はbinlog_row_imageをMINIMALに設定し、変更された列だけがイベントに残るようにしています。アプリケーションが大きなテキスト／ブロブ列を頻繁に変更する場合は、すべての列を本当に書き込む必要があるかどうかをチェックします。さらに、binlog_transaction_compressionは、8.0のセットアップでネットワークとI/Oの負荷を減らすのに役立ちます。.

コミット・パラメーターは、スループットと一貫性の関係を考慮して慎重に使います。binlog_order_commitsでは、コミット順序を安定に保ちます。レプリカでは、アプリケーションに依存する場合のみreplica_preserve_commit_orderを設定します。並列アプリケーションを最大化するために、transaction_dependency_tracking=WRITESETと適切なtransaction_write_set_extraction（XXHASH64など）を有効にしています。replica_parallel_type=LOGICAL_CLOCKと合わせて、独立したトランザクションが同時に使用される可能性が高まります。.

並列レプリケーションとGTIDを正しく使用する

並列レプリケーションは、ワークロードが十分な独立したトランザクションを必要とする場合に、私が最も効果的な手段の1つである。 オファー. .replica_parallel_workersをレプリカのvCPU数に設定し、イベント配信が本当に並列処理できるかをチェックする。ホットな単一テーブル更新のあるスキーマでは、その効果は消えてしまう。 を通して. .GTIDは私にとってフェイルオーバーを容易にし、特に複数のレプリカが関与している場合、分岐のリスクを減らす。マスター/レプリカやマルチソースに関するアーキテクチャの質問については、私は以下の詳細なガイドを使うのが好きだ。マスター-スレーブ・レプリケーション, オプションをきれいに比較する。.

準同期レプリケーションを使えば、データ損失のウィンドウは小さくなりますが、プライマリ・サーバーでのレイテンシは大きくなります。私は、ビジネス上の目的でこのセキュリティが明らかに必要な場合にのみ、この機能をオンにします。需要. .バックプレッシャーを監視することは依然として重要である：レプリカが追いつかない場合、コミット時間が長くなり、アプリケーションのレイテンシーが増加する。そのため、私はステージング環境でテストを行い、測定可能なプラスの効果が出てから引き継ぐようにしています。これにより、新たなボトルネックを作ることなく、データパスとユーザーエクスペリエンスのバランスを保つことができる。.

テーブルレイアウト、キー、クエリの最適化

プライマリーキーやユニークキーがなければ、どんな変更も高い代償を払うことになる。鍵. .頻繁に変更されるテーブルには意味のある主キーを選び、頻繁にフィルターされるカラムには必要なセカンダリインデックスを設定します。これにより、SQLスレッドでのスケジュールされたスキャンの回数が減り、binlogイベントの適用が速くなります。 目立つ. .私は大きな更新を小さなアトミックなステップに分割し、LIMITとORDER BY PKで制御しています。長いSELECTをレプリカにカプセル化し、SQLスレッドを常に止めないようにしています。.

私は定期的にレプリカの遅いクエリログをチェックしている。ファイル・ソート、テンポラリの使用、インデックスのないクエリは、すぐに最適化の対象になります。同時に、私はInnoDBの統計情報をチェックし、バッファプールのヒット率が95%以上を維持していることを確認します。90%を下回ると、I/Oが増え、すべてのレプリケーション・ステップが危険にさらされる危険性がある。 より高い. .純粋なクエリー・チューニングでさえ、ラグに大きな影響を与える。.

レプリケーション・ショックのないDDL戦略

DDLはレプリケーションの速度を著しく低下させる可能性があるので、私は追跡可能な小さなステップを形成するように変更を計画します。可能であれば、ALGORITHM=INPLACEまたはINSTANTを使用し、変更中もテーブルが読み取り可能な状態を保ち、SQLスレッドが長時間ブロックされないようにします。大きなテーブルを変換する必要がある場合は、オンライン・アプローチに頼り、リレー・ログが蓄積しないように速度を調整します。長い排他ロックが必要なDDLやカラムを完全に書き換えるDDLは特にクリティカルです。.

ネットワークとストレージ経路の最適化

RTTの高いネットワーク・ルートは、IOスレッドとSQLスレッドの間にアイドル時間を発生させるので、ノード間の距離とホップ数を最小にする。 一貫した. .特に複数のレプリカが同時にプルしている場合は、専用リンクや高品質のピアリングパスが役立ちます。ストレージ・パスでは、安定した書き込み性能を持つSSDに頼り、コントローラにバッテリー保護機能があれば、ライトバック・キャッシュを有効にする。 オファー. .TRIMが有効かどうか、十分なリザーブブロックが空いているかどうかを定期的にチェックし、突然のクラッシュが起きないようにしている。noatimeや適切なI/Oスケジューラなどのファイルシステムとマウントのオプションが、チューニングの連鎖を完成させます。.

I/Oパターンが競合するとレイテンシが増えるので、リレーログを運ぶのと同じデータキャリアにはバックアップをロードしない。 ドライブアップ. .可能であれば、バックアップを別のレプリカに移すか、ホットパス外のスナップショットを使用します。ネットワーク側では、NICのMTUサイズとオフロード機能を見てみる価値があります。最後に、再現可能なベンチマークと実際のプロダクション・メトリクスで効果を検証します。これが、レプリケーション・パスにおける測定可能な利益と認識された利益を分離する唯一の方法である。 クリア.

リソースの分離とノイジー・ネイバー・コントロール

ホスティング業務では、複数のワークロードが同じリソースを奪い合うことがよくある。私は明確な制限を設けている：オペレーティングシステムレベルでは、バックアップとバッチプロセスをcgroups、nice/ionice、I/Oクォータでカプセル化し、レプリカのSQLスレッドが優先されるようにしています。MySQL 8では、リソースグループを使って高価なリーダーを特定のCPUコアにバインドし、レプリケーションワーカーを高速に応答するコアに配置しています。さらに、長い分析クエリを時間制限で制限し、アプリケーションパスの速度を落とさないように意図的に実行スケジュールを組んでいます。.

ホスティング事業におけるスケーリング戦略

ある時点で、最適化だけでは十分でなくなった。 ローラー. .レプリカのCPUとRAMを増やすと、SQLスレッドの速度が上がり、バッファ・プールの容量が増えます。私は読み取り要求を積極的にレプリカにルーティングし、書き込み負荷はプライマリ・サーバーに残すようにしています。 グラブ. .追加のレプリカは読み込み負荷のピークを分散させるが、同じボトルネックが存在する場合、自動的にラグを減らすことはできない。データモデルが実際の分割を必要とする場合、私はシャーディングとレプリケーションなぜなら、書き込みパスが分かれていると、ロードがきれいに分離されるからだ。.

並列ワーカーを増やし、バッファを増やし、バッチを均等にし、ロングランをオフピークの時間帯に移動させる。一般的なサイジングルールをやみくもに採用するのではなく、独自のレイテンシーとスループット曲線を用いて分析することが重要であることに変わりはない。検証. .しきい値が設定された小型のパフォーマンス・ランブックは、運転中の判断をスピードアップします。その結果、測定から調整までの経路が再現可能になります。これにより、MySQLのレプリケーション・ラグを成長しても抑えることができます。 ハンドル.

レプリカの構築、キャッチアップ、トポロジー

クリーンなレプリカの構築は、障害が発生した後に素早くグリーンゾーンに戻れるかどうかを決定する。私は一貫性のあるスナップショットで新しいレプリカをシードし、キャッチアップ時にパラレルワーカーをアクティブにします。キャッチアップフェーズでは、レプリカ上で競合するリーダをスロットルし、SQLワーカーが常に進捗するようにします。大規模な環境では、チェーンではなくファンアウトを選択します。複数のレプリカをプライマリサーバに直接接続するか、いくつかの強力な中間ステージに接続します。長いレプリケーション・チェーンは待ち時間を増やし、個々のリンクが遅れをとるリスクを高める。.

relay_log_recoveryは、有効なリレーログだけが処理されるようにします。relay_log_purgeは、Relay_Log_Spaceが制限内に収まるようにアクティブなままにしておきます。フルデータキャリアでは、どのような最適化でもラグを減らすことができません。.

アプリケーションにおける一貫性パターンと読者ルーティング

技術的なチューニングだけでは十分ではありません。私はアプリケーションのパターンを使って、認識される一貫性を保証しています。書き込み後の読み取りを保証するために、書き込み後に定義された期間、セッションをプライマリ・サーバにルーティングしたり、遅延がしきい値以下のレプリカからの読み取りのみをルータが行う、bounded stalenessを使用したりします。特にセンシティブな読み取りには、レプリカ上でWAIT_FOR_EXECUTED_GTID_SETを使用して、特定のトランザクションセットがすでに適用されていることを確認する。これにより、個々のレイテンシは制御された方法で増加しますが、データパスとユーザーの期待値は維持されます。.

レプリケーションのエラー処理と安定性

レプリケーション・エラーは操作中に避けられないものです。重要なのは、的を絞った再現可能な方法で処理することです。重複キーやnot-foundエラーの場合、私はSQLスレッドを停止し、影響を受けたイベントを分析し、それをスキップするかデータをクリーンアップするかを決定する。GTIDのセットアップでは、一律にスキップすることは控え、必要であれば、影響を受けたGTIDで空のトランザクションを注入し、セットの一貫性を保つようにする。エラーリストと明確なステップのあるランブックは、時間が迫っているときに数分の節約になる。また、しつこく繰り返されるエラーも監視している。不適切なレプリケーション・フィルターや、中期的に乖離を生じさせるような手動のホットフィックスを示していることが多い。.

レプリケーションの耐久性については、耐久性パラメーターのバランスをとる。クラッシュがデータ損失につながらないように、sync_relay_logとsync_relay_log_infoを設定するが、IOパスが過度に遅くなることはない。レプリケーション・リンクのTLS暗号化を考慮に入れています。CPU負荷は増えますが、リスクは軽減されます。高レートの場合、圧縮とTLSを併用することに意味があるのか、それとも暗号オフロードを強化したプロファイルを計画すべきなのかを評価します。.

モニタリング、アラーム、SLO

信頼できるアラームがなければ、どんなチューニングも無駄になる。 しきい値. .例：Seconds_Behind_Masterが300秒を超えるとアラームを出す。IOとSQLのバックログを分析するために、Read_Master_Log_PosとExec_Master_Log_Posの差もモニターしています。 差別化. .各アラームに対する標準的な対策を記したノートブックがある：クエリーのスロットル、バッチの一時停止、DDLの移動、パラメーターの一時的な緩和。介入後、私は効果を記録し、SLOを更新して、会社がすべてのインシデントから学べるようにしています。.

レプリケーションのレイテンシー、コミット率、IOPS、CPU、バッファプールのヒット率、スワップ、ネットワークのRTTなどです。Slave_IO_RunningとSlave_SQL_Runningのプロセスチェックを追加し、障害を早期に認識できるようにしました。スロー・クエリ・ログは常時アクティブのままですが、洗練されたしきい値でログのフラッディングを最小限に抑えます。 避ける. .週次レポートにはトレンドが示され、そこからハードウェアやコンバージョンの予算を割り出す。このようにして、レプリケーションの信頼性は一歩一歩向上し、日常生活でも以下のような方法で最適化されます。数字を占めた。.

驚きのない高可用性とフェイルオーバー

遅延と可用性は関連している。なぜなら、連鎖故障はシステムがすでにストレスを受けているときに発生することが多いからだ。 レプリケーション を開始します。私はGTIDを使ったフェイルオーバー・パスを準備し、テスト環境で切り替えの練習をして、役割の変更が素早くきれいにできるようにしている。 絶え入る. .仮想IPスイッチまたは読み書きトラフィック用のインテリジェント・ルーターは、スイッチ後のミスリードを防ぐ。クラスタとヘルスチェックのための管理ツールは、一刻を争う時に数分を節約する。冗長性とスイッチングに関するより詳細なコンセプトはこちらをご覧ください：高可用性ホスティング.

レプリカを紙屑籠の代わりとして扱わないことが重要であることに変わりはない。読者のルーティングがそこで終わり、ユーザーが迅速なレスポンスを必要とするのであれば、同一かそれ以上のハードウェア・プロファイルが必要だ。 期待する. .ノードが落ちた場合、レイテンシはビジネス目標値を下回っているか？もしそうでなければ、容量を増やすか、ワークロードを均等にします。こうして、ユーザー・エクスペリエンスとデータの一貫性を同等に守ることができるのです。 サプライズ.

クイックスタートのまとめ

MySQLのレプリケーションの遅れを解消するために、すぐに機能することをまとめる。 下げる. .まず、IOスレッドとSQLスレッドのどちらが遅いかを判断し、Seconds_Behind_Masterとログの位置を観察する。不足している主キーを作成し、大きな更新を分割し、DDLを移動し、レプリカの遅いクエリログを監視する。バッファプールを増やし、並列ワーカーを起動し、innodb_flush_log_at_trx_commit=2 をレプリカに設定して書き込みパスを最小にする。 やわらげる. .それでも不十分な場合は、レプリカをスケールし、読み込み負荷を分散し、フェイルオーバーをきれいに計画する。レプリケーション・アーキテクチャは、適切なレベルの選択を支援します。これにより、可用性を高く、レイテンシを低く、データの一貫性を確実に軌道に乗せることができます。 持続可能.