天天看點
傳回

MySQL大表DDL方式對比一、原生DDL、Online DDL二、pt-online-schema-change三、gh-ost

文章目錄

  • 一、原生DDL、Online DDL
    • 1.1 Online ddl操作原理
    • 1.2 DDL操作的兩個參數
    • 1.3 Online DDL 操作空間存儲的要求
  • 二、pt-online-schema-change
    • 2.1 pt-osc的大緻流程：
    • 2.2 pt-osc的特點
    • 2.3 使用pt-osc的限制
    • 2.4 操作示例
  • 三、gh-ost
    • 3.1 gh-ost的三種模式
    • 3.2 gh-ost的特點
    • 3.3 使用gh-ost的限制

線上環境的大表DDL操作一般都是比較讓人頭疼的，對于大表的DDL我們主要擔心的風險點主要有：

  • 對目前資料庫的資源消耗（CPU、IO、磁盤）的影響
  • DDL執行期間，是否會鎖表，是否會阻塞業務正常DML請求的影響
  • 在主從複制架構中，對從庫的複制延遲的影響

作為一名DBA，對于DDL執行消耗的評估，選擇最佳方式執行DDL就是非常重要了，以下主要講解一些線上環境執行DDL的方法和工具

一、原生DDL、Online DDL

從MySQL5.6開始支援Online DDL，Online DDL相對于普通的DDL操作在對資料庫并發性支援以及鎖、IO、空間資源消耗上都有較大的優化。但是，并不是所有類型的DDL都是執行Online的。我們在執行一個DDL變更時，為減少為生産環境的影響，必須要清楚該操作的基本屬性以及其帶來的資源消耗。

DDL操作帶來的資源消耗：

  • 對業務并發的影響，DDL操作是否會影響到正常業務的DML請求
  • 對資料庫本身性能的影響，DDL操作執行耗時以及帶來的IO資源消耗
  • 對資料庫伺服器空間存儲帶來的影響，伺服器剩餘空間是否滿足執行DDL語句
  • 對資料庫架構中從庫複制延遲的影響，

1.1 Online ddl操作原理

Online DDL操作可以分為以下三個階段：

  • 初始化階段

該階段會判斷DDL語句執行需要的算法以及鎖模式，盡可能減少DDL執行期間對資料庫并發和資源的消耗，該階段需要持有變更表的MDL讀鎖/

  • 執行階段

該階段準備執行DDL語句，也是DDL操作中最耗時的一個階段，需要将MDL讀鎖提升為MDL寫鎖，若DDL操作支援并發DML可快速将MDL寫鎖降級為MDL讀鎖，開始進行表資料重建等操作。

  • 送出表定義階段

該階段是表定義送出階段，會将MDL讀鎖再次更新為MDL寫鎖，變更表結構定義，完成DDL操作，這是一個比較快的操作。

1.2 DDL操作的兩個參數

1、ALGORITHM（算法）

Algorithm主要有3中參數選擇：Inplace、Copy、default，Inplace > copy DDL操作執行消耗很大程度上取決于該操作是否可使用inplace政策，是否需要重建表

  • Copy：MySQL5.6之前預設使用的方式，DDL操作會在Server層建立一個臨時表用于資料拷貝，當拷貝結束後最後通過rename進行替換，資源消耗比較巨大。
  • Inplace：MySQL5.6引入online DDL後預設優先使用的方式，inplace算法下又分為是否需要重建表兩種，對于重建表操作，在引擎層内部建立臨時表來完成DDL操作，不涉及Server層的資料拷貝；對于非重建表操作，基本上可直接在其表中繼資料資訊進行變更即可，整體來講inplace算法的執行效率相對于copy算法都是要好很多的。
  • Default：預設我們不顯式添加algorithm或者指定algorithm=default時，MySQL預設評估DDL操作執行，選擇最佳算法執行

2、Lock（鎖）

lock是影響資料庫并發性的關鍵名額，主要有以下幾種情況：

  • LOCK=NONE ：DDL執行期間允許并發查詢以及并發DML
  • LOCK=SHARED ：DDL執行期間允許并發查詢，不允許并發DML
  • LOCK=DEFAULT ：DDL執行期間，盡可能選擇最佳的方式執行DDL，避免對資料庫業務并發的影響
  • LOCK=EXCLUSIVE ：DDL執行期間禁止并發查詢以及并發DML

1.3 Online DDL 操作空間存儲的要求

  • 臨時日志檔案空間消耗

在Online DDL執行期間允許并發DML操作，這些并發的DML操作會記錄在臨時的日志空間中，該空間按需申請，沒吃申請機關由參數innodb_sort_buffer_size 控制，最大上限由參數innodb_online_alter_log_max_size 控制。若執行DDL期間臨時日志檔案空間消耗超過innodb_online_alter_log_max_size 參數，則DDL操作執行失敗并復原期間仍未送出的事物。

  • 臨時排序檔案空間消耗

Online DDL執行期間重建表的操作是需要使用到臨時排序空間的，，

  • 臨時中間表檔案空間消耗

二、pt-online-schema-change

2.1 pt-osc的大緻流程：

  • 建立_tblname_new表，其表結構與DDL原表（tblname）表結構一緻
  • 對_tblname_new表進行DDL變更操作
  • 在原表tblname上分别針對Insert/Update/Delete操作建立觸發器
  • 将原表中的曆史資料拷貝至_tblname_new表，同時通過觸發器在拷貝資料期間原表發生過的DML操作映射到_tblname_new表上
  • 如果原表有外鍵限制，處理外鍵限制
  • 将原表tblname重命名為_tblname_old，将_tblname_new重命名為tblname表
  • 删除_tblname_old表，DDL變更結束

2.2 pt-osc的特點

  • 通過資料拷貝與觸發器來實作DDL變更操作
  • 可控制進行DDL操作期間最大負載與slave最大延遲
  • DDL操作需要的空間消耗包括：表資料空間一倍多的空間消耗+binlog日志空間消耗

2.3 使用pt-osc的限制

  • 表需要有主鍵，因為pt-osc進行事物拆分時使用了“force index(primary)”
  • 避免原表上存在觸發器，以防止與pt工具産生幹擾
  • 在使用pt-osc工具進行變更期間，容易因為觸發器與正常的業務事物産生死鎖

2.4 操作示例 

# pt-online-schema-change -h127.0.0.1 -P3306 -uroot -p'123' --recursion-method=none --no-version-check --charset=utf8 --dry-run --print --alter="add index idx_id1(id1)" D=db1,t=t5
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `db1`.`t5` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `db1`.`_t5_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id1` int(11) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57622883 DEFAULT CHARSET=utf8mb4
Created new table db1._t5_new OK.
Altering new table...
ALTER TABLE `db1`.`_t5_new` add index idx_id1(id1)
Altered `db1`.`_t5_new` OK.
Not creating triggers because this is a dry run.
-----------------------------------------------------------
Skipped trigger creation:
Event : DELETE
Name  : pt_osc_db1_t5_del
SQL   : CREATE TRIGGER `pt_osc_db1_t5_del` AFTER DELETE ON `db1`.`t5` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `db1`.`_t5_new` WHERE `db1`.`_t5_new`.`id` <=> OLD.`id`; END
Suffix: del
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Skipped trigger creation:
Event : UPDATE
Name  : pt_osc_db1_t5_upd
SQL   : CREATE TRIGGER `pt_osc_db1_t5_upd` AFTER UPDATE ON `db1`.`t5` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `db1`.`_t5_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `db1`.`_t5_new`.`id` <=> OLD.`id`; REPLACE INTO `db1`.`_t5_new` (`id`, `id1`, `name`) VALUES (NEW.`id`, NEW.`id1`, NEW.`name`); END
Suffix: upd
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Skipped trigger creation:
Event : INSERT
Name  : pt_osc_db1_t5_ins
SQL   : CREATE TRIGGER `pt_osc_db1_t5_ins` AFTER INSERT ON `db1`.`t5` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `db1`.`_t5_new` (`id`, `id1`, `name`) VALUES (NEW.`id`, NEW.`id1`, NEW.`name`);END
Suffix: ins
Time  : AFTER
-----------------------------------------------------------
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `db1`.`_t5_new` (`id`, `id1`, `name`) SELECT `id`, `id1`, `name` FROM `db1`.`t5` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 1008 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`t5` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t5_del`
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t5_upd`
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t5_ins`
2021-06-26T17:45:21 Dropping new table...
DROP TABLE IF EXISTS `db1`.`_t5_new`;
2021-06-26T17:45:21 Dropped new table OK.
Dry run complete.  `db1`.`t5` was not altered.

三、gh-ost

3.1 gh-ost的三種模式

gh-ost工具主要有三種模式：connect to replica 、 connect to master 、 migrate/test on replica，前兩種模式的差別是binlog的讀取是從主庫還是從庫進行讀取，第三種模式主要是用來做一定的測試，以connect to replica為例，其DDL操作流程主要如下

  • 根據原表結構_tblname_gho，對_tblname_gho表進行DDL變更
  • gh-ost會連結master資料庫執行個體用于曆史資料拷貝，
  • 連接配接slave資料庫執行個體進行增量資料的binlog讀取并應用
  • cut-over是最後一步，鎖住主庫的源表，等待binlog 應用完畢，然後替換gh-ost表為源表。

3.2 gh-ost的特點

  • 不依賴觸發器而是通過讀取資料庫binlog進行日志回放
  • 單線程回放binlog來替換觸發器，是以增量DML回放效率不如觸發器
  • 可控制進行DDL操作期間最大負載與slave最大延遲
  • DDL操作需要的空間消耗包括：表資料空間一倍多的空間消耗+binlog日志空間消耗
  • 對于DDL操作的靈活度掌控，可暫停，可動态修改參數；DBA可以根據執行情況來快速調整預設的參數，可快可慢，實作DDL操作性能和對業務影響的平衡；
  • 更為穩健的切表控制：将-cut-over-lock-timeout-seconds和-default-retries 配合使用，可以對切表進行靈活的控制。避免pt-osc切表異常導緻對業務造成嚴重影響；

3.3 使用gh-ost的限制 

# gh-ost --ok-to-drop-table --host="127.0.0.1" --port=3306 --user="root" --password="123" --database="db1" --table="t1" --verbose --alter="add index idx_id1(id1)" --panic-flag-file=/tmp/t1.ghost.panic.flag --allow-on-master  --throttle-flag-file /tmp/t1.log --execute
2021-06-26 19:59:57 INFO starting gh-ost 1.1.2
2021-06-26 19:59:57 INFO Migrating `db1`.`t1`
2021-06-26 19:59:57 INFO inspector connection validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO User has ALL privileges
2021-06-26 19:59:57 INFO binary logs validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO Restarting replication on 127.0.0.1:3306 to make sure binlog settings apply to replication thread
2021-06-26 19:59:57 INFO Inspector initiated on mdw:3306, version 5.7.23-log
2021-06-26 19:59:57 INFO Table found. Engine=InnoDB
2021-06-26 19:59:57 INFO Estimated number of rows via EXPLAIN: 23
2021-06-26 19:59:57 INFO Recursively searching for replication master
2021-06-26 19:59:57 INFO Master found to be mdw:3306
2021-06-26 19:59:57 INFO log_slave_updates validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO streamer connection validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO Connecting binlog streamer at mysql-bin.000012:328158254
[2021/06/26 19:59:57] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 127.0.0.1 3306 root    false false <nil> false UTC true 0 0s 0s 0 false}
[2021/06/26 19:59:57] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000012, 328158254)
[2021/06/26 19:59:57] [info] binlogsyncer.go:203 register slave for master server 127.0.0.1:3306
2021-06-26 19:59:57 INFO applier connection validated on 127.0.0.1:3306
[2021/06/26 19:59:57] [info] binlogsyncer.go:723 rotate to (mysql-bin.000012, 328158254)
2021-06-26 19:59:57 INFO rotate to next log from mysql-bin.000012:0 to mysql-bin.000012
2021-06-26 19:59:57 INFO applier connection validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO will use time_zone='+08:00' on applier
2021-06-26 19:59:57 INFO Examining table structure on applier
2021-06-26 19:59:57 INFO Applier initiated on mdw:3306, version 5.7.23-log
2021-06-26 19:59:57 INFO Dropping table `db1`.`_t1_ghc`
2021-06-26 19:59:57 INFO Table dropped
2021-06-26 19:59:57 INFO Creating changelog table `db1`.`_t1_ghc`
2021-06-26 19:59:57 INFO Changelog table created
2021-06-26 19:59:57 INFO Creating ghost table `db1`.`_t1_gho`
2021-06-26 19:59:57 INFO Ghost table created
2021-06-26 19:59:57 INFO Altering ghost table `db1`.`_t1_gho`
2021-06-26 19:59:57 INFO Ghost table altered
2021-06-26 19:59:57 INFO Altering ghost table AUTO_INCREMENT value `db1`.`_t1_gho`
2021-06-26 19:59:57 INFO Ghost table AUTO_INCREMENT altered
2021-06-26 19:59:57 INFO Intercepted changelog state GhostTableMigrated
2021-06-26 19:59:57 INFO Waiting for ghost table to be migrated. Current lag is 0s
2021-06-26 19:59:57 INFO Handled changelog state GhostTableMigrated
2021-06-26 19:59:57 INFO Chosen shared unique key is PRIMARY
2021-06-26 19:59:57 INFO Shared columns are id,id1
2021-06-26 19:59:57 INFO Listening on unix socket file: /tmp/gh-ost.db1.t1.sock
2021-06-26 19:59:57 INFO Migration min values: [1]
2021-06-26 19:59:57 INFO Migration max values: [23]
2021-06-26 19:59:57 INFO Waiting for first throttle metrics to be collected
2021-06-26 19:59:57 INFO First throttle metrics collected
# Migrating `db1`.`t1`; Ghost table is `db1`.`_t1_gho`
# Migrating mdw:3306; inspecting mdw:3306; executing on mdw
# Migration started at Sat Jun 26 19:59:57 +0800 2021
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-flag-file: /tmp/t1.log
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/t1.ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.db1.t1.sock
Copy: 0/23 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000012:328160456; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
Copy: 0/23 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000012:328164813; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
2021-06-26 19:59:58 INFO Row copy complete
Copy: 23/23 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000012:328165755; Lag: 0.01s, HeartbeatLag: 0.02s, State: migrating; ETA: due
2021-06-26 19:59:58 INFO Grabbing voluntary lock: gh-ost.24.lock
2021-06-26 19:59:58 INFO Setting LOCK timeout as 6 seconds
2021-06-26 19:59:58 INFO Looking for magic cut-over table
2021-06-26 19:59:58 INFO Creating magic cut-over table `db1`.`_t1_del`
2021-06-26 19:59:58 INFO Magic cut-over table created
2021-06-26 19:59:58 INFO Locking `db1`.`t1`, `db1`.`_t1_del`
2021-06-26 19:59:58 INFO Tables locked
2021-06-26 19:59:58 INFO Session locking original & magic tables is 24
2021-06-26 19:59:58 INFO Writing changelog state: AllEventsUpToLockProcessed:1624708798317058699
2021-06-26 19:59:58 INFO Intercepted changelog state AllEventsUpToLockProcessed
2021-06-26 19:59:58 INFO Handled changelog state AllEventsUpToLockProcessed
2021-06-26 19:59:58 INFO Waiting for events up to lock
Copy: 23/23 100.0%; Applied: 0; Backlog: 1/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000012:328171119; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2021-06-26 19:59:59 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1624708798317058699
2021-06-26 19:59:59 INFO Done waiting for events up to lock; duration=977.509552ms
# Migrating `db1`.`t1`; Ghost table is `db1`.`_t1_gho`
# Migrating mdw:3306; inspecting mdw:3306; executing on mdw
# Migration started at Sat Jun 26 19:59:57 +0800 2021
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-flag-file: /tmp/t1.log
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/t1.ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.db1.t1.sock
Copy: 23/23 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000012:328171605; Lag: 0.01s, HeartbeatLag: 0.02s, State: migrating; ETA: due
2021-06-26 19:59:59 INFO Setting RENAME timeout as 3 seconds
2021-06-26 19:59:59 INFO Session renaming tables is 18
2021-06-26 19:59:59 INFO Issuing and expecting this to block: rename /* gh-ost */ table `db1`.`t1` to `db1`.`_t1_del`, `db1`.`_t1_gho` to `db1`.`t1`
2021-06-26 19:59:59 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2021-06-26 19:59:59 INFO Checking session lock: gh-ost.24.lock
2021-06-26 19:59:59 INFO Connection holding lock on original table still exists
2021-06-26 19:59:59 INFO Will now proceed to drop magic table and unlock tables
2021-06-26 19:59:59 INFO Dropping magic cut-over table
2021-06-26 19:59:59 INFO Releasing lock from `db1`.`t1`, `db1`.`_t1_del`
2021-06-26 19:59:59 INFO Tables unlocked
2021-06-26 19:59:59 INFO Tables renamed
2021-06-26 19:59:59 INFO Lock & rename duration: 1.013337139s. During this time, queries on `t1` were blocked
[2021/06/26 19:59:59] [info] binlogsyncer.go:164 syncer is closing...
[2021/06/26 19:59:59] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
2021-06-26 19:59:59 INFO Closed streamer connection. err=<nil>
2021-06-26 19:59:59 INFO Dropping table `db1`.`_t1_ghc`
[2021/06/26 19:59:59] [info] binlogsyncer.go:179 syncer is closed
2021-06-26 19:59:59 INFO Table dropped
2021-06-26 19:59:59 INFO Dropping table `db1`.`_t1_del`
2021-06-26 19:59:59 INFO Table dropped
2021-06-26 19:59:59 INFO Done migrating `db1`.`t1`
2021-06-26 19:59:59 INFO Removing socket file: /tmp/gh-ost.db1.t1.sock
2021-06-26 19:59:59 INFO Tearing down inspector
2021-06-26 19:59:59 INFO Tearing down applier
2021-06-26 19:59:59 INFO Tearing down streamer
2021-06-26 19:59:59 INFO Tearing down throttler
# Done
mysql Online DDL gh-ost pt-osc mysql ddl
上一篇: mysql pt online ddl_MySQL Online DDL 工具之pt-online-schema-change
下一篇: 新能宜維小課堂：電池管理系統應具備以下功能。電池管理系統應具備以下功能：·1.實時采集電池系統運作狀态參數。實時采集電動

繼續閱讀