MYSQL の binlog 最適化に関する考察の要約

質問

質問 1: トランザクションをコミットするときに REDO ログをフラッシュすることによって発生するパフォーマンスの低下を解決するにはどうすればよいですか?

WAL は、トランザクションの永続性 (D) を実現するための一般的なテクノロジです。基本原理は、トランザクションの変更を REDO ログに記録することです。 REDO ログは追加順に順次書き込まれます。トランザクションがコミットされたら、トランザクションの REDO ログがディスクに書き込まれるようにするだけで済みます。ランダムなページ書き込みをシーケンシャルな REDO ログ書き込みに置き換えることで、データベース システムのパフォーマンスが向上します。ただし、このソリューションでは、各トランザクションによって生成された REDO ログをコミット時に 1 回ディスクにフラッシュする必要があり、非効率的です。

質問2: バイナリログとエンジンレベルのトランザクションをコミットする順序

単一トランザクションの場合、ログの書き込み順序は最初に redo ログ、次に binlog です。この順序が維持されている限り、正確性は維持されます。しかし、同時実行性の高いデータベース システムの場合、常に多数のトランザクションが同時に実行される可能性があります。また、サーバー層のバイナリログとエンジン層のトランザクション送信の順序的な一貫性を維持するために、特定の手段を使用する必要があります。

この順次的な一貫性を維持することは、実際にはバックアップ ツール Xtrabackup の正確性を確保するためです。

binlog がコーディネーターとして機能する場合、そこに記録されたトランザクションの順序がストレージ エンジン層で記録された順序と異なると、バックアップ ツール (Innodb Hot Backup) によって取得されたバックアップ セットの場所にギャップが生じる可能性があります。バックアップ ツールは REDO ログをコピーするため、最後にコミットされたトランザクションに対応するバイナリ ログの位置が REDO ヘッダーに記録されます。バックアップ セットが作成されると、この位置に基づいてプライマリ データベースからバイナリ ログが引き続きダンプされます。

binlog ファイルに fsync された 3 つのトランザクション T1、T2、T3 があるとします。ファイル内の 3 つのトランザクションの位置は、それぞれ 100、200、300 です。ただし、エンジン レイヤーでは、T1 と T3 のみがコミットを完了し、REDO ログに記録されています。最後にコミットされたトランザクション T3 の位置は 300 です。この時点で、バックアップ ツールによって取得されたデータはこの状態です。バックアップ セットが開始されると、クラッシュ回復プロセスが実行され、準備トランザクションがロールバックされます (バックアップ セットは、前のセクションで設定された空の xid に対応する binlog ファイルをバックアップしません)。ポイント 300 以降、プライマリ データベースからの binlog 同期が継続して適用され、スタンバイ データベースで T2 が失われます。

したがって、サーバー層でのバイナリログの書き込み順序がストレージ エンジン層でのトランザクションの送信順序と一致するようにメカニズムを設計する必要があります。

問題3: redoとbinlogを同時に書き込むことによるパフォーマンスの低下

質問 1 では、各トランザクションの送信によってパフォーマンスの問題が発生し、binlog の導入後にこの問題がさらに深刻化すると述べられています。トランザクションの送信ごとにファイル IO が 1 回増加し、ディスクのフラッシュが必要になります。システムの同時実行性が高い場合、これらの IO がボトルネックとなり、全体的なパフォーマンスが低下します。

解決

質問1: REDOロググループ送信技術

REDO グループ コミット テクノロジの考え方は非常にシンプルです。複数のトランザクション REDO ログのフラッシュ アクションをマージすることで、フラッシュ回数を減らすことができます。 Innodb ログ システムでは、各 REDO ログに LSN (ログ シーケンス番号) があります。トランザクションがログを REDO ログ バッファにコピーすると、現在の最大 LSN が取得され、LSN は単調に増加するため、異なるトランザクションの LSN が重複することがなくなります。次に、3 つのトランザクション Trx1、Trx2、および Trx3 のログの最大 LSN がそれぞれ LSN1、LSN2、および LSN3 (LSN1 < LSN2 < LSN3) であり、それらが同時にコミットされるとします。trx3 が最初にコミットされると、ディスクを LSN3 にフラッシュするように要求され、Trx1 と Trx2 の redo ログもフラッシュされます。Trx1 と Trx2 は、自身の LSN が現在ディスクにフラッシュされている最大 LSN よりも小さいと判断するため、ディスクを再度フラッシュする必要はありません。

問題2: 内部XAトランザクション

binlog を有効にすると、上位層とストレージ エンジン層を調整するために内部 XA トランザクションが導入されます。具体的には、トランザクションがコミットされるときに 2 つのステージが導入されます。

準備: データ ページと UNDO ページへの更新がディスクにフラッシュされたことを確認するために、REDO ログをディスクにフラッシュし、トランザクション ステータスを PREPARE に設定します。

コミット: 1) binlog を書き込み、ディスクにフラッシュします。2) エンジン レイヤー トランザクション コミット インターフェイスを呼び出します。トランザクション ステータスを COMMIT に設定します。

このような 2 フェーズ コミットは、主にデータベースがクラッシュしたときに正確性を保証するために行われます。バイナリログがディスクに書き込まれると、下流のノードによって消費される可能性があるためです。このようなトランザクションは、再起動後にロールバックするのではなく、コミットする必要があります。 binlog でディスクに書き込まれていないトランザクションは、クラッシュ回復中に直接ロールバックされます。

具体的には、障害回復中に、最後の binlog ファイルをスキャンし (フラッシュ フェーズで、binlog サイズがしきい値を超える場合は、binlog ファイルをローテーションして、ファイルに記録された最後のトランザクションがコミットされるようにします)、そこから xid を抽出します。チェックポイント後にREDOログをやり直し、トランザクションのUNDOセグメント情報を読み取り、準備段階でトランザクションリストを収集し、トランザクションのxidをbinlogに記録されたxidと比較し、存在する場合はコミットし、そうでない場合はロールバックします。

MySQL 5.6 より前では、データベース binlog の書き込み順序が InnoDB レイヤーのトランザクション コミット順序と一致するようにするために、MySQL データベースは内部で prepare_commit_mutex ロックを使用します。

具体的には、2 フェーズ コミット エンジン レイヤーの準備中にロックが追加され、エンジン レイヤーがコミットした後にロックが解除されます。

innobase_xa_prepare()
write() および fsync() バイナリログ
innobase_commit()

これにより、binlog と innodb のトランザクション順序の一貫性が確保されますが、このロックによりすべてのトランザクションがシリアルに実行され、各送信で少なくとも複数の fsync が呼び出されるため、非常に非効率的になります。これも次に調査して解決する必要がある問題です。

質問4

REDO ログ最適化テクノロジを参照し、グループ コミット テクノロジを導入して、binlog 書き込みパフォーマンスを最適化します。

最適化されていない場合のトランザクション送信プロセスを検討してください。

準備: このフェーズでは、ストレージ エンジン層 (innodb) の redo ログがフラッシュされ、トランザクション ステータスが PREPARED に設定されます (undo ページでトランザクション ステータスを更新します)。このフェーズでは、binlog は関係ありません。
コミット: バイナリログを書き込み、ディスクにフラッシュします。同時に、エンジン レイヤーはロックを解除し、ロールバック セグメントを解放し、トランザクション ステータスを COMMITTED に設定し、その他のいわゆるグループ コミット テクノロジを実行します。本質的には、時間のかかるコミット ステップをより細かいステップに分割します。具体的には、次のようになります。

ステップ 2 のコミットを 3 つの段階に分けます。

フラッシュ: バイナリログを書き込むが、同期はしない
同期: fsync操作を呼び出してファイルをディスクに書き込みます
コミット: ストレージエンジンインターフェースを呼び出してトランザクションをコミットします

ここでの fsync は時間のかかる操作なので、fsync 呼び出しを行う前に十分な書き込みを蓄積し、ここでバッチ テクノロジを使用することを期待しています。原則として、上記のステップの各ステージには対応するタスクのリンク リストがあり、このステージに入る各スレッドは独自のタスクをリンク リストに追加し、リンク リストは正確性を確保するためにロックされます。リンク リストに参加する最初のスレッドがリーダーになり、後続のスレッドはフォロワーになります。リンク リスト内のすべてのタスクはバッチを形成し、リーダーによって実行されますが、フォロワーはタスクが完了するまで待機するだけです。

あるステージのリンク リスト タスクが完了すると、これらのタスクは次のステージに入り、そのステージのタスク リンク リストに追加され、上記の実行フローが繰り返されます。

この設計には次の利点があります。

1. 各スレッドの代わりにリーダー実行を使用すると、書き込み/ fsync呼び出しの数を効果的に減らし、効率を向上させることができます。
2. トランザクションのバイナリログ書き込みとエンジン層の送信の順序が一貫していることを保証できます。
3. prepare_commit_mutex ロックによって強制的にシリアル化されることなく、複数のトランザクションを同時に実行できます。
   

さらに、MYSQL では準備フェーズでの REDO ログのフラッシュがさらに最適化されました。元の設計では、複数のトランザクションが同時に REDO ログをフラッシュできましたが、これも非効率的でした。準備フェーズでの REDO ログのフラッシュは、コミット フェーズのフラッシュ フェーズで実行できます。ただし、説明が必要な小さな問題があります。最適化の前は、各スレッドが自身の redo ログをフラッシュする責任があり、フラッシュする必要がある redo ログの LSN を認識しています。リーダー スレッドがフラッシュ フェーズで redo ログをディスクにフラッシュする場合、リーダー スレッドは各スレッドの redo ログの LSN を認識していないため、log_sys の最大 LSN に単純に大まかにフラッシュします。これにより、送信されるトランザクションの redo ログをディスクにフラッシュできるようになります。

要約する

これで、MYSQL における binlog 最適化の考え方に関するこの記事は終了です。MYSQL binlog 最適化の考え方に関するより関連性の高いコンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。