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

MySQL のマスター/スレーブの基本原則、主要な形式、およびマスター/スレーブの同期遅延原則 (読み取り/書き込みの分離) により、マスター/スレーブのデータの不整合の問題と解決策が発生します

1. マスター データベースとスレーブ データベースの違い

スレーブ データベース (Slave) はマスター データベースのバックアップであり、マスター データベース (Master) が変更されると、スレーブ データベースを更新する必要があるため、これらのデータベース ソフトウェアの更新サイクルを設計できます。これは情報セキュリティを向上させるための手段です。マスターデータベースサーバーとスレーブデータベースサーバーは地理的に同じ場所にないため、事故が発生した場合でもデータベースを保存できます。

(1) マスターとスレーブの分業

マスターは書き込み操作の負荷を担当します。つまり、すべての書き込み操作はマスター上で実行されます。一方、読み取り操作はスレーブに割り当てられます。これにより、読み取り効率が大幅に向上します。一般的なインターネット アプリケーションでは、いくつかのデータ調査の結果、読み取り/書き込みの比率は約 10:1 であると結論付けられました。これは、多数のデータ操作が読み取り操作に集中していることを意味します。これが、複数のスレーブの理由がある理由です。しかし、なぜ読み書きを分けるのでしょうか? DB に精通した研究開発担当者は皆、書き込み操作には行ロック、テーブル ロック、ブロック ロックなどのロックの問題が関係し、システムの実行効率が相対的に低下することを知っています。書き込み操作を 1 つのノードに集中させ、読み取り操作を他の N ノードで実行することで、読み取り効率が効果的に向上し、システムの高可用性が確保されます。

(2) 基本的なプロセス
1) MySQL のマスターとスレーブの同期とは、マスター (メイン データベース) でデータが変更されると、スレーブ (スレーブ データベース) に同期されることを意味します。 ）リアルタイムです。
2) マスター/スレーブ レプリケーションにより、データベースの負荷容量、フォールト トレランス、高可用性、データ バックアップを水平方向に拡張できます。

3) 関数やストアド プロシージャの削除、更新、挿入、作成はすべてマスター上にあり、マスターが操作を行うと、スレーブはこれらの操作をすぐに受け取り、同期を実行します。

(3) 目的と条件
1)、mysql マスター/スレーブ レプリケーションの目的
●リアルタイムの災害復旧、フェイルオーバーに使用
●読み取りと書き込みの分離、クエリサービスの提供
●ビジネスへの影響を避けるためのバックアップ
2)、マスター/スレーブ展開の必要条件:
●バイナリログを有効にするメインデータベース (log-bin パラメータを設定します)
●マスターとスレーブのサーバー ID が異なります
●スレーブサーバーはマスターライブラリに接続できます

2. マスター/スレーブ同期の粒度、原則、形式:

(1). 3 つの主な実装粒度
詳細なマスター/スレーブ同期には主に 3 つの形式があります。 :statement,row,mixed
1),statement:yes データベース操作の SQL ステートメントを binlog に書き込みます
2),row:各データの変更を binlog に書き込みます。
3)、混合: ステートメントと行の混合。 Mysql は、binlog をいつステートメント形式で書き込むか、いつ binlog を行形式で書き込むかを決定します。

(2)、主な実装原理、具体的な操作、概略図

1)、マスター マシンでの操作:
マスター上のデータが変更されると、イベントの変更が順番にバイナリログに書き込まれます。スレーブがマスターに接続されると、マスター マシンはスレーブのバイナリ ログ ダンプ スレッドを開始します。マスターのバイナリログが変更されると、バイナリログダンプスレッドはスレーブに通知し、対応するバイナリログの内容をスレーブに送信します。
2)、スレーブ マシンで操作します:

マスター/スレーブ同期がオンになると、スレーブ上に 2 つのスレッド (I\O スレッド) が作成されます。このスレッドはマスター マシンに接続されており、マスター マシン上のバイナリ ログ ダンプ スレッドはバイナリ ログの内容を I/O スレッドに送信します。 binlog コンテンツを受信した後、I/O スレッドはそのコンテンツをローカル リレー ログ (SQL スレッド) に書き込みます。このスレッドは、I/O スレッドによって書き込まれた ralay ログを読み取ります。そして中継ログによると。そして、リレーログの内容に従って、スレーブデータベースに対して対応する操作を実行します。

3) MySQL マスター/スレーブ レプリケーションの概略図は次のとおりです。

スレーブ ライブラリは 2 つのスレッド、1 つの I/O を生成します。スレッドと 1 つの SQL スレッド;
i/o スレッドはメイン ライブラリの binlog を要求し、取得した binlog ログをリレー ログ (リレー ログ) ファイルに書き込みます;
メイン ライブラリは、提供するためにログ ダンプ スレッドを生成しますスレーブは、ライブラリ I/O スレッドが binlog を送信します。
SQL スレッドは、リレー ログ ファイル内のログを読み取り、それを特定の操作に解析して、一貫したマスター/スレーブ操作と一貫した最終データを実現します。

(2)、マスター/スレーブ形式

mysql マスター/スレーブ レプリケーションは柔軟です

● 1 つのマスターと 1 つのスレーブ

● マスター/マスター レプリケーション
● 1 つのマスターおよび複数のスレーブ ---拡張システムの読み取りパフォーマンス。読み取りはスレーブ ライブラリから読み取られるためです。
● 複数のマスターと 1 つのスレーブ --- 5.7 以降でサポート
# ● カスケード レプリケーション ---

3. マスター/スレーブ同期の遅延などの問題と原因と解決策:

(1). mysql データベースの遅延問題スレーブ同期

1) 関連パラメータ:

最初にサーバー上で show smile satus を実行すると、多くの同期パラメータが表示されます:

Master_Log_File: SLAVE の I/O スレッドによって現在読み取られているマスター サーバーのバイナリ ログ ファイルの名前
Read_Master_Log_Pos: 現在のマスター サーバーのバイナリ ログ内で、SLAVE の I/O スレッドが読み取った位置
Relay_log_file: SQL スレッドの名前は現在、リレー ログ ファイルの名前を読み取り、実行しています。
Relay_log_pos: 現在のリレー ログ内で、SQL スレッドの場所が読み取り、実行されています。
Relay_master_log_file:最新のイベントを含むマスターバイナリログファイルの名前
slave_io_running：。サーバーSQLスレッドとスレーブサーバーI/Oスレッドの間の時間ギャップは秒単位で。
スレーブ同期遅延が発生します● 表示スレーブ ステータス表示パラメータ Seconds_Behind_Master は 0 ではなく、この値は非常に大きい可能性があります
● 表示スレーブ ステータス表示パラメータ Relay_Master_Log_File と Master_Log_File は、bin-log 番号が大きく異なることを示しますこれは、bin-log がスレーブ データベース上で時間的に同期されていないことを示しています。そのため、最近実行された bin-log と現在の IO スレッドによって読み取られた bin-log は大きく異なります。
# mysql スレーブ データベースのデータ ディレクトリにあるリレーログ ログ、ログは同期完了後にシステムによって自動的に削除されます。大量のログがあり、マスターとスレーブの同期遅延が非常に深刻であることを示しています

(2)、MySql データベースのスレーブ同期遅延の問題

1)、MySQL データベースのマスターとスレーブの同期遅延の原理 mysql マスターとスレーブ同期原理: メイン ライブラリは書き込み操作のために binlog を連続的に書き込み、スレーブ ライブラリからの単一スレッドがメイン ライブラリに移動して連続的に読み取りおよび書き込みを行う「binlog 操作」、ライブラリから binlog を取得してローカルで実行します (ランダムに書き込まれます)。マスターとスレーブのデータが論理的に一貫していることを確認します。 mysql のマスター/スレーブ レプリケーションはシングル スレッド操作です。メイン ライブラリはすべての DDL および DML のバイナリ ログを生成します。バイナリは順次書き込まれるため、非常に効率的です。スレーブの Slave_IO_Running スレッドはログを取得するためにメイン ライブラリに移動します次のステップ、質問 ここで、スレーブの Slave_SQL_Running スレッドは、スレーブ上のメイン ライブラリの DDL および DML 操作を実装します。 DML と DDL の IO 操作はランダムであり、シーケンシャルではなく、コストが大幅に高くなります。スレーブ上の他のクエリもロック競合を引き起こす可能性があります。Slave_SQL_Running もシングルスレッドであるため、DDL カード マスターの実行には 10 分かかります。その後、後続のすべての DDL は、この DDL が実行されるまで待機してから続行するため、遅延が発生します。 「メイン ライブラリの同じ DDL も 10 分間実行する必要があります。なぜスレーブが遅れるのですか?」と尋ねる友人もいます。その答えは、マスターは同時に実行できますが、Slave_SQL_Running スレッドは実行できないからです。

2) MySQL データベースのマスター/スレーブ同期の遅延はどのようにして発生しますか?メイン ライブラリの TPS 同時実行性が高く、生成される DDL の数がスレーブの 1 つの SQL スレッドが耐えられる数を超えると、遅延が発生します。もちろん、スレーブの大規模なクエリ ステートメントによって引き起こされるロック待機も発生する可能性があります。主な理由: ビジネスでデータベースの読み取りと書き込みに過大な負荷がかかっている、CPU コンピューティングの負荷が高い、ネットワーク カードの負荷が高い、ハードディスクのランダム IO が高すぎる 二次的な理由: 読み取りと書き込みによるパフォーマンスへの影響バイナリログの書き込み、およびネットワーク送信の遅延。

(3)、データベースからの MySql データベース同期の遅延ソリューション

1)、アーキテクチャ面

1. ビジネス永続層の実装にはサブデータベース アーキテクチャが採用されており、mysql サービスを並行して拡張して圧力を分散できます。

2. 圧力を分散するために、単一のライブラリ、1 つのマスターと複数のスレーブ、マスターが書き込み、スレーブが読み取りで読み取りと書き込みを分離します。このようにして、スレーブ ライブラリの圧力はメイン ライブラリの圧力よりも高く、メイン ライブラリを保護します。

3. サービス インフラストラクチャは、ビジネスと mysql の間に memcache または redis キャッシュ層を追加します。 mysql の読み取り圧力を軽減します。

4. 圧力を分散するために、さまざまなビジネスの MySQL が物理的に異なるマシンに配置されます。

5. メインデータベースよりも優れたハードウェア機器をスレーブとして使用する まとめ MySQL の方が負担が少なく、遅延も自然に小さくなります。

2) ハードウェアの側面

1. 優れたサーバーを使用します。たとえば、4u は 2u よりも大幅に優れたパフォーマンスを発揮し、2u は 1u よりも大幅に優れたパフォーマンスを発揮します。

2. ランダム書き込みパフォーマンスを向上させるには、ストレージに SSD、ディスク アレイ、または SAN を使用します。

3. マスターとスレーブは同じスイッチの下にあり、10G 環境にある必要があります。

まとめ、ハードウェアが強ければ当然遅延は小さくなります。つまり、レイテンシーを最小限に抑える解決策はお金と時間です。

3)、mysql マスター/スレーブ同期アクセラレーション

1、sync_binlog はスレーブ側で 0 に設定されます

2、-logs-slave -updates smile サーバーがマスターサーバーから受信した更新は、バイナリログに記録されません。

3. スレーブ側でバイナリログを直接無効にする

4. スレーブ側で、使用されるストレージ エンジンが innodb の場合、innodb_flush_log_at_trx_commit =2

4 )、ファイルシステムから独自の属性角度の最適化

マスター側は、Linux および Unix ファイル システムのファイルの etime 属性を変更します。OS は、ファイルが読み取られるたびに、読み取り操作が発生した時刻をディスクに書き戻すため、これは、次のようなデータベース ファイルでは不可能です。頻繁な読み取り操作が必要な場合は、ディスク システムの負荷が増大するだけであり、I/O パフォーマンスに影響を与えます。ファイル システムのマウント属性を設定することで、atime 情報を書き込むようにオペレーティング システムを構成できます。Linux での操作は、/etc/fstab を開き、noatime パラメータ /dev/sdb1 /data reiserfs noatime 1 2 を追加して、再マウントします。ファイル システム #mount -oremount /data

5)、同期パラメータの調整 sync_binlog=1、innodb_flush_log_at_trx_commit = 1 などのより高いデータ セキュリティ を備えたメイン ライブラリが作成されます。スレーブにはそのような高度なデータ セキュリティは必要ありません。sync_binlog を 0 に設定するか、binlog をオフにすることができます。SQL の実行効率を向上させるために、Innodb_flushlog を 0 に設定することもできます。

1, sync_binlog=1 oMySQL は提供しますsync_binlog パラメータを指定すると、制御データベースの binlog がディスクにフラッシュされます。デフォルトでは、sync_binlog=0 です。これは、MySQL が binlog のリフレッシュを制御せず、ファイル システム自体がキャッシュのリフレッシュを制御することを意味します。この時のパフォーマンスは最高ですが、リスクも最高です。システムがクラッシュすると、binlog_cache 内のすべての binlog 情報が失われます。

sync_binlog>0 の場合、すべての sync_binlog トランザクションが送信され、MySQL がファイル システムのリフレッシュ操作を呼び出してキャッシュをフラッシュすることを意味します。最も安全な設定は sync_binlog=1 で、トランザクションが送信されるたびに MySQL が binlog をフラッシュします。これは最も安全な設定ですが、パフォーマンスの損失が最も大きくなります。この場合、システムが 1 つのトランザクションのデータを失う可能性があるのは、データベースが配置されているホストのオペレーティング システムが破損した場合、または突然電源が失われた場合のみです。ただし、binlog はシーケンシャル IO ですが、sync_binlog=1 を設定すると、複数のトランザクションが同時に送信され、MySQL と IO のパフォーマンスにも大きな影響を与えます。グループ コミット パッチによって軽減できますが、更新頻度が高すぎると IO に大きな影響が生じます。

同時トランザクションが多いシステムの場合、「sync_binlog」を 0 に設定したシステムと 1 に設定したシステムとの間の書き込みパフォーマンスの差は 5 倍以上になる可能性があります。したがって、多くの MySQL DBA によって設定される sync_binlog は、最も安全な 1 ではなく、2 または 0 になります。これにより、より高い同時実行性とパフォーマンスを実現するために、ある程度の一貫性が犠牲になります。デフォルトでは、バイナリログは書き込まれるたびにハード ディスクと同期されません。したがって、オペレーティング システムまたはマシン (MySQL サーバーだけでなく) がクラッシュすると、バイナリログの最後のステートメントが失われる可能性があります。これを防ぐには、sync_binlog グローバル変数 (1 が最も安全な値ですが、最も遅い値でもあります) を使用して、N 回のバイナリ書き込みごとにバイナリログをハードディスクと同期します。 sync_binlog が 1 に設定されている場合でも、クラッシュが発生すると、テーブルの内容と binlog の内容の間に不一致が生じる可能性があります。

2. innodb_flush_log_at_trx_commit (これは非常に便利です) Innodb が MyISAM より 100 倍遅いと文句を言っていませんか?この値を調整するのを忘れている可能性があります。デフォルト値の 1 は、トランザクションのコミットまたはトランザクション外の命令ごとにログをハードディスクに書き込む (フラッシュ) 必要があり、これには非常に時間がかかることを意味します。特にバッテリーバックアップされたキャッシュを使用している場合。多くのアプリケーション、特に MyISAM テーブルから転送されたアプリケーションでは、2 に設定しても問題ありません。これは、ハードディスクに書き込むのではなく、システム キャッシュに書き込むことを意味します。ログは依然として毎秒ディスクにフラッシュされるため、通常は 1 ～ 2 秒以上の更新が失われることはありません。 0 に設定すると高速になりますが、セキュリティが低く、MySQL がハングアップした場合でもトランザクション データが失われる可能性があります。値 2 は、オペレーティング システム全体がハングした場合にのみデータ損失を引き起こします。

3. ls(1) コマンドを使用すると、ファイルの atime、ctime、mtime を一覧表示できます。

atime ファイルのアクセス時間 ctime は、ファイルが読み取られるか実行されるときに変化します。 ファイルの作成時間は、ファイルの書き込み時、所有者、権限、またはリンク設定の変更時に i ノードの内容が変化するにつれて変化します。 mtimeファイルの書き込み時にファイルの内容が変更されると、ファイルの変更時刻も変化します。 ls -lc filename はファイルの ctimels をリストします。 -lu filename はファイルの atimels をリストします。 -l filename はファイルの mtimestat をリストします。 filename は atime、mtime、ctimeatime をリストします。 ext3 ファイルシステムを使用する場合、マウント中に noatime パラメータが使用されると、atime 情報は更新されません。これら 3 つのタイムスタンプは inode に配置されます。mtime と atime が変更されると、必ず inode が変更されます。inode が変更されるため、ctime も変更されます。noatime がマウント オプションで使用される理由は、ファイルがシステムはそれを望んでいません。読み取りパフォーマンスを向上させるには修正が多すぎます

(4)、MySql データベースの同期その他の問題と解決策

1) mysql マスター/スレーブ レプリケーションの問題: ● メイン データベースがダウンすると、データが失われる可能性がある ● スレーブ データベースには SQL スレッドが 1 つしかなく、メイン データベースは非常に負荷が高い書き込みプレッシャーにより、レプリケーションが遅延する可能性が高くなります 2). 解決策: ● 半同期レプリケーション --- データ損失の問題を解決します ● 並列レプリケーション --- データベースからのレプリケーション遅延の問題を解決します

3)、準同期レプリケーション mysql semi-sync (準同期レプリケーション) 準同期レプリケーション: ● 5.5 は mysql に統合され、プラグインの形式で存在し、別途インストールする必要があります。 ● トランザクションの送信後、ビンログが少なくとも 1 であることを確認します。 スレーブ ライブラリに転送します。 ● スレーブ ライブラリがこのトランザクションのバイナリ ログの適用を完了するという保証はありません。 ● パフォーマンスはある程度低下します。応答時間が長くなります。 ● ネットワーク異常またはスレーブ ライブラリのダウンタイムにより、メイン ライブラリはタイムアウトまたはスレーブ ライブラリの回復まで停止します。 4) 、マスター/スレーブ レプリケーション -- 非同期レプリケーション原理、半同期レプリケーション、および並列レプリケーション原理の比較

a. 非同期レプリケーションの原則:

b. 半同期レプリケーションの原則:

トランザクションがバイナリログを書き込んだ後メイン ライブラリでは、クライアントにメッセージを返す前にライブラリから受け入れられたメッセージを返す必要があります。5.5 は mysql に統合され、プラグインの形式で存在し、送信後のトランザクションを確実にするために別途インストールする必要があります。 、バイナリログは少なくとも 1 つのスレーブ ライブラリに送信されます。このトランザクションを完了するためのスレーブ ライブラリ アプリケーションのバイナリ パフォーマンスが、ネットワーク異常またはスレーブ ライブラリのダウンタイムによりある程度低下するという保証はありません。タイムアウトになるか、スレーブ ライブラリが回復するまで停止します

c. 並列レプリケーション mysql 並列レプリケーション ● Community Edition 5.6 の新機能 ● 並列とは、マルチスレッドを指し、ライブラリから binlog を適用します ● binlogはライブラリ レベルで並列に適用され、同じライブラリ内のデータ変更はシリアルのままです (バージョン 5.7 の並列レプリケーションはトランザクション グループに基づいています) set set global smile_parallel_workers=10; SQL スレッドの数を 10

# に設定します##原則: マルチスレッドはライブラリからバイナリを適用します。コミュニティ 5.6 では、新しいライブラリ レベルの並列アプリケーション バイナリが追加されます。同じライブラリ内のデータ変更は引き続きシリアルです。並列レプリケーションの 5.7 バージョンはトランザクション グループに基づいています

MySQL 関連の技術記事の詳細については、

MySQL チュートリアル 列にアクセスして学習してください。

以上がMySQL のマスターとスレーブの同期遅延の理由と解決策の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。