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          

Step5, 收尾工作.

57049 Query  ANALYZE TABLE `test`.`_test_zzzz_new`

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.