pt-osc原理、限制、及与原生online-ddl比较

1. pt-osc工作过程

1. 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
2. 在新表执行alter table 语句（速度应该很快）
3. 在原表中创建触发器3个触发器分别对应insert,update,delete操作
4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
5. Rename 原表到old表中，在把临时表Rename为原表
6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
7. 默认最后将旧原表删除

2. 常用选项说明

只介绍部分常用的选项

• --host=xxx --user=xxx --password=xxx
  连接实例信息，缩写-h xxx -u xxx -p xxx，密码可以使用参数--ask-pass 手动输入。
• --alter
  结构变更语句，不需要 ALTER TABLE关键字。与原始ddl一样可以指定多个更改，用逗号分隔。
  • 绝大部分情况下表上需要有主键或唯一索引，因为工具在运行当中为了保证新表也是最新的，需要旧表上创建 DELETE和UPDATE 触发器，同步到新表的时候有主键会更快。个别情况是，当alter操作就是在c1列上建立主键时，DELETE触发器将基于c1列。
  • 子句不支持 rename 去给表重命名。
  • alter命令原表就不支持给索引重命名，需要先drop再add，在pt-osc也一样。(mysql 5.7 支持 RENAME INDEX old_index_name TO new_index_name)
    但给字段重命名，千万不要drop-add，整列数据会丢失，使用change col1 col1_new type constraint（保持类型和约束一致，否则相当于修改 column type，不能online）
  • 子句如果是add column并且定义了not null，那么必须指定default值，否则会失败。
  • 如果要删除外键（名 fk_foo），使用工具的时候外键名要加下划线，比如--alter "DROP FOREIGN KEY _fk_foo"

  • D=db_name,t=table_name
    指定要ddl的数据库名和表名

  • --max-load
    默认为Threads_running=25。每个chunk拷贝完后，会检查 SHOW GLOBAL STATUS 的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。
    因为拷贝行有可能会给部分行上锁，Threads_running 是判断当前数据库负载的绝佳指标。

  • --max-lag
    默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况（Seconds_Behind_Master）。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值。--check-interval配合使用，指定出现从库滞后超过 max-lag，则该工具将睡眠多长时间，默认1s，再检查。如--max-lag=5 --check-interval=2。
    熟悉percona-toolkit的人都知道--recursion-method可以用来指定从库dsn记录。另外，如果从库被停止，将会永远等待，直到从开始同步，并且延迟小于该值。

  • --chunk-time
    默认0.5s，即拷贝数据行的时候，为了尽量保证0.5s内拷完一个chunk，动态调整chunk-size的大小，以适应服务器性能的变化。
    也可以通过另外一个选项--chunk-size禁止动态调整，即每次固定拷贝 1k 行，如果指定则默认1000行，且比 chunk-time 优先生效

  • --set-vars
    使用pt-osc进行ddl要开一个session去操作，set-vars可以在执行alter之前设定这些变量，比如默认会设置--set-vars "wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60"。
    因为使用pt-osc之后ddl的速度会变慢，所以预计2.5h只能还不能改完，记得加大wait_timeout。

  • --dry-run
    创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节，和--print配合最佳。。

  • --execute
    确定修改表，则指定该参数。真正执行alter。–dry-run与–execute必须指定一个，二者相互排斥

3. 使用疑惑（限制）

3.1 原表上不能有触发器存在

这个很容易理解，pt-osc会在原表上创建3个触发器，而一个表上不能同时有2个相同类型的触发器，为简单通用起见，只能一棍子打死。
所以如果要让它支持有触发器存在的表也是可以实现的，思路就是：先找到原表触发器定义；重写原表触发器；最后阶段将原表触发器定义应用到新表。

3.2 通过触发器写数据到临时新表，会不会出现数据不一致或异常

这其实是我的一个顾虑，因为如果update t1，触发update t2，但这条数据还没copy到t2，不就有异常了吗？后台通过打开general_log，看到它创建的触发器：

1 2 3 4 5 6 7 8 9 10 11 12

6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` 6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_upd` AFTER UPDATE ON `confluence`.`sbtest3` FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) 6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_ins` AFTER INSERT ON `confluence`.`sbtest3` FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) 并且copy操作是： 6165 Query INSERT LOW_PRIORITY IGNORE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `confluence`.`sbtest3` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4692805')) AND ((`id` <= '4718680')) LOCK IN SHARE MODE /*pt-online-schema-change 46459 copy nibble*/

在原表上update，新临时表上是replace into整行数据，所以达到有则更新，无则插入。同时配合后面的 insert ignore，保证这条数据不会因为重复而失败。

3.3 为什么外键那么特殊

假设 t1 是要修改的表，t2 有外键依赖于 t1，_t1_new 是 alter t1 产生的新临时表。
这里的外键不是看t1上是否存在外键，而是作为子表的 t2。主要问题在 rename t1 时，t1“不存在”导致t2的外键认为参考失败，不允许rename。
pt-osc提供--alter-foreign-keys-method选项来决定怎么处理这种情况：

• rebuild_constraints，优先采用这种方式
  • 它先通过 alter table t2 drop fk1,add _fk1 重建外键参考，指向新表
  • 再 rename t1 t1_old, _t1_new t1 ，交换表名，不影响客户端
  • 删除旧表 t1_old
    但如果字表t2太大，以致alter操作可能耗时过长，有可能会强制选择 drop_swap。
    涉及的主要方法在 pt-online-schema-change 文件的 determine_alter_fk_method, rebuild_constraints, swap_tables三个函数中。
• drop_swap，
  • 禁用t2表外键约束检查 FOREIGN_KEY_CHECKS=0
  • 然后 drop t1 原表
  • 再 rename _t1_new t1
    这种方式速度更快，也不会阻塞请求。但有风险，第一，drop表的瞬间到rename过程，原表t1是不存在的，遇到请求会报错；第二，如果因为bug或某种原因，旧表已删，新表rename失败，那就太晚了，但这种情况很少见。
    我们的开发规范决定，即使表间存在外键参考关系，也不通过表定义强制约束。

3.4 在使用之前需要对磁盘容量进行评估

使用OSC会使增加一倍的空间，包括索引
而且在 Row Based Replication 下，还会写一份binlog。不要想当然使用--set-vars去设置 sql_log_bin=0，因为在这个session级别，alter语句也要在从库上执行，除非你对从库另有打算。

4. 使用 pt-osc原生 5.6 online ddl相比，如何选择

• online ddl在必须copy table时成本较高，不宜采用
• pt-osc工具在存在触发器时，不适用
• 修改索引、外键、列名时，优先采用online ddl，并指定 ALGORITHM=INPLACE
• 其它情况使用pt-osc，虽然存在copy data
• pt-osc比online ddl要慢一倍左右，因为它是根据负载调整的
• 无论哪种方式都选择的业务低峰期执行
• 特殊情况需要利用主从特性，先alter从库，主备切换，再改原主库

5.使用pt-osc修改主键时注意

使用 pt-online-schema-change 做在线ddl最添加普通索引、列，修改列类型、添加默认值等使用比较常规，但涉及到要修改的是主键时就有点棘手。在我修改线上实例过程中，有这样的需求，不妨先思考一下怎么做才好：

1	原表上有个复合主键，现在要添加一个自增id作为主键，如何进行

会涉及到以下修改动作：

1. 删除复合主键定义
2. 添加新的自增主键
3. 原复合主键字段，修改成唯一索引

如果你够聪明，应该会把这三个操作放在同一个 alter table 命令执行。percona手册里有两个地方对修改主键进行了特殊注解：

–alter
A notable exception is when a PRIMARY KEY or UNIQUE INDEX is being created from existing columns as part of the ALTER clause; in that case it will use these column(s) for the DELETE trigger.

–[no]check-alter

• DROP PRIMARY KEY
  If –alter contain DROP PRIMARY KEY (case- and space-insensitive), a warning is printed and the tool exits unless –dry-run is specified. Altering the primary key can be dangerous, but the tool can handle it. The tool’s triggers, particularly the DELETE trigger, are most affected by altering the primary key because the tool prefers to use the primary key for its triggers. You should first run the tool with –dry-run and –print and verify that the triggers are correct.

由上一篇文章 pt-online-schema-change使用说明、限制与比较 可知，pt-osc会在原表t1上创建 AFTER DELETE/UPDATE/INSERT 三个触发器，修改主键影响最大的就是 DELETE 触发器：新表t2上的主键字段在旧表t1上不存在，无法根据主键条件触发删除新表t2数据。but the tool can handle it，原因是pt-osc把触发器改成了下面的形式：

1 2 3 4

CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` AND `confluence`.`_sbtest3_new`.`k` <=> OLD.`k` 注：sbtest3表上以(id,k)作为复合主键

但是如果id或k列上没有索引，这个删除的代价非常高，所以一定要同时添加复合（唯一）索引 (id,k) .

而对于INSERT,UPDATE的触发器，依然是 REPLACE INTO语法，因为它采用的是先插入，如果违反主键或唯一约束，则根据主键或意义约束删除这条数据，再执行插入。（但是注意你不能依赖于新表的主键递增，因为如果原表有update，新表就会先插入这一条，导致id与原表记录所在顺序不一样）

所以如果使用pt-osc去修改删除主键，务必同时添加原主键为 UNIQUE KEY，否则很有可能导致性能问题：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

$ pt-online-schema-change --user=ecuser --password=ecuser --host=10.0.201.34 \ --alter "DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k)" \ D=confluence,t=sbtest3 --print --dry-run --alter contains 'DROP PRIMARY KEY'. Dropping and altering the primary key can be dangerous, especially if the original table does not have other unique indexes. ==>注意 dry-run的输出 ALTER TABLE `confluence`.`_sbtest3_new` DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k) Altered `confluence`.`_sbtest3_new` OK. Using original table index PRIMARY for the DELETE trigger instead of new table index PRIMARY because ==> 使用原表主键值判断 the new table index uses column pk which does not exist in the original table. CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` AND `confluence`.`_sbtest3_new`.`k` <=> OLD.`k`