MySQL マスタースレーブ同期遅延の原因と解決策

歴史的な理由により、MySQL レプリケーションは、REDO ログではなく論理バイナリ ログに基づいています。 MySQL が物理ベースのレプリケーションをいつサポートするのかと何度も尋ねられました。実際のところ、それは MySQL の責任者の考え次第です。前回、Lai 教授とブレインストーミングをしていたとき、私は突然、「MySQL には Paxos に基づく redo レプリケーション機能がありますか?」と尋ねました。

ROW 形式に基づくログ レプリケーションではレプリケーションの正確性が完全に保証されるため、物理レプリケーションの本当の利点は正確性にはありません。物理ログはトランザクション実行中に継続的に書き込まれますが、バイナリ ログはトランザクションがコミットされたときにのみ書き込まれるためです。したがって、物理的なログ記録の利点は次のとおりです。

• レプリケーション アーキテクチャでは、大きなトランザクション ログが迅速にコミットされます。
• レプリケーション アーキテクチャでは、マスターとスレーブ間のデータ遅延は小さくなります。

1 時間にわたって大規模なトランザクションが実行されると仮定します。最終コミットでは、最後にコミットされた部分の redo ログのみを書き込む必要があります (redo ログは物理ログとみなすことができます)。この大規模なトランザクションに対して書き込まれる REDO ログの合計量は 1G になる場合がありますが、送信時に、前回の REDO ログが実行後 1 時間以内にスレーブに継続的に同期されているため、データ マスター スレーブ レプリケーションでは、ログの最後の部分のみをリモート スレーブに転送する必要があります。

バイナリ ログの場合、書き込み時間はトランザクションがコミットされたときに発生するため、1G のバイナリ ログが生成されたと仮定すると、トランザクションのコミット時間にはこの 1G のログの書き込み時間も含まれる必要があります。 Oracle には、トランザクションのサイズに関係なく、トランザクションのコミット速度は一定であるという格言があります。これは MySQL データベースでは当てはまりません。つまり、MySQL のコミット速度はトランザクションによって生成されるバイナリ ログのサイズに依存し、トランザクションのコミット速度は一定ではありません。

さらに悪いことに、大規模なトランザクションでは MySQL のマスター/スレーブ レプリケーションが遅延します。また、マスターサーバー上で1時間にわたって大規模なトランザクションが実行されると仮定すると、最後のコミット時刻にスレーブサーバーに転送する必要があります。マスター スレーブ間の遅延は少なくとも 1 時間です。スレーブ サーバーの実行にさらに 1 時間かかる場合、最悪の場合、マスター スレーブ間のレプリケーション遅延は 2 時間になる可能性があります。物理レプリケーションにはそのような制限はありません。理由は前述のとおりです。トランザクション送信プロセス中、ログはすでに送信され、再生されています。

物理的な複製は良いものですが、欠点もあります。私の実際の経験から言うと、

• 物理レプリケーションでは、ホスト上の不良ブロックにより、マスター サーバーとスレーブ サーバーの両方が起動に失敗します。多くの学生がこの問題に遭遇したことがあると思います。
• さらに、ETL を実行するのは困難です。たとえば、物理ログを Hadoop ビッグデータ プラットフォームに同期するにはどうすればよいでしょうか。

簡単に言えば、MySQL データベースでは、大規模なトランザクションをいつでも実行することは許可されません。実行する場合は、大きなトランザクションを小さなサブトランザクションに分割して実行します。これは最も基本的なマントラですが、Oracle とは大きく異なります。つまり、気派と剣派には良いも悪いもありません。両者の違いを理解し、統合することを学ぶことによってのみ、馮青陽のような究極の境地に到達できるのです。

MySQL はマスター スレーブ同期方式を使用して読み取りと書き込みを分離します。これにより、マスター サーバーへの負荷が軽減され、現在業界では非常に一般的になっています。 マスタースレーブ同期は基本的にリアルタイム同期を実現できます。マスタースレーブ同期の回路図は別のウェブサイトから借用しました。

設定が完了し、マスターとスレーブの同期が完了すると、マスター サーバーは更新ステートメントを binlog に書き込み、スレーブ サーバーの IO スレッド (5.6.3 より前は IO スレッドが 1 つしかなく、5.6.3 以降は読み取りスレッドが複数あるため、当然速度が速くなります) がマスター サーバーの binlog を読み取り、スレーブ サーバーのリレー ログに書き込みます。その後、スレーブ サーバーの SQL スレッドがリレー ログ内の SQL ステートメントを 1 つずつ実行して、データを回復します。

リレーは渡すという意味で、リレーレースはリレー競技という意味です。

1. マスタースレーブ同期の遅延の原因

サーバーはクライアントが接続するための N 個のリンクを開くため、大量の同時更新操作が発生しますが、サーバーから binlog を読み取るスレッドは 1 つだけです。特定の SQL がスレーブ サーバーで長時間実行されたり、特定の SQL がテーブルをロックする必要がある場合、マスター サーバー上の大量の SQL が蓄積され、スレーブ サーバーに同期されなくなります。これにより、マスターとスレーブの不整合、つまりマスターとスレーブの遅延が発生します。

2. マスタースレーブ同期遅延の解決策

実際には、すべての SQL ステートメントをスレーブ サーバーで 1 回実行する必要があるため、マスター スレーブ同期の遅延に対する万能の解決策はありません。ただし、マスター サーバーが継続的に更新および書き込みされる場合、遅延が発生すると、遅延が増加する可能性が高くなります。 もちろん、何らかの緩和策を講じることはできます。

• a. マスター サーバーは更新操作を担当するため、スレーブ サーバーよりも高いセキュリティ要件があるため、sync_binlog=1、innodb_flush_log_at_trx_commit = 1 などの設定を変更できます。ただし、スレーブにはそれほど高いデータ セキュリティは必要ありません。sync_binlog を 0 に設定するか、binlog をオフにすることができます。innodb_flushlog、innodb_flush_log_at_trx_commit も 0 に設定して SQL の実行効率を向上させることができ、効率を大幅に向上できます。もう 1 つのオプションは、マスター データベースよりも優れたハードウェアをスレーブとして使用することです。
• b. つまり、クエリを提供する代わりに、スレーブサーバーをバックアップとして使用します。そこでの負荷が軽減され、リレーログでの SQL の実行効率が自然に高くなります。
• c. スレーブ サーバーを追加します。その目的は、読み取り負荷を分散し、サーバーの負荷を軽減することです。

3. マスタースレーブ遅延を決定する方法

MySQL にはスレーブ サーバーのステータス コマンドが用意されており、show slave status で表示できます。たとえば、Seconds_Behind_Master パラメータの値をチェックして、マスター スレーブ間の遅延があるかどうかを判断できます。

値は次のとおりです。

NULL - io_thread または sql_thread のいずれかが失敗したことを示します。つまり、スレッドの実行ステータスは Yes ではなく No です。
0 - この値はゼロであり、最も注目したい値であり、マスタースレーブレプリケーションの状態が正常であることを示します。

他の方法を試したことがないので、今のところコメントしません。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。