MySQL DDLによる同期遅延はどう解決しますか？

8536 ワード
MySQL DDL 同期遅延
前言
文章を書く実例は分析して、主にツールの紹介と推薦です。MySQLの同期メカニズムは単純であり、メインライブラリで実行されたDMLとDDLはライブラリからもう一回実行されます。メインライブラリでは10 minしか実行できないDDL理論上、ライブラリから少なくとも10 minをかけて実行できます。これはライブラリの同期が10 min以上遅れることを意味します。
ソリューション
MySQLの同期原理から見ると、主にDDLという単独操作は時間がかかりすぎて、ライブラリからも持ち主に選ばれることになります。この問題を解決する方法は簡単に思いつきます。「分解」DDLの操作は大きな操作（大きな仕事と同じ道理）を複数の小さな操作に分割して、一回の操作の時間を減らします。
「分解」DDL操作は主にMySQL Online DDLのツールを使います。例えば、pt-osc、facebook-osc、oak-online-alter-table、gh-ostなどです。これらのツールの考え方は似ています。ソーステーブルのイメージテーブルを作成して、まずテーブル構造の変更を実行してから、ソーステーブルのフルデータと増分データを同期させます。したがって、単一のDDL操作による同期遅延を避けることができます。
ツール紹介
本論文ではGh-ostを紹介します。Githubによって維持されたMySQL online DDLツールは、同様にミラーリングテーブルの形式を使用していますが、効果のないtriggerを使用するのをやめて、binlogから必要な増分データを抽出して、イメージ表とソーステーブルのデータの整合性を維持します。Online DDL全体の操作は、最終的なrenameソーステーブルとミラーテーブルの間だけで数秒の読み書きをブロックします。
仕事の原理
go-ostの操作手順は大体以下の通りです。
  • マスターに鏡像表を作成する（ウテーブルシークghoと心拍計テーブルシークghc)
  • 心拍表にOnline-DDLの進捗と時間を書き込む。
  • 鏡像表でALTER操作を行う。
  • マスターにslaveを装って接続されたあるSlaveの例でbinlogsの情報を取得する（デフォルトの接続Slaveは、Masterにも接続できる）。
  • マスターで鏡像表のデータ同期を完了する：
  • ソーステーブルから鏡像テーブルにデータをコピーする。
  • Binlog情報に基づいて増分データの変更を完了する。
  • ソーステーブルにロックをかける。
  • 心拍表の時間を確認し、データが完全に同期されていることを確認する。
  • ソーステーブルをミラーで置換する。
  • オンラインDDLが完了しました。
  • 将来的にサポートされる機能や特性：
  • 外キー対応。
  • gh-ostプロセスが突然中断された後、新たに一つのプロセスを開始してOnline DDLを継続することができます。
    • _。テーブルシークghcの内容は以下の通りです

    使用制限
  • binlogsフォーマットは必ずrowを使用し、binlog_row_イメージはFULLでなければなりません。
  • 必要な権限はSUPER、REPLICATION CLIENT、REPLICATION SLAVE on*.and ALL on dbname.*
  • binlogsのフォーマットがrowであることを確認したら-asume-r brを加えることができます。super権限は不要です。
  • REPLICATIONに関する権限がないため、TiDBは使用できません。
  • 外キーはサポートされていません。
  • ソーステーブルがメインテーブルでもサブテーブルでも使えません。
  • トリガーはサポートされていません。
  • JSON列を含むキーはサポートされていません。
  • 移動表は、定義されたキーを表示するか、または空でない唯一の索引が必要です。
  • 移行ツールは、大文字と英字を区別せず、同名があれば、大文字と小文字が異なる表は移動できません。
  • 移動表の主キーまたは空でない一意の索引に列挙タイプが含まれている場合、移動効率は大幅に低下します。
    • 使用上の注意
  • ソーステーブルが非常に多いデータがあれば、ロットごとに削除する。
  • delete from table table name uold limit 5000
  • または業務空き時間にtruncate table table name_oldテーブルのデータをクリアしてからdrop表を作成します。
  • 単一のMySQLは、複数のgh-ostを起動して複数のテーブルのOnline DDLを操作する際に、-replica-server-indパラメータ
  • 使用可能なディスク空間、特に大きいテーブルを操作する場合には必ず注意してください。
  • gh-ostの鏡像表はソーステーブルのすべてのデータを含み、ディスクの2倍を追加的に占有します。
  • gh-ost操作中に大量のbinlogsが発生し、binlog_row_イメージはFULLでなくてはいけません。ディスクの容量が多くなります。
  • rename列の操作に問題があるかもしれません。dropとaddの操作を考えて結合します。
    • 使用例
    github公式サイトでは、インストールパッケージがダウンロードできます。参考release note。
    実際のコマンドは以下のこれを参考にしてもいいです。
    
gh-ost --max-load=Threads_running=50 \
            --critical-load=Threads_running=100 \
            --chunk-size=3000 --user="temp" --password="test" --host=10.10.1.10 \
            --allow-on-master --database="sbtest" --table="sbtest1" \
            --alter="engine=innodb" --cut-over=default \
            --exact-rowcount --concurrent-rowcount --default-retries=120 \
            --timestamp-old-table -assume-rbr --panic-flag-file=/tmp/ghost.panic.flag \
            --execute
    部分パラメータ説明
    上記の命令内容に準じる。
    
max-load=Threads_running=50           50 client   SQL   ,  Online DDL  
critical-load=Threads_running=100     100 client   SQL   ,  Online DDL  
chunk-size=3000                              3000   
allow-on-master                            Online DDL       
alter                               Online DDL   ,     alter  (     )
                                      ：alter table sbtest.sbtest1 [add column t int not NULL]
cut-over=default                                         
exact-rowcount                             ,        
timestamp-old-table                           
assume-rbr                               slave   row format  ,     super  
panic-flag-file                            ,     Online DDL
    これらのパラメータ以外にも、gh-ostは外部からの一時停止または強制的にOnline DDLの操作を中止する非常に多くの方法を提供しています。詳細な情報はgh-ost--helpコマンドを使って確認できます。
    出力結果例
    
# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`
# Migrating 10.10.1.10:3306; inspecting10.10.1.10:3306; executing on localhost-debian
# Migration started at Thu Jul 30 11:30:17 +0800 2020
# chunk-size: 3000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=50; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.sbtest.sbtest1.sock
Copy: 0/9863066 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000050:31635038; Lag: 0.03s, State: migrating; ETA: N/A
Copy: 0/9863066 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000050:31639503; Lag: 0.03s, State: migrating; ETA: N/A
Copy: 69000/9999998 0.7%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: mysql-bin.000050:44815698; Lag: 0.03s, State: migrating; ETA: 4m49s
Copy: 135000/9999998 1.4%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 3s(copy); streamer: mysql-bin.000050:57419220; Lag: 0.03s, State: migrating; ETA: 3m39s
Copy: 195000/9999998 2.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 4s(copy); streamer: mysql-bin.000050:68877374; Lag: 0.03s, State: migrating; ETA: 3m21s
......（  ）
Copy: 9729000/9999998 97.3%; Applied: 0; Backlog: 0/1000; Time: 3m16s(total), 3m16s(copy); streamer: mysql-bin.000057:8595335; Lag: 0.04s, State: migrating; ETA: 5s
[2020/07/30 11:33:32] [info] binlogsyncer.go:723 rotate to (mysql-bin.000057, 4)
Copy: 9774000/9999998 97.7%; Applied: 0; Backlog: 0/1000; Time: 3m17s(total), 3m17s(copy); streamer: mysql-bin.000057:17190073; Lag: 0.03s, State: migrating; ETA: 4s
[2020/07/30 11:33:32] [info] binlogsyncer.go:723 rotate to (mysql-bin.000057, 4)
Copy: 9822000/9999998 98.2%; Applied: 0; Backlog: 0/1000; Time: 3m18s(total), 3m18s(copy); streamer: mysql-bin.000057:26357495; Lag: 0.04s, State: migrating; ETA: 3s
Copy: 9861000/9999998 98.6%; Applied: 0; Backlog: 0/1000; Time: 3m19s(total), 3m19s(copy); streamer: mysql-bin.000057:33806865; Lag: 0.03s, State: migrating; ETA: 2s
Copy: 9903000/9999998 99.0%; Applied: 0; Backlog: 0/1000; Time: 3m20s(total), 3m20s(copy); streamer: mysql-bin.000057:41828922; Lag: 0.03s, State: migrating; ETA: 1s
Copy: 9951000/9999998 99.5%; Applied: 0; Backlog: 0/1000; Time: 3m21s(total), 3m21s(copy); streamer: mysql-bin.000057:50996347; Lag: 0.03s, State: migrating; ETA: 0s
Copy: 9999998/9999998 100.0%; Applied: 0; Backlog: 0/1000; Time: 3m22s(total), 3m21s(copy); streamer: mysql-bin.000057:60354465; Lag: 0.03s, State: migrating; ETA: due
# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`
# Migrating 10.10.1.10:3306; inspecting 10.10.1.10:3306; executing onlocalhost-debian
# Migration started at Thu Jul 30 11:30:17 +0800 2020
# chunk-size: 3000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=50; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.sbtest.sbtest1.sock
Copy: 9999998/9999998 100.0%; Applied: 0; Backlog: 0/1000; Time: 3m23s(total), 3m21s(copy); streamer: mysql-bin.000057:60359997; Lag: 0.03s, State: migrating; ETA: due
[2020/07/30 11:33:41] [info] binlogsyncer.go:164 syncer is closing...
[2020/07/30 11:33:41] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2020/07/30 11:33:41] [info] binlogsyncer.go:179 syncer is closed
    ログの内容から詳細な進捗率と移転の残り時間が出力されていることが分かります。メンテナンス終了時間を見積り、DDLの進捗状況を確認するととても便利です。
    騰訊雲データベースMySQLの使用注意
  • 騰訊雲データベースMySQLデフォルトのbinlog_row_imageはMINIMALで、使用前にコントロールでFULLに自動調整する必要があります。
  • 騰訊雲データベース、阿里雲データベース、容器中のMySQLなどがポートの問題に遭遇する可能性があります。
  • エラーメッセージはFATAL Uexpected database port reportに似ています。
  • 関連の議論はissuesを参照してください。
    • まとめてみます
    gh-ost出力の情報、移行データの効率、サポートの機能はpt-oscなどより優れていますが、gh-ostツールの問題（例えばディスク空間）は他のツールでも発生しますので、DDL操作では遅延を避けるためにgh-ostを優先的に考慮することをおすすめします。
    以上がMySQL DDLによる同期遅延の詳細です。MySQL DDLによる同期遅延に関する詳細については、他の関連記事に注目してください。
    詳細なMySQL共同クエリの最適化メカニズム
    MySQL Deleteデータを削除した後、ディスク空間が解放されなかった理由