MySQL DDL 引發的同步延遲該如何解決
前言
寫作案例分析,主要是工具介紹&推薦。MySQL 的同步機制比較單純,主庫上執行過的 DML 和 DDL 會在從庫上再執行一次,那麼主庫上需要 10min 才能執行完的 DDL 理論上在從庫至少也要花費 10min 才能執行完,這意味著從庫的同步會延遲 10min 以上,等 DDL 執行完之後才會繼續追同步。
解決方案
從 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 的操作流程大致如下:
- 在 Master 中創建鏡像表(_tablename_gho)和心跳表(_tablename_ghc)。
- 向心跳表中寫入 Online-DDL 的進度以及時間。
- 在鏡像表上執行 ALTER 操作。
- 偽裝成 slave 連接到 Master 的某個 Slave 實例上獲取 binlog 的信息(默認連接 Slave,也可以連 Master)。
- 在 Master 中完成鏡像表的數據同步:
- 從源表中拷貝數據到鏡像表;
- 依據 Binlog 信息完成增量數據的變更;
- 在源表上加鎖;
- 確認心跳表中的時間,確保數據是完全同步的;
- 用鏡像表替換源表。
- Online DDL 完成。
- 未來考慮會支持的功能或特性:
- 支持外鍵。
- gh-ost 進程意外中斷以後,可以新啟動一個進程繼續進行 Online DDL。
_tablename_ghc 內容如下:
使用限制
- binlog 格式必須使用 row,且binlog_row_image必須是 FULL。
- 需求的權限為SUPER, REPLICATION CLIENT, REPLICATION SLAVE on *.* and ALL on dbname.*
- 如果確認 binlog 的格式為 row,那麼可以加上 -assume-rbr,則不再需要 super 權限。
- 由於不支持 REPLICATION 相關的權限,TiDB 無法使用。
- 不支持外鍵。
- 不論源表是主表還是子表,都無法使用。
- 不支持觸發器。
- 不支持包含 JSON 列的主鍵。
- 遷移表需要有顯示定義的主鍵,或者有非空的唯一索引。
- 遷移工具不區分大小寫英文字母,如果存在同名,但是大小寫不同的表則無法遷移。
- 遷移表的主鍵或者非空唯一索引包含枚舉類型時,遷移效率會大幅度降低。
使用註意
- 如果源表有非常多的數據,盡量分批次刪除。
- delete from table tablename_old limit 5000;
- 或者在業務空閑時段用truncate table tablename_old清空表數據之後再 drop 表。
- 單個 MySQL 實例上啟動多個 gh-ost 來進行多個表的 Online DDL 操作時要制定-replica-server-id參數
- 務必註意可用的磁盤空間,尤其是操作大表的時候。
- gh-ost 的鏡像表包含源表的所有數據,會額外占用一倍的磁盤。
- gh-ost 在操作的過程中會產生大量的 binlog,且binlog_row_image必須為 FULL,會占用比較多的磁盤空間。
- rename 列的操作可能會有問題,考慮 drop 和 add 的操作結合起來。
使用示例
github 官網有安裝包可以下載,參考 release note。
實際命令可以參考下面這個(已開啟瞭 row 模式):
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 等都可能會遇到端口的問題,加上–aliyun-rds參數即可。
- 報錯信息類似於FATAL Unexpected database port reported。
- 相關討論參考 issues。
總結一下
gh-ost 輸出的信息,遷移數據的效率,以及支持的功能都比 pt-osc 等工具要優秀,而 gh-ost 工具的問題(例如磁盤空間)在其他工具也會遇到,因此在 DDL 操作又想避免延遲等問題時,推薦優先考慮 gh-ost。
以上就是MySQL DDL 引發的同步延遲該如何解決的詳細內容,更多關於MySQL DDL 引發的同步延遲的資料請關註WalkonNet其它相關文章!