InnoDB Online DDL續

在"InnoDB Online DDL一瞥"中說到了Online DDL的局限性, 主從復制環境中, 若主數據庫上對一大表變更ALTER TABLE, 耗時較長, 該過程在從數據庫上回放, 一般也要較長時間, 這期間主數據庫上對該數據表的DML操作, 將不能及時體現在從數據庫上, 這樣從數據庫的可用性就受到了影響. 

原因可歸結為, 一個大事物未能及時執行完畢, 引起了復制延時(其實MySQL 5.6, 5.7版本中ALTER TABLE還不具有原子性, 此處只是為了便于說明.). 而對于大事物的優化, 首先嘗試分解成多個小事物, 本文主角pt-online-schema-change工具(以下簡稱pt-osc)正是利用了該思想, 其可有效的控制復制延時的問題.

pt-osc在不阻塞讀寫的情況下, 進行數據表變更. 其先創建一個符合要求的新數據表, 然后將原數據表中的數據, 以塊為單位, 拷貝至新數據表中, 這期間原數據表上的DML操作, 都會通過其先前在原數據表上創建的觸發器, 反映到新數據表上. 整個過程中, 該工具會通過多種方法將復制延時和主數據庫負載控制在合理范圍內.

看下實際中pt-osc使用的例子, 其日志輸出也展示了它的工作過程.

mysql@db01: ~$pt-online-schema-change --alter "ADD COLUMN org_id BIGINT UNSIGNED NOTNULL DEFAULT 0" --nocheck-replication-filters --recursion-method=processlist h=192.168.19.168,P=3316,u=zz,p=123456,D=test,t=test_zzzz --execute

Found 1 slaves:

db02 ->192.168.19.190:3316

Will check slave lagon:

db02 ->192.168.19.190:3316

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

Altering`test`.`test_zzzz`...

Creating new table...

Created new tabletest._test_zzzz_new OK.

Waiting forever fornew table `test`.`_test_zzzz_new` to replicate to db02...

Altering new table...

Altered`test`.`_test_zzzz_new` OK.

2017-09-15T16:12:11Creating triggers...

2017-09-15T16:12:11Created triggers OK.

2017-09-15T16:12:11Copying approximately 4861821 rows...

Copying`test`.`test_zzzz`:   6% 07:42 remain

...

Copying`test`.`test_zzzz`:  89% 00:41 remain

Copying`test`.`test_zzzz`:  97% 00:08 remain

2017-09-15T16:18:42Copied rows OK.

2017-09-15T16:18:42Analyzing new table...

2017-09-15T16:18:42Swapping tables...

2017-09-15T16:18:42Swapped original and new tables OK.

2017-09-15T16:18:42Dropping old table...

2017-09-15T16:18:42Dropped old table `test`.`_test_zzzz_old` OK.

2017-09-15T16:18:42Dropping triggers...

2017-09-15T16:18:42Dropped triggers OK.

Successfully altered`test`.`test_zzzz`.

通過數據表變更過程中產生的general log, 了解下pt-osc背后運行細節, 從而也可印證上面說的主要工作原理.

Step1, 設置各種超時時間, 以防遇到鎖等待等情況, 可盡快退出, 不影響其它操作.

57049 Query    SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'

57049 Query    SET SESSION innodb_lock_wait_timeout=1

57049 Query    SHOW VARIABLES LIKE 'lock\_wait_timeout'

57049 Query    SET SESSION lock_wait_timeout=60

57049 Query    SHOW VARIABLES LIKE 'wait\_timeout'

57049 Query    SET SESSION wait_timeout=10000

Step2, 創建一個符合變更要求的新表.

57049 Query  CREATE TABLE `test`.`_test_zzzz_new` (

  `id` bigint(20)unsigned NOT NULL AUTO_INCREMENT,

  ...

  PRIMARY KEY(`id`),

  ...

) ENGINE=InnoDBAUTO_INCREMENT=5342221 DEFAULT CHARSET=utf8mb4

57049 Query  ALTER TABLE `test`.`_test_zzzz_new` ADD COLUMN org_id BIGINT UNSIGNED NOT NULL DEFAULT 0

Step3, 創建觸發器, 以便將原數據表上的DML操作, 體現到新數據表上.

57049 Query  CREATE TRIGGER `pt_osc_test_test_zzzz_del`AFTER DELETE ON `test`.`test_zzzz` FOR EACH ROW DELETE IGNORE FROM`test`.`_test_zzzz_new` WHERE `test`.`_test_zzzz_new`.`id` <=> OLD.`id`

57049 Query  CREATE TRIGGER `pt_osc_test_test_zzzz_upd`AFTER UPDATE ON `test`.`test_zzzz` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_test_zzzz_new`WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_test_zzzz_new`.`id` <=>OLD.`id`;REPLACE INTO `test`.`_test_zzzz_new` (`id`, ...) VALUES (NEW.`id`,...);END

57049 Query  CREATE TRIGGER `pt_osc_test_test_zzzz_ins`AFTER INSERT ON `test`.`test_zzzz` FOR EACH ROW REPLACE INTO`test`.`_test_zzzz_new` (`id`, ...) VALUES (NEW.`id`, ...)

Step4, 分塊拷貝數據, 這期間其會監控延時和負載情況.

57049 Query  INSERT LOW_PRIORITY IGNORE INTO`test`.`_test_zzzz_new` (`id`, ...) SELECT `id`, ... FROM `test`.`test_zzzz`FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '147592')) AND ((`id` <='148591')) LOCK IN SHARE MODE /*pt-online-schema-change 4924 copy nibble*/         

Step5, 收尾工作.

57049 Query  ANALYZE TABLE `test`.`_test_zzzz_new` /*pt-online-schema-change */

57049 Query  RENAME TABLE `test`.`test_zzzz` TO`test`.`_test_zzzz_old`, `test`.`_test_zzzz_new` TO `test`.`test_zzzz`

57049 Query  DROP TABLE IF EXISTS `test`.`_test_zzzz_old`

57049 Query  DROP TRIGGER IF EXISTS`test`.`pt_osc_test_test_zzzz_del`

57049 Query  DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_zzzz_upd`

57049 Query  DROP TRIGGER IF EXISTS`test`.`pt_osc_test_test_zzzz_ins`

在主從復制環境下, pt-osc借助選項--recursion-method=processlist獲取從數據庫的信息. 若某臺服務器使用命令行, mysqlbinlog --host=192.168.19.168 --port=3316 --user=zz --password=123456 --read-from-remote-server --raw --stop-never --to-last-log --stop-never-slave-server-id=4444 --result-file=/backup/binlog/ bin.000044, 實時備份binlog, 那processlist方式就失效了, 此時要用dsn方式. 先創建一個數據表, 然后寫入從庫的IP信息, 如下所示:

(root@localhost)[test]> SHOW CREATE TABLE dsns\G

***************************1. row ***************************

      Table: dsns

Create Table: CREATE TABLE `dsns` (

  `id` int(11)NOT NULL AUTO_INCREMENT,

  `parent_id`int(11) DEFAULT NULL,

  `dsn`varchar(255) NOT NULL,

  PRIMARY KEY(`id`)

) ENGINE=InnoDBAUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

1 row in set (0.00sec)

(root@localhost)[test]> SELECT * FROM dsns;

+----+-----------+---------------+

| id | parent_id | dsn          |

+----+-----------+---------------+

|  1 |     NULL | 192.168.19.190 |

+----+-----------+---------------+

1 row in set (0.05sec)

最后, pt-osc命令行如下所示:

pt-online-schema-change --alter "ADD COLUMN org_id BIGINT UNSIGNED NOT NULL DEFAULT 0" --nocheck-replication-filters --recursion-method=dsn=D=test,t=dsns h=192.168.19.168,P=3316,u=zz,p=123456,D=test,t=test_zzzz --execute

pt-osc的局限性

1. 數據表要有主鍵, 或唯一索引, 其實這也是任一InnoDB數據表的設計規范.

2. 有外鍵約束情況下, 使用pt-osc會比較復雜, 實際業務中一般是在應用程序中實現邏輯上的外鍵約束的.

3. MySQL 5.6版本中, 若要變更的數據表上已有觸發器, pt-osc將不能使用, 該情況在5.7版本得了到改善.

pt-osc和OnlineDDL相比, 執行速度會慢, 要求磁盤空間會大, 但其保證了從庫的可用性. 一般建議, 數據表數據量較小時, 可用Online DDL; 若數據量較大(大于500萬或1000萬), 這時要想到Online DDL會造成延時, 可考慮pt-osc.