MySQLデータベースのマスタースレーブレプリケーションの長い遅延に対する解決策

序文

MySQL マスター スレーブ レプリケーションの遅延は、業界では長年の問題となっています。遅延が発生すると、マスターとスレーブの読み取り/書き込み分離の価値が低下し、データのリアルタイム性が求められるビジネスでは MySQL の使用に適さなくなります。

UDB は、UCloud が立ち上げたクラウド データベース サービスです。6 年間オンラインになっており、数万の UDB MySQL インスタンスを運用しています。チームは、高可用性、高パフォーマンス、使いやすい製品機能の提供に加えて、ユーザーが毎日平均 2 ～ 3 件の MySQL インスタンスのマスター/スレーブ レプリケーション遅延の問題を解決できるように支援します。多くの実践から、マスター-スレーブ レプリケーション遅延のさまざまな原因と解決策をまとめ、ここで共有します。

レイテンシー問題の重要性

マスタースレーブレプリケーションメカニズムは、UDB の内部実装で広く使用されています。UDB によって作成されたスレーブデータベースとマスターデータベースは、「マスタースレーブレプリケーション」データレプリケーションを採用しています。また、UDB の主力製品である「UDB MySQL High Availability Instance」も、2 つのデータベースが相互にマスタースレーブとなってデータを複製する「デュアルマスターモード」を採用しており、デュアルマスターモードの中核はマスタースレーブレプリケーションメカニズムです。

マスターとスレーブ間のレプリケーションに遅延が発生すると、マスターとスレーブ間のデータの一貫性が影響を受けます。

高可用性レプリケーション シナリオでは、プライマリ データとスタンバイ データが不整合の場合、高可用性災害復旧の切り替えはデフォルトでは許可されないことを UDB 高可用性災害復旧設計で考慮しました。プライマリ データとスタンバイ データが不整合になると、災害復旧の切り替えが発生し、データが新しいプライマリ データベースに書き込まれるため、ビジネスの観点から予期しない重大な結果が発生します。

レプリケーション遅延の問題は、UDB の高可用性に悪影響を及ぼすだけでなく、読み取り専用スレーブ データベースのシナリオでも、スレーブ データベースにレプリケーション遅延が発生すると、ビジネスに一定の影響を及ぼす可能性があります。たとえば、ビジネスでの読み取りと書き込みに一貫性がなくなり、新しく追加または変更されたデータが見つからないなどの問題が発生します。

これは、マスター スレーブ レプリケーションの遅延問題がデータベース操作において特別な注意を必要とすることを示しています。通常、DBAはライブラリに対して「SHOW SLAVE STATUS」を実行し、

「Seconds_Behind_Master」の値は、現在のデータベースとそのマスター データベース間のデータ レプリケーションの遅延を理解するのに役立ちます。この値は非常に重要であるため、UDB 監視インターフェースで個別に抽出し、運用保守担当者がコンソールで直接確認できるように「スレーブ同期遅延」監視項目を設計しました。

生産環境における遅延問題の分析と解決

最も一般的なマスタースレーブレプリケーション遅延のケースをいくつかのカテゴリにまとめました。以下は、関連するケースの現象の説明、原因分析、および解決策のまとめです。

◆ ケース1: メインデータベースへの頻繁なDMLリクエスト

ビジネスのピーク時には、特にデータベース マスターで多数の書き込み要求操作、つまり挿入、削除、更新などの多数の同時操作がある場合に、一部のユーザーがマスター スレーブ レプリケーションの遅延を経験することがあります。

現象の説明

マスターデータベースの書き込み操作の QPS 値を観察すると、マスターデータベースの書き込み操作の QPS 値が突然増加し、マスタースレーブレプリケーションの遅延が増加していることがわかります。これは、マスターデータベースへの頻繁な DML 要求が原因であると判断できます。

上図の通り、17:58 あたりから QPS が急激に増加し、それに応じてコンソール上の書き込み関連の QPS も増加しました。 QPS が突然増加すると、次の図に示すように、対応するレイテンシも徐々に増加します。

原因分析

分析の結果、メイン データベースに対する書き込み要求操作が多数発生し、短期間で大量のバイナリ ログが生成されたことが原因であると考えられます。これらの操作はスレーブ データベースに同期して実行する必要があり、その結果、マスターとスレーブ間のデータ複製に遅延が発生します。

原因をさらに詳しく分析すると、業務のピーク時にマスター データベースが同時にデータを書き込む一方で、スレーブ データベースの SQL スレッドが binlog ログを単一スレッドで再生するため、リレーログの蓄積と遅延が発生しやすいことがわかります。

解決

MySQL 5.7 以前のバージョンの場合は、シャーディングを使用して水平方向にスケールアウトすることで書き込み要求を分割し、binlog への書き込み要求の並列性を高めることができます。

MySQL 5.7以降の場合、MySQL 5.7では論理クロックに基づく並列レプリケーション(Group Commit)が使用されます。 MySQL 8.0 では、Write Set に基づく並列レプリケーションが使用されます。どちらのソリューションも、binlog 再生のパフォーマンスを向上させ、レイテンシを削減できます。

◆ ケース2: メインデータベースが大規模なトランザクションを実行する

大規模トランザクションとは、非常に長い時間を要するトランザクションの実行を指します。大規模なトランザクションを生成する一般的なステートメントは次のとおりです。

INSERT INTO $tb、SELECT * FROM $tb、LOAD DATA INFILE などの低速なデータ インポート ステートメントが多数使用されています。
大きなテーブルに対して UPDATE および DELETE を実行するには、UPDATE および DELETE ステートメントを使用します。
このトランザクションがスレーブ データベースで再生されると、マスターとスレーブ間のレプリケーション遅延が発生する可能性があります。

現象の説明

SHOW SLAVE STATUS の結果を分析すると、Exec_Master_Log_Pos フィールドは変化しておらず、second_behinds_master は増加し続けており、Slave_SQL_Running_State フィールドの値は「リレー ログからイベントを読み取り中」であることがわかります。同時に、マスター データベースの binlog を分析し、マスター データベースによって現在実行されているトランザクションを確認すると、いくつかの大きなトランザクションが見つかります。これにより、基本的に、マスター スレーブ レプリケーションの遅延は、大きなトランザクションの実行によって引き起こされていることが判断されます。

原因分析

大規模なトランザクションがバイナリログに記録され、スレーブデータベースに同期された後、スレーブデータベースがトランザクションを実行するのに非常に長い時間がかかります。この期間中、マスタースレーブレプリケーションの遅延が発生します。

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

解決

この状況によって発生するマスター スレーブ レプリケーションの遅延を改善する方法は、大きなトランザクション ステートメントをいくつかの小さなトランザクションに分割し、それらを時間内にコミットしてマスター スレーブ レプリケーションの遅延を削減することです。

◆ ケース3: メインデータベースが大きなテーブルに対してDDL文を実行する

DDL はデータ定義言語の略で、テーブルにフィールドやインデックスを追加するなど、テーブル構造を変更するステートメントを指します。マスター データベース内の大きなテーブルに対して DDL ステートメントを実行すると、マスター スレーブ レプリケーションの遅延が発生する可能性があります。

現象の説明

この現象から、スレーブライブラリで実行した SHOW SLAVE STATUS の出力で Exec_Master_Log_Pos が変化しておらず、マスターライブラリで大規模なトランザクションが実行されていない場合は、大規模なテーブルの DDL が実行されている可能性があります。これは、メイン データベースのバイナリ ログを分析し、メイン データベースによって現在実行されているトランザクションを確認することで確認できます。

DDL ステートメントの実行は、さらに次のように分類できます。

1. DDL が開始されず、ブロックされています。この場合、SHOW SLAVE STATUS の結果は、Slave_SQL_Running_State がテーブル メタデータ ロックを待機していることを示し、Exec_Master_Log_Pos は変更されません。

2. DDL が実行され、SQL スレッドのシングルスレッド アプリケーションによって待機時間が増加します。この場合、SHOW SLAVE STATU の結果を観察すると、Slave_SQL_Running_State がテーブルを変更している一方で、Exec_Master_Log_Pos は変更されていないことがわかります。

上記の現象が発生する場合、マスター データベースが大きなテーブルに対して DDL ステートメントを実行し、それをスレーブ データベースに同期してスレーブ データベースで再生し、マスター スレーブ レプリケーションの遅延が発生する可能性が非常に高くなります。

原因分析

DDL によってマスター スレーブ間のレプリケーションが遅延する理由は、大規模なトランザクションの場合と同様です。また、スレーブ ライブラリが DDL の binlog をゆっくりと実行するため、マスター スレーブ間のレプリケーションが遅延する原因にもなります。

解決

この場合、主に SHOW PROCESSLIST またはクエリ information_schema.innodb_trx を使用して、ブロックしている DDL ステートメントを見つけ、関連するクエリを KILL して、スレーブ データベースで DDL が正常に実行されるようにします。

DDL 自体によって発生する遅延は回避が困難です。次の点を考慮することをお勧めします。

ビジネスのピークを避け、オフピーク期間に実行をスケジュールするようにしてください。

sql_log_bin=0 を設定した後、マスター データベースとスレーブ データベースでそれぞれ手動で DDL を実行します (この操作により、一部の DDL 操作でデータの不整合が発生する可能性があるため、厳密にテストしてください)。ユーザーがクラウド データベース UDB を使用している場合は、UCloud UDB 運用保守チームに連絡してサポートを受けることができます。

◆ ケース4: マスターデータベースとスレーブデータベース間の構成の不一致

マスター ライブラリとスレーブ ライブラリが異なるコンピューティング リソースやストレージ リソース、または異なるカーネル チューニング パラメータを使用する場合、マスターとスレーブに不整合が生じる可能性があります。

現象の説明

マスターデータベースとスレーブデータベースのパフォーマンス監視データを詳細に比較します。監視データが大きく異なる場合は、マスターデータベースとスレーブデータベースのさまざまな構成を確認することで明確な判断を下すことができます。

原因分析

さまざまなハードウェアやリソースの構成の違いにより、マスターとスレーブ間のパフォーマンスに違いが生じ、マスター スレーブ間のレプリケーションが遅延する可能性があります。

ハードウェア: たとえば、マスター データベース インスタンス サーバーが SSD ディスクを使用し、スレーブ データベース インスタンス サーバーが通常の SAS ディスクを使用している場合、マスター データベースによって生成された書き込み操作をスレーブ データベースですぐに処理できず、マスターとスレーブ間のレプリケーションが遅延します。
構成: たとえば、一貫性のない RAID カード書き込み戦略、一貫性のない OS カーネル パラメータ設定、一貫性のない MySQL ディスク配置戦略などが原因として考えられます。

解決

DB マシンの構成 (ハードウェアやオプションパラメータを含む) を可能な限り統一することを検討してください。一部の OLAP ビジネスでも、スレーブ データベース インスタンスのハードウェア構成は、マスター データベースの構成よりも若干高くする必要があります。

◆ ケース5: テーブルに主キーまたは適切なインデックスがない

データベース テーブルに主キーまたは適切なインデックスがない場合、マスター スレーブ レプリケーションの binlog_format が 'row' に設定されていると、マスター スレーブ レプリケーションの遅延が発生する可能性があります。

現象の説明

データベースを確認すると、次のことがわかります。

SHOW SLAVE STATUS の出力を観察し、Slave_SQL_Running_State がリレー ログからイベントを読み取っていることを確認します。

SHOW OPEN TABLES WHERE in_use=1 のテーブルは常に存在します。

SHOW SLAVE STATUS の Exec_Master_Log_Pos フィールドが変更されていないことを確認します。

mysqld プロセスの CPU 使用率は 100% に近く (読み取りサービスがない場合)、IO 負荷はそれほど大きくありません。

これらの現象が発生する場合、テーブルに主キーまたは一意のインデックスが不足している可能性が高いと考えられます。

原因分析

たとえば、マスター スレーブ レプリケーションの binlog_format が 'row' に設定されている場合、マスター データベースが 500 万行のテーブル内の 200,000 行のデータを更新するシナリオがあります。行形式では、binlog は 200,000 件の更新操作を記録します。つまり、各操作で 1 つのレコードが更新されます。このステートメントに、完全なテーブル スキャンなどの不適切な実行プランが含まれている場合、各更新ステートメントで完全なテーブル スキャンが必要になります。この時点で、SQL スレッドの再生は非常に遅くなり、マスターとスレーブ間のレプリケーションに重大な遅延が発生します。

解決

この場合、テーブル構造をチェックし、各テーブルに明示的な自動増分主キーがあることを確認し、ユーザーが適切なインデックスを作成できるように支援します。

◆ ケース6: 奴隷自身の圧力が高すぎる

場合によっては、スレーブ データベースのパフォーマンス圧力が非常に高いと、マスター データベースの更新速度に追いつけず、マスター スレーブ レプリケーションの遅延が発生します。

現象の説明

データベース インスタンスを観察すると、CPU 負荷が高すぎて IO 使用率が高すぎるために、SQL スレッド アプリケーションが遅くなっていることがわかる場合があります。このようにして、マスタースレーブレプリケーションの遅延は、スレーブライブラリ自体への過度の負荷によって発生していることが判断できます。

原因分析

一部の UCloud ユーザーは、マスター データベースとスレーブ データベースに読み取り/書き込み分離モードを使用しており、ほとんどの読み取り要求はスレーブ データベースで実行されます。ビジネスで大量の読み取り要求が発生するシナリオでは、スレーブ データベースはマスター データベースよりもはるかに大きなパフォーマンス負荷を生み出します。ユーザーによっては、スレーブ データベースで大量のコンピューティング リソースを消費する OLAP ビジネスを実行している場合もあります。この場合も、スレーブ データベースのパフォーマンスに高い課題が生じ、マスターとスレーブ間のレプリケーションに遅延が発生します。

解決

この場合、読み取り要求を分散し、既存のスレーブ インスタンスへの負荷を軽減するために、さらにスレーブを作成することをお勧めします。 OLAP ビジネスの場合、OLAP ビジネス専用のスレーブ データベースを設定し、このスレーブ データベースに対して適切なマスター スレーブ レプリケーション遅延を許可できます。

要約する

データレプリケーションに MySQL のマスター/スレーブ レプリケーション モードを使用する場合、マスター/スレーブ レプリケーションの遅延は考慮する必要がある重要な要素です。データの一貫性に影響し、データベースの高可用性災害復旧切り替えに影響します。

データベース間のマスター/スレーブ レプリケーションの遅延が発生した場合、当社のチームは過去の経験に基づいて、問題のトラブルシューティングに役立つ次の方法とプロセスをまとめました。

スレーブ ライブラリの現在のステータスを表示するには、SHOW SLAVE STATUS と SHOW PROCESSLIST を使用します。 (ちなみに、ライブラリからバックアップする場合も同様の理由が考えられます);

Exec_Master_Log_Pos が変更されない場合は、大規模なトランザクション、DDL、主キーがないことを考慮し、マスター データベースに対応する binlog と位置を確認します。

Exec_Master_Log_Pos が変化して遅延が徐々に増加する場合は、IO、CPU などのスレーブ マシンの負荷を考慮し、マスターの書き込み操作とスレーブ自体への負荷が大きすぎないかを検討します。

この記事は UCloud Technology からのもので、UCloud の上級専門家である Ding Shun 氏と Zhang Suning 氏によって共有されています。

さて、今回の記事は以上です。この記事の内容が皆さんの勉強や仕事に少しでも参考になれば幸いです。123WORDPRESS.COM をよろしくお願いいたします。