MySQL マスタースレーブレプリケーションの遅延の原因と解決策

出典: 公開アカウント「Oracle's Shadow Gallery」

非同期または半同期のレプリケーション構造では、スレーブで遅延が発生するのは正常です。
遅延は正常ですが、対応が必要かどうかは通常、企業によって評価されます。
たとえば、スレーブ データベースに高い一貫性が要求され、遅延が一定値未満であることが求められる読み取り業務がある場合、注意が必要です。

レプリケーション ロジックの簡単な概要:

1. マスター データベースは、データベース インスタンスへの変更を binlog に記録します。
2. Master has sent all binlog to slave; waiting for more updatesバイナリ ログの変更をリアルタイムで監視し、これらの新しいイベントをスレーブ データベースにプッシュするためbinlog dumpスレッドがあります (マスターはすべてのバイナリ ログをスレーブに送信し、さらに更新を待機しています)
3. ライブラリのIO Threadからこれらのイベントを受信し、リレーログに記録します。
4. スレーブSQL Threadリレーログのイベントを読み取り、これらのイベントをスレーブ インスタンスに適用 (または再生) します。

上記は、デフォルトの非同期レプリケーション ロジックです。半同期レプリケーションは少し異なるため、ここでは説明しません。

さらに、スレーブ ライブラリに遅延があるかどうかを判断するのは非常に簡単です。
スレーブデータベースでは、 SHOW SLAVE STATUSを使用します。
Seconds_Behind_Master値を確認してください。

遅延の原因と解決策

〇メインデータベースへの頻繁なDMLリクエスト（tpsが大きい）

つまり、メイン データベースには多数の書き込み要求があり、多数の同時挿入、削除、更新操作が実行され、短時間で大量の binlog が生成されます。

【原因分析】

マスター データベースはデータを同時に書き込みますが、スレーブ データベースのSQL Thread単一のスレッドでログを適用するため、リレーログの蓄積と遅延が簡単に発生する可能性があります。

【解決】

シャーディングを実行し、スケールアウトして書き込み要求を分散します。または、MySQL 5.7 以降にアップグレードし、論理クロックに基づいて並列レプリケーションを有効にすることを検討してください。

〇メインデータベースは大規模なトランザクションを実行する

たとえば、大量のデータのインポート、 INSERT INTO $tb1 SELECT * FROM $tb2、LOAD DATA INFILEなど。たとえば、テーブル全体のUPDATE 、 DELETEなど。
Exec_Master_Log_Pos変更されていません。Slave_SQL_Running_State Slave_SQL_Running_State Reading event from the relay log
メイン データベースの binlog を分析し、メイン データベースによって現在実行されているトランザクションを確認します。

【原因分析】

マスター データベースが大きなテーブルを更新するのにかかる時間が 200 秒で、マスター データベースとスレーブ データベースの構成が似ている場合、スレーブ データベースも大きなテーブルを更新するのにかかる時間がほぼ同じになります。この時点で、スレーブ データベースの遅延が蓄積し始め、後続のイベントを更新できなくなります。

【解決】

大規模なトランザクションを分割し、時間内に送信します。

〇メインデータベースは大きなテーブルに対してDDL文を実行する

この現象は、メイン データベースで大規模なトランザクションを実行する場合と似ています。
DDL の実行により Exec_Master_Log_Pos が移動していないことを確認します。
メイン データベースの binlog を分析し、メイン データベースによって現在実行されているトランザクションを確認します。

【原因分析】

1. DDL は開始されておらず、ブロックされています。SHOW SHOW SLAVE STATUS 、 Slave_SQL_Running_Stateがwaiting for table metadata lock 、 Exec_Master_Log_Posが変更されていないことを示しています。
2. DDL が実行されており、 SQL Threadシングルスレッド アプリケーションによってレイテンシが増加します。 Slave_SQL_Running_Stateはaltering tableですが、 Exec_Master_Log_Posは変更されません。

【解決】

processlistまたはinformation_schema.innodb_trxを使用して、DDL ステートメントをブロックするクエリを見つけ、そのクエリを強制終了し、スレーブ データベースで DDL が正常に実行されるようにします。
DDL 自体によって発生する遅延は回避が困難です。次の点を考慮することをお勧めします。
① オフピーク時に実行する ② set sql_log_bin=0後、マスターデータベースとスレーブデータベースでそれぞれ手動で DDL を実行する (この操作により、一部の DDL 操作でデータの不整合が発生する可能性があるため、厳密にテストしてください)

〇マスターライブラリとスレーブライブラリの構成が一致していません:

【原因分析】

ハードウェア: マスターインスタンスサーバーは SSD を使用し、スレーブインスタンスサーバーは通常の SAS ディスクを使用しており、CPU のメイン周波数は一貫していません。構成: RAID カードの書き込み戦略が一貫していない、OS カーネルパラメータ設定が一貫していない、MySQL ディスクの配置戦略が一貫していないなど。

【解決】

DBマシンの構成（ハードウェアやオプションパラメータを含む）を統一する
一部の OLAP ビジネスでも、スレーブ インスタンスのハードウェア構成はマスター インスタンスよりも高くなります。

〇テーブルに主キーまたは一意のインデックスがない

binlog_format=rowの場合、テーブルに主キーまたは一意のインデックスがないと、 UPDATEおよびDELETE中にスレーブ データベースの待ち時間が大幅に増加する可能性があります。
現時点では、 Slave_SQL_Running_State Reading event from the relay log 。
また、 SHOW OPEN TABLES WHERE in_use=1テーブルは常に存在します。
Exec_Master_Log_Pos変更されません。
mysqld プロセスの CPU 使用率はほぼ 100% (読み取りトラフィックがない場合) であり、IO 負荷はそれほど大きくありません。

【原因分析】

マスター データベースが 500 万のテーブルで 200,000 行のデータを更新するという極端なケースを想定してみましょう。更新ステートメントにはテーブル全体のスキャンが必要です。行形式では、200,000 の更新操作がバイナリ ログに記録されます。この場合、SQL スレッドの再生は非常に遅くなり、更新ごとにテーブル全体のスキャンが必要になる場合があります。

【解決】

テーブル構造をチェックして、各テーブルに明示的な自動増分主キーがあることを確認し、適切なインデックスを作成します。

〇図書館自体からの圧力が大きすぎる

【原因分析】

スレーブ データベースが大量の選択要求を実行するか、ビジネスの選択要求のほとんどがスレーブ データベース インスタンスにルーティングされるか、大量の OLAP ビジネスが存在するか、スレーブ データベースがバックアップされているかなどです。
この時点で、CPU 負荷が高すぎる、IO 使用率が高すぎる、SQL スレッド アプリケーションが遅すぎる可能性があります。

【解決】

読み取り要求を分散し、既存のスレーブ インスタンスへの負荷を軽減するために、スレーブをさらに作成します。

０MyISAMストレージエンジン

現時点では、スレーブライブラリSlave_SQL_Running_StateはWaiting for table level lock

【原因分析】

MyISAM はテーブルレベルのロックのみをサポートしており、読み取りと書き込みを同時に実行することはできません。
@@concurrent_insertの対応する値が設定されている場合、マスター データベースは選択中に挿入を同時に実行できますが、スレーブ データベースのSQL Threadの再生中に同時に実行することはできません。興味がある場合は、MyISAM の実装を参照してください。

【解決】

もちろん、私はそれを許すことを選択します。MyISAM を選択したので、精神的に準備する必要があります。 (レプリケーション構造で MyISAM を使用することが推奨されないシナリオもいくつかあります。)
InnoDB に変更します。

要約:

スレーブ ライブラリの現在のステータスを表示するにはSHOW SLAVE STATUSとSHOW PROCESSLISTを使用します。 (データベースからバックアップする場合もこれを回避できます。)
Exec_Master_Log_Pos変更されない場合は、大規模なトランザクション、DDL、主キーがないことを考慮し、マスター データベースに対応する binlog と位置を確認します。
Exec_Master_Log_Posが変化して遅延が徐々に増加する場合は、io、cpu などのスレーブマシンの負荷を考慮し、マスターの書き込み操作とスレーブ自身の負荷が高すぎないかを検討します。

上記の理由のいずれにも当てはまらない場合は、DBA の専門家に支援を求めてください。

もちろん、 Seconds_Behind_Masterは必ずしも正確ではありません。いくつかのシナリオでは、 Seconds_Behind_Masterが 0 であっても、マスターとスレーブのデータに矛盾が生じます。
それはまた別の機会にブログで書きます。

全文は以上です。

上記は、MySQL マスタースレーブレプリケーション遅延の原因と解決策の詳細な内容です。MySQL マスタースレーブレプリケーション遅延の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。