MySQLマスタースレーブ遅延現象と原理の詳細な分析

1. 現象

早朝、オンラインテーブルにインデックスが追加されました。テーブル内のデータ量が大きすぎたため (1 億件以上のデータ、50G 以上のデータ)、マスターとスレーブの間で数時間の遅延が発生しました。スレーブデータベースに依存するシステムはデータを照会できず、最終的には業務に影響を及ぼしました。

それでは、マスタースレーブ遅延の原理を整理してみましょう。

2. 原則

MySQL の公式ドキュメント「MySQL Replication Implementation Details」の説明によると、MySQL マスタースレーブレプリケーションは、 master用のスレッド 1 つ ( Binlog dump thread ) とslaveスレッド 2 つ ( I/O threadとSQL thread ) の 3 つのスレッドに依存します。マスタースレーブレプリケーションのプロセスは次のとおりです。

マスターサーバーとスレーブサーバーが接続されたら、 Binlog dump threadを作成して、 bin logデータを送信します。

1 つのBinlog dump thread 1 つのスレーブサーバーに対応します。
Binlog dump thread bin logからデータを取得するときにデータをロックし、データ取得後すぐにロックを解除します。

スレーブサーバーは START_SLAVE コマンドを受信すると、 I/O threadとSQL threadを作成します。

I/O threadマスターからプル方式でイベントを読み取り、スレーブサーバーのrelay logに保存します。
SQL thread relay logからイベントを読み取り、実行します。
slave独自のペースでデータを読み取り、更新することができ、またレプリケーションプロセス (開始と停止) を自由に操作することもできます。

注意: START_SLAVEコマンドがスレッドを正常に開始した後、後続のI/O threadまたはSQL threadが何らかの理由で停止しても、警告は表示されず、ビジネスパーティはそれに気付きません。スレーブのエラーログを表示するか、SHOW SLAVE STATUS コマンドを使用してスレーブ上のスレッドステータスを表示できます。

SHOW PROCESSLIST を通じてスレッドのステータスを表示できます。

Binlog ダンプスレッド:

mysql> SHOW PROCESSLIST\G
************************** 1. 行 ****************************
  識別子: 2
 ユーザー: root
 ホスト: localhost:32931
  デシベル: NULL
コマンド: Binlog ダンプ
 時間: 94
 状態: すべてのバイナリログをスレーブに送信しました。バイナリログがスレーブに送信されるまで待機しています。
   更新される
 情報: NULL

I/O スレッドと SQL スレッド:

mysql> SHOW PROCESSLIST\G
************************** 1. 行 ****************************
  識別子: 10
 ユーザー: システムユーザー
 ホスト：
  デシベル: NULL
コマンド: 接続
 時間: 11
 状態: マスターがイベントを送信するのを待機中
 情報: NULL
 ************************** 2. 行 ****************************
  識別子: 11
 ユーザー: システムユーザー
 ホスト：
  デシベル: NULL
コマンド: 接続
 時間: 11
 状態: すべてのリレーログを読み取りました。スレーブI/Oを待機しています。
   スレッドを更新
 情報: NULL

分析

上記の原則によれば、 slave単一のスレッド（ I/O thread ）でデータを読み取り、単一のスレッド（ SQL thread ）でデータを更新し、 master複数のスレッドでデータを書き込むため、 master書き込み頻度がslave読み取りおよび更新頻度よりも高い限り、次のようなマスタースレーブ間遅延が発生する可能性があります。

masterの書き込みtps slave更新速度よりも高くなります。
slave 、ロックの保持などの特定のステートメントの実行に長い時間がかかります。
master特定のDDLステートメントを実行すると、実行時間が長くなり、 slaveも同じ時間でそれを実行します。

ここでインデックスが作成されます。DBA に相談したところ、生成されたbin logファイルが 100 GB を超えていることがわかりました。データ量が大きすぎるため、スレーブライブラリのI/O thread DDL操作によって生成されたbin logイベントを読み取り続け、通常のビジネスDMLイベントの更新に影響し、マスターとスレーブの同期が遅れることになります。

4. 解決策

マスタースレーブ遅延の原因の観点から、解決は次の方向から始めることができます。