MySQL DDL による同期遅延を解決する方法

MySQL DDL による同期遅延を解決する方法

序文

ツールの紹介と推奨を中心に事例分析を執筆します。 MySQL の同期メカニズムは比較的単純です。マスター データベースで実行された DML と DDL は、スレーブ データベースで再度実行されます。そのため、マスター データベースで実行に 10 分かかる DDL は、理論上はスレーブ データベースで実行するのに少なくとも 10 分かかります。つまり、スレーブ データベースの同期は 10 分以上遅延され、DDL が実行された後にのみ同期が続行されます。

解決

MySQL の同期原則の観点から見ると、主な理由は、DDL 操作だけで時間がかかりすぎるため、スレーブ データベースがマスター状態のままになることです。そうすると、この問題の解決策は簡単に考えられます。DDL 操作を「分解」し、大きな操作 (大きなトランザクションにも同じことが当てはまります) を複数の小さな操作に分割し、単一の操作の時間を短縮します。

DDL 操作を「分解」するには、通常、pt-osc、facebook-osc、oak-online-alter-table、gh-ost などの MySQL オンライン DDL ツールが使用されます。これらのツールの考え方は似ています。ソース テーブルのミラー テーブルを作成し、最初にテーブル構造の変更を実行してから、ソース テーブルの完全なデータと増分データを同期します。これにより、単一の DDL 操作によって発生する同期の遅延を回避できます。

ツールの紹介

この記事では、Github が管理する MySQL オンライン DDL ツールである gh-ost を紹介します。これもミラー テーブル形式を使用しますが、非効率的なトリガーの使用を放棄し、代わりにミラー テーブルとソース テーブル間のデータの一貫性を維持するために必要な増分データを binlog から抽出します。ソース テーブルとミラー テーブルの名前が最終的に変更されると、オンライン DDL 操作全体によって読み取りと書き込みが数秒間ブロックされるだけです。

仕組み

go-ostの操作プロセスは次のとおりです。

  • マスターにミラー テーブル (_tablename_gho) とハートビート テーブル (_tablename_ghc) を作成します。
  • Online-DDL の進行状況と時間をハートビート テーブルに書き込みます。
  • ミラー テーブルに対して ALTER 操作を実行します。
  • スレーブに偽装し、マスターのスレーブ インスタンスに接続して、バイナリ ログ情報を取得します (デフォルトではスレーブに接続しますが、マスターに接続することもできます)。
  • マスター内のミラー テーブルのデータ同期を完了します。
    • ソース テーブルからミラー テーブルにデータをコピーします。
    • Binlog 情報に基づいて増分データの変更を完了します。
  • ソース テーブルをロックします。
  • データが完全に同期されていることを確認するために、ハートビート テーブルの時間を確認します。
  • ソース テーブルをミラー テーブルに置き換えます。
  • オンラインDDLが完了しました。
  • 将来サポートされる予定の機能または特徴:
    • 外部キーをサポートします。
    • gh-ost プロセスが予期せず終了した場合は、新しいプロセスを開始してオンライン DDL を続行できます。

_tablename_ghc の内容は次のとおりです。

使用制限

  • binlog 形式では row を使用する必要があり、binlog_row_image は FULL である必要があります。
  • 必要な権限は、*.* に対する SUPER、REPLICATION CLIENT、REPLICATION SLAVE、および dbname.* に対する ALL です。
    • binlog 形式が row であることを確認したら、-assume-rbr を追加でき、super 権限は不要になります。
    • TiDB は、REPLICATION 関連の権限をサポートしていないため、使用できません。
  • 外部キーはサポートされていません。
    • ソース テーブルがプライマリ テーブルであるか子テーブルであるかに関係なく、使用できません。
  • トリガーはサポートされていません。
  • JSON 列を含む主キーはサポートされていません。
  • 移行テーブルには、明示的に定義された主キーまたは空でない一意のインデックスが必要です。
  • 移行ツールでは大文字と小文字は区別されません。同じ名前でも大文字と小文字が異なるテーブルが存在する場合、移行できません。
  • 移行テーブルの主キーまたは空でない一意のインデックスに列挙型が含まれている場合、移行効率は大幅に低下します。

使用上の注意

  • ソース テーブルに大量のデータがある場合は、バッチで削除してみてください。
    • テーブル tablename_old から 5000 を削除します。
    • または、業務アイドル時間中に、truncate table tablename_old を使用してテーブル データをクリアし、テーブルを削除します。
  • 複数のテーブルに対してオンライン DDL 操作を実行するために、単一の MySQL インスタンス上で複数の gh-ost サーバーを起動する場合は、-replica-server-id パラメータを指定する必要があります。
  • 特に大きなテーブルを扱う場合には、使用可能なディスク容量を常に意識してください。
    • gh-ost のミラー テーブルにはソース テーブルのすべてのデータが含まれており、2 倍のディスク領域を占有します。
    • gh-ost は操作中に大量の binlog を生成するため、binlog_row_image は FULL である必要があり、大量のディスク領域を占有します。
  • 列の名前変更操作によって問題が発生する可能性があります。削除操作と追加操作を組み合わせることを検討してください。

使用例

インストール パッケージは github 公式 Web サイトからダウンロードできます。リリース ノートを参照してください。

実際のコマンドは次のように参照できます (行モードが有効)。

gh-ost --max-load=実行中のスレッド数=50 \
            --critical-load=実行中のスレッド数=100 \
            --チャンクサイズ=3000 --user="temp" --password="test" --host=10.10.1.10 \
            --allow-on-master --database="sbtest" --table="sbtest1" \
            --alter="engine=innodb" --cut-over=default \
            --正確な行数 --同時行数 --デフォルトの再試行回数=120 \
            --timestamp-old-table -assume-rbr --panic-flag-file=/tmp/ghost.panic.flag \
             - 実行する

いくつかのパラメータの説明

上記の注文内容が優先されます。

max-load=Threads_running=50 50 を超えるクライアントが SQL クエリを実行している場合、オンライン DDL 操作は一時停止されます。 critical-load=Threads_running=100 100 を超えるクライアントが SQL クエリを実行している場合、オンライン DDL 操作は中断されます。 chunk-size=3000 各同期操作は 3000 行のデータを処理します。 allow-on-master マスター データベースでオンライン DDL に関連するすべての操作を実行できるようにします。 alter オンライン DDL 操作には、alter ステートメントの一部 (角括弧で囲まれた部分) のみが必要です。
                                     例: テーブル sbtest.sbtest1 を変更する [列 t int not NULL を追加]
cut-over=default データ同期が完了した後、ミラーテーブルとソーステーブルを自動的に切り替えます exact-rowcount 行数を正確に計算して、より正確な進行状況を提供します timestamp-old-table タイムスタンプを使用して古いテーブルに名前を付けます assume-rbr スレーブスレッドの再起動と行形式のチェックをスキップします。設定後はスーパー権限は必要ありません panic-flag-file このファイルを作成すると、オンライン DDL 操作が強制的に中断されます

これらのパラメータに加えて、gh-ost はオンライン DDL 操作を外部から一時停止または強制的に中止するさまざまな方法も提供します。詳細情報は、gh-ost --help コマンドを使用して表示できます。

出力例

# `sbtest`.`sbtest1` を移行しています。ゴースト テーブルは `sbtest`.`_sbtest1_gho` です。
# 10.10.1.10:3306 を移行中; 10.10.1.10:3306 を検査中; localhost-debian で実行中
# 移行は 2020 年 7 月 30 日木曜日 11:30:17 +0800 に開始されました
# チャンクサイズ: 3000; 最大ラグミリ秒: 1500ms; dml バッチサイズ: 10; 最大負荷: Threads_running=50; クリティカル負荷: Threads_running=100; ナイス比率: 0.000000
# スロットル追加フラグファイル: /tmp/gh-ost.throttle
# パニックフラグファイル: /tmp/ghost.panic.flag
# Unix ソケットで提供: /tmp/gh-ost.sbtest.sbtest1.sock
コピー: 0/9863066 0.0%; 適用済み: 0; バックログ: 0/1000; 時間: 0 秒 (合計)、0 秒 (コピー); ストリーマー: mysql-bin.000050:31635038; ラグ: 0.03 秒、状態: 移行中; ETA: N/A
コピー: 0/9863066 0.0%; 適用済み: 0; バックログ: 0/1000; 時間: 1 秒 (合計)、1 秒 (コピー); ストリーマー: mysql-bin.000050:31639503; ラグ: 0.03 秒、状態: 移行中; ETA: N/A
コピー: 69000/9999998 0.7%; 適用済み: 0; バックログ: 0/1000; 時間: 2 秒 (合計)、2 秒 (コピー); ストリーマー: mysql-bin.000050:44815698; ラグ: 0.03 秒、状態: 移行中; ETA: 4 分 49 秒
コピー: 135000/9999998 1.4%; 適用済み: 0; バックログ: 0/1000; 時間: 3 秒 (合計)、3 秒 (コピー); ストリーマー: mysql-bin.000050:57419220; ラグ: 0.03 秒、状態: 移行中; ETA: 3 分 39 秒
コピー: 195000/9999998 2.0%; 適用済み: 0; バックログ: 0/1000; 時間: 4 秒 (合計)、4 秒 (コピー); ストリーマー: mysql-bin.000050:68877374; ラグ: 0.03 秒、状態: 移行中; ETA: 3 分 21 秒
......（省略）
コピー: 9729000/9999998 97.3%; 適用済み: 0; バックログ: 0/1000; 時間: 3 分 16 秒 (合計)、3 分 16 秒 (コピー); ストリーマー: mysql-bin.000057:8595335; ラグ: 0.04 秒、状態: 移行中; ETA: 5 秒
[2020/07/30 11:33:32] [情報] binlogsyncer.go:723 を (mysql-bin.000057, 4) にローテーションします
コピー: 9774000/9999998 97.7%; 適用済み: 0; バックログ: 0/1000; 時間: 3 分 17 秒 (合計)、3 分 17 秒 (コピー); ストリーマー: mysql-bin.000057:17190073; ラグ: 0.03 秒、状態: 移行中; ETA: 4 秒
[2020/07/30 11:33:32] [情報] binlogsyncer.go:723 を (mysql-bin.000057, 4) にローテーションします
コピー: 9822000/9999998 98.2%; 適用済み: 0; バックログ: 0/1000; 時間: 3 分 18 秒 (合計)、3 分 18 秒 (コピー); ストリーマー: mysql-bin.000057:26357495; ラグ: 0.04 秒、状態: 移行中; ETA: 3 秒
コピー: 9861000/9999998 98.6%; 適用済み: 0; バックログ: 0/1000; 時間: 3 分 19 秒 (合計)、3 分 19 秒 (コピー); ストリーマー: mysql-bin.000057:33806865; ラグ: 0.03 秒、状態: 移行中; ETA: 2 秒
コピー: 9903000/9999998 99.0%; 適用済み: 0; バックログ: 0/1000; 時間: 3 分 20 秒 (合計)、3 分 20 秒 (コピー); ストリーマー: mysql-bin.000057:41828922; ラグ: 0.03 秒、状態: 移行中; ETA: 1 秒
コピー: 9951000/9999998 99.5%; 適用済み: 0; バックログ: 0/1000; 時間: 3 分 21 秒 (合計)、3 分 21 秒 (コピー); ストリーマー: mysql-bin.000057:50996347; ラグ: 0.03 秒、状態: 移行中; ETA: 0 秒
コピー: 9999998/9999998 100.0%; 適用済み: 0; バックログ: 0/1000; 時間: 3 分 22 秒 (合計)、3 分 21 秒 (コピー); ストリーマー: mysql-bin.000057:60354465; ラグ: 0.03 秒、状態: 移行中; ETA: 予定
# `sbtest`.`sbtest1` を移行しています。ゴースト テーブルは `sbtest`.`_sbtest1_gho` です。
# 10.10.1.10:3306 を移行中; 10.10.1.10:3306 を検査中; onlocalhost-debian を実行中
# 移行は 2020 年 7 月 30 日木曜日 11:30:17 +0800 に開始されました
# チャンクサイズ: 3000; 最大ラグミリ秒: 1500ms; dml バッチサイズ: 10; 最大負荷: Threads_running=50; クリティカル負荷: Threads_running=100; ナイス比率: 0.000000
# スロットル追加フラグファイル: /tmp/gh-ost.throttle
# パニックフラグファイル: /tmp/ghost.panic.flag
# Unix ソケットで提供: /tmp/gh-ost.sbtest.sbtest1.sock
コピー: 9999998/9999998 100.0%; 適用済み: 0; バックログ: 0/1000; 時間: 3 分 23 秒 (合計)、3 分 21 秒 (コピー); ストリーマー: mysql-bin.000057:60359997; ラグ: 0.03 秒、状態: 移行中; ETA: 予定
[2020/07/30 11:33:41] [info] binlogsyncer.go:164 syncer を終了しています...
[2020/07/30 11:33:41] [エラー] binlogstreamer.go:77 同期を終了しました。エラー: 同期を終了しています...
[2020/07/30 11:33:41] [情報] binlogsyncer.go:179 syncerは閉じられています

ログの内容を見ると、移行の詳細な進行状況のパーセンテージと残り時間が出力されており、メンテナンスの終了時間を見積もったり、DDL 実行の進行状況を確認したりするのに非常に便利です。

Tencent Cloud データベース MySQL 使用上の注意

  • Tencent Cloud Database MySQL のデフォルトの binlog_row_image は MINIMAL です。使用前にコントロールで積極的に FULL に調整する必要があります (オンライン変更、即時有効)。
  • Tencent Cloud Database、Alibaba Cloud Database、コンテナ内のMySQLなどでポートの問題が発生する可能性があります。--aliyun-rdsパラメータを追加するだけです。
    • エラー メッセージは、「FATAL 予期しないデータベース ポートが報告されました」に似ています。
    • 関連する議論については、問題を参照してください。

総括する

gh-ost は、pt-osc や他のツールよりも優れた情報を出力し、データをより効率的に移行し、より多くの機能をサポートします。ただし、gh-ost の問題 (ディスク領域など) は他のツールでも発生します。したがって、DDL 操作中のレイテンシの問題を回避したい場合は、gh-ost を優先することをお勧めします。

上記は、MySQL DDL による同期遅延を解決する方法の詳細です。MySQL DDL による同期遅延の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQLのSeconds_Behind_Masterの詳細な説明
  • MySQL マスタースレーブ同期遅延の原因と解決策
  • MYSQL マスタースレーブ非同期遅延原理の分析と解決
  • MySQLマスタースレーブデータ同期遅延の削減の詳細な説明
  • MySQL 同期遅延が発生したときに Seconds_Behind_Master が 0 のままになる理由

<<:  【Webデザイン】E-WebTemplates の美しい海外の Web ページ テンプレート (FLASH+PSD ソース ファイル+HTML) を共有します

>>:  1つの記事でJavaScriptのクロージャ関数について学ぶ

推薦する

docker を使用して Redis マスター/スレーブを構築する方法

1. Docker環境を構築する1. Dockerfileを作成する Centos:latest か...

TypeScript 3.7 で注目すべき 3 つの新機能について簡単に説明します。

目次序文オプションの連鎖ヌル結合呼び出されていない関数のチェック他の序文TypeScript 3.7...

Reactはグローバル箇条書きボックスメソッドをカプセル化します

この記事の例では、Reactカプセル化グローバルポップアップボックスの具体的なコードを参考までに共有...

GolangでMySQLデータベースを操作するための実装コード

序文Golang は、SQL データベースにアクセスするための database/sql パッケージ...

Vueカスタムコンポーネントは双方向バインディングを実装します

シナリオ：一般的に使用される親コンポーネントと子コンポーネント間の相互作用方法は次のとおりです。親コ...

フォーム要素の簡単な実装コードでは登録を例に挙げています

コード実装:コードをコピーコードは次のとおりです。 <!DOCTYPE html> &l...

VUEプロジェクトでXSS攻撃に遭遇した実体験

目次序文原因を発見するカスタムフィルタリングルール要約する序文インターネットの急速な発展に伴い、情報...

nginx と keepalived を組み合わせて高可用性を実現するための手順を完了する

序文システムの高可用性を満たすためには、通常、クラスターを構築する必要があります。ホストがクラッシュ...

JavaScript でオブジェクトのプロパティを削除する方法

1. 削除delete は、オブジェクトのプロパティを残さずに削除する唯一の方法ですが、その「代替」...

Docker ベースの Jenkins のデプロイに関する詳細なチュートリアル

このドキュメントを作成した当時は2019年12月頃で、er2.200が最新バージョンでした。 1.画...

CSSレイアウトで中央揃えレイアウトを実現する方法

1. 親コンテナーをテーブルに設定し、子をインライン要素に設定します。テキストを表示するサブコンテン...

Ubuntu 18.04 サーバーのパスワードを忘れたり改ざんされた場合にパスワードをリセットする方法

最近、サーバー上のアカウントが2つハッキングされ、パスワードが改ざんされました。幸い、まだ使えるアカ...

よくある Linux 英語エラーの中国語翻訳 (初心者必見)

1.コマンドが見つかりません コマンドが見つかりません2. そのようなファイルまたはディレクトリは...

jsはaudioContextを通じて3Dサウンド効果を実現します

この記事では、audioContextを介して3Dサウンド効果を実現するためのjsの具体的なコードを...

ウェブページ上でデスクトップ exe プログラムを呼び出す簡単な方法

この記事では主に、Web ページ上でデスクトップ exe プログラムを呼び出す方法を紹介します。 W...