原来 pt-osc 改表是这样实现的！原理详解【附场景案例】

pt-osc原理探索及其触发器的深入分析

作者：莫善，某互联网公司高级 DBA。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 6000 字，预计阅读需要 20 分钟。

背景

自工作以来就开始接触 pt-osc，至今差不多 8 年了，过去那么多次接触都只是停留在使用及简单了解原理，并没有对原理进行更深的思考，简而言之就是没有吃透其原理，后面使用改表平台以后，连编写 pt-osc 命令都省去了，感觉更加的生疏了，趁着这次机会就来梳理总结一下。

本文主要是梳理一下 pt-osc 的工作原理，以及对自己的一些疑惑进行解惑，最后是一点使用建议，仅供参考。

测试环境

• pt-osc 3.0.13
• MySQL 5.7.26

问题梳理

用过 pt-osc 改表的朋友都应该非常熟悉这工具的工作流程了，这里简单过一遍：

• 创建一张与原表结构一致的新表，然后对新表完成改表需求。
• 创建触发器。这时候增量数据就已经开始同步。
• 同步全量数据。遍历原表，通过 INSERT IGNORE INTO 将数据拷贝到新表。
• 等全量同步完毕后切表，清理触发器。

触发器	映射的 SQL 语句
INSERT 触发器	REPLACE INTO
UPDATE 触发器	DELETE IGNORE + REPLACE INTO
DELETE 触发器	DELETE IGNORE

关于这个流程，之前并没有太多的思考，也就是简单的记忆一下，但是前段时间给公司分享我们改表平台的时候有研发的同事问了我几个问题，有些我能解答，有些不能解答，这里就统一梳理出来供大家思考一下：

• 问题一：这里的触发器为什么要这么设计，能不能 INSERT 用 INSERT，UPDATE 用 UPDATE？
• 问题二：改表工单明明暂停了，但是集群负载不会降下来。
• 问题三：使用 pt-osc 为什么会产生死锁？
• 问题四：改表准备结束阶段，切表和删除触发器是串行执行，既存在先后顺序也没法保证原子性，会不会有问题？

上述问题的前三个问题是在分享时问我的。其中 问题一 在现场其实回答的不够好，对于 UPDATE 的触发器就给我问住了，后来通过查资料及测试才搞明白，然后 问题四 是我在后面查资料及测试的时候出现的一个疑问。

下面就逐一来看看这几个问题。

问题解惑

通过 binlog 来抓取一下 pt-osc 的一次完整改表操作。

准备一个测试表

写入两条测试数据，表结构定义如下：

(dba:3306)@[dbzz_new_dbversion1]>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'pk',
  `name` char(4) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
+----+------+
2 rows in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>

改表需求是用 pt-osc 将 name 字段的长度 char(4) 改为 char(10)。

pt-online-schema-change --alter=" modify column name char(10) not null default '' comment 'name'; " --chunk-size=2000 --max-lag=31 --check-interval=5 --no-check-replication-filters --check-alter --max-load='Threads_running=64' --critical-load='Threads_running=4000' --recurse=0 --ask-pass --execute --pause-file=/opt/soft/zzonlineddl/logs/mark/dd2fb8319b658bb43fb1e579ed2fc7fd_pause_6098 --print --check-slave-lag=192.168.168.1.2:3306 -p123456 h=192.168.1.1,P=3306,u=inception,D=dbzz_new_dbversion1,t=t1,A=utf8

执行完上述的 pt-osc 命令后，将 binlog 解析出来可以得到下面的操作步骤：

为了简洁，所有操作仅保留 SQL，其他信息已经去掉了。

1. 创建一张新表，表结构与旧表相同

CREATE TABLE `dbzz_new_dbversion1`.`_t1_new` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'pk',
  `name` char(4) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

2. 对新表执行改表需求

ALTER TABLE `dbzz_new_dbversion1`.`_t1_new`  modify column name char(10) not null default '' comment 'name'

3. 创建触发器

CREATE DEFINER=`inception`@`192.168.%.%` TRIGGER `pt_osc_dbzz_new_dbversion1_t1_del` AFTER DELETE ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbzz_new_dbversion1`.`_t1_new` WHERE `dbzz_new_dbversion1`.`_t1_new`.`id` <=> OLD.`id`

CREATE DEFINER=`inception`@`192.168.%.%` TRIGGER `pt_osc_dbzz_new_dbversion1_t1_upd` AFTER UPDATE ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW BEGIN DELETE IGNORE FROM `dbzz_new_dbversion1`.`_t1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `dbzz_new_dbversion1`.`_t1_new`.`id` <=> OLD.`id`;REPLACE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`);END

CREATE DEFINER=`inception`@`192.168.%.%` TRIGGER `pt_osc_dbzz_new_dbversion1_t1_ins` AFTER INSERT ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW REPLACE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

这里的触发器为什么要这么设计？其实很值得我们去细想，想明白以后会发现真的很巧妙。

DELETE 触发器

没啥好介绍的，就跳过吧。

INSERT 触发器

这里为什么要替换成 REPLACE 操作？如果用 INSERT 操作会怎么样？答案是数据可能不一致。

如果业务使用 INSERT 写入记录，这种场景是没问题的，正常触发该触发器。但是如果业务是通过 REPLACE 操作，这种场景也是会触发 INSERT 触发器，那在原表执行 REPLACE 操作，新表执行 INSERT 操作。如果目标记录不存在则没问题，但是目标记录存在的话就会导致数据不一致，即原表的记录被更新了，新表的记录还是原来的记录。所以说直接使用 INSERT 操作是存在数据不一致的风险的。

只有当业务的 REPLACE 的目标记录存在，且这条记录已经被拷贝到新表，这种场景才会导致数据不一致，如果该记录还没拷贝到新表也是没问题的。

UPDATE 触发器

我们都知道这个会先 DELETE，然后 REPLACE，那为什么要这么设计？能不能直接使用 UPDATE，或者说能不能不 DELETE，直接 REPLACE，又或者说其他方案？

关于这个触发器我们可以好好讨论一下。

我们先来讨论一下，为什么不能直接使用 UPDATE 操作？

毕竟 gh-ost 在回放 UPDATE 操作的时候是直接使用 UPDATE，而不是替换成其他操作。如果 pt-osc 也直接使用 UPDATE 会有什么问题？答案是可能会导致数据丢失。

下面我们来测试验证一下：

drop table if exists t1;
drop table if exists _t1_new;
create table t1 (id int not null auto_increment primary key comment 'pk',name char(4) not null default '' comment 'name');
create table _t1_new like t1;

insert into t1 values(1,1),(10,10);
insert into _t1_new values(1,1);
delimiter //

CREATE TRIGGER `pt_osc_dbzz_new_dbversion1_t1_upd` AFTER UPDATE ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW BEGIN UPDATE `dbzz_new_dbversion1`.`_t1_new`  set `id` = NEW.`id`, `name` = NEW.`NAME` where `id` = OLD.`id` and `name` = OLD.`NAME`;END //

delimiter ;

CREATE TRIGGER `pt_osc_dbzz_new_dbversion1_t1_ins` AFTER INSERT ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW REPLACE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`);

CREATE TRIGGER `pt_osc_dbzz_new_dbversion1_t1_del` AFTER DELETE ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbzz_new_dbversion1`.`_t1_new` WHERE `dbzz_new_dbversion1`.`_t1_new`.`id` <=> OLD.`id`;

准备了两个测试表，来模拟改表。

• t1 写入两条记录 (1,1),(10,10)。
• _t1_new 写入一条记录(1,1)。
• UPDATE 触发器是直接用 UPDATE 来操作。

假设拷贝全量数据的任务拷贝到 id=9 的记录，id=10 还没拷贝到新表。

这时候如果在原表执行了一条 UPDATE t1 SET id = 2 , name = 2 WHERE id = 10 结果会怎么样？

(dba:3306)@[dbzz_new_dbversion1]>select * from _t1_new;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
+----+------+
1 row in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
| 10 | 10   |
+----+------+
2 rows in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>update t1 set id = 2 , name = 2 where id = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(dba:3306)@[dbzz_new_dbversion1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
+----+------+
2 rows in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>select * from _t1_new;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
+----+------+
1 row in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>

我们测试的有个前提假设，那就是在执行 UPDATE 的时候，目标记录还没有被同步到新表，且 UPDATE 后的 id 要小于当前正在拷贝的 id。可以看到测试结果是 id=2 这条记录就会被丢失，因为 id=2 这条记录不会被同步到新表。

通过上面的测试我们知道，UPDATE 触发器 不能使用 UPDATE 操作来完成。

那么问题又来了，gh-ost 的 UPDATE 操作是直接使用 UPDATE 进行回放，为什么没问题，按说采用上面的测试用例也应该是数据不一致的。关于这个问题我也做了测试，但是由于 gh-ost 工具的输出非常多，这里就不贴输出结果了，有兴趣的朋友可以自行测试一下，这里直接提供测试结果。

从测试结果看，其实gh-ost 并不是完全的使用 UPDATE 操作进行回放，应该是还有其他线程在监听 binlog，如果当前的 UPDATE 是修改主键或者唯一键，且是从大改到小，且修改后的主键或者唯一键小于当前正在拷贝的数据块的值，这时候就会执行一条 REPLACE 操作，如果不满足条件就不做处理。

下面我们再来讨论第二个疑问。

既然已经删除了这里为什么还要使用 REPLACE 而不是 INSERT？

关于这一点我没想明白，我认为 DELETE + REPLACE 和 DELETE + INSERT 是等效的。那为什么要先删除再 REPLACE？这是为了避免因为修改唯一键或者主键的场景，如果没有 DELETE，那就会在新表多出记录。比如将主键等于1改成主键等于 2，在原表只有一条记录，但是新表会有两条记录，所以要先 DELETE 再 REPLACE。

同样我们通过测试来验证一下为什么要先 DELETE 再 REPLACE，不能直接 REPLACE 操作：

drop table if exists t1;
drop table if exists _t1_new;
create table t1 (id int not null auto_increment primary key comment 'pk',name char(4) not null default '' comment 'name');
create table _t1_new like t1;

DROP TRIGGER `pt_osc_dbzz_new_dbversion1_t1_upd`;
DROP TRIGGER `pt_osc_dbzz_new_dbversion1_t1_ins`;
DROP TRIGGER `pt_osc_dbzz_new_dbversion1_t1_del`;

insert into t1 values(1,1),(9,9);
insert into _t1_new values(1,1);
delimiter //

CREATE TRIGGER `pt_osc_dbzz_new_dbversion1_t1_upd` AFTER UPDATE ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW BEGIN REPLACE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (OLD.`id`, OLD.`name`);END //

delimiter ;

CREATE TRIGGER `pt_osc_dbzz_new_dbversion1_t1_ins` AFTER INSERT ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW REPLACE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`);

CREATE TRIGGER `pt_osc_dbzz_new_dbversion1_t1_del` AFTER DELETE ON `dbzz_new_dbversion1`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbzz_new_dbversion1`.`_t1_new` WHERE `dbzz_new_dbversion1`.`_t1_new`.`id` <=> OLD.`id`;

准备了两个测试表，来模拟改表。

• t1 写入两条记录 (1,1),(9,9)。
• _t1_new 写入一条记录(1,1)。
• UPDATE 触发器是直接用 REPLACE 来操作。

假设拷贝全量数据的任务拷贝到 id=8 的记录，即 id=9 这条记录还没有拷贝到新表。

这时候如果在原表执行了一条 INSERT INTO t1(id,name) VALUE(9,9) ON DUPLICATE KEY UPDATE id = 10, name = 10; 结果会怎么样？

INSERT INTO ON DUPLICATE KEY UPDATE 这种操作在目标记录存在时会触发 UPDATE 触发器。

(dba:3306)@[dbzz_new_dbversion1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  9 | 9    |
+----+------+
2 rows in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>select * from _t1_new;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
+----+------+
1 row in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>insert into t1(id,name) value(9,9) ON DUPLICATE KEY UPDATE id=10, name = 10;
Query OK, 2 rows affected (0.01 sec)

(dba:3306)@[dbzz_new_dbversion1]>select * from _t1_new;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  9 | 9    |
+----+------+
2 rows in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
| 10 | 10   |
+----+------+
2 rows in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>

我们可以看到测试结果是在 _t1_new 表中写进了 (9,9) 这条记录，而不是预期内的 (10,10)。那么问题就来了，此时全量拷贝任务的进度是拷贝到 id = 8，那么后面 t1 表的 id = 10 这条记录也会被拷贝到新表。所以最终 _t1_new 的记录会比 t1 多一条记录 (9,9)，最终数据不一致。

通过上面的测试我们知道，UPDATE 触发器 不能直接使用 REPLACE 操作来完成。

我们再来思考一下，UPDATE 触发器 还有没有其他可以实现数据最终一致的方案？

经过测试验证发现，其实 INSERT IGNORE + UPDATE，或者 DELETE + INSERT，都是可以实现最终数据一致。那么 pt-osc 官方为什么会选择 DELETE + REPLACE 呢？

这点我猜测可能是因为出于成本考虑，毕竟 INSERT 可能会多占用一次 IO。

有兴趣的小伙伴可以自行测试一下。不过需要注意一下，这里 INSERT IGNORE + UPDATE 里面的 INSERT 是 OLD.ID 的数据。DELETE + REPLACE 或者 DELETE + INSERT 是 NEW.ID 的数据。

那么 DELETE + REPLACE 和 DELETE + INSERT 为什么选择前者呢？这两个操作放在一个事务里面的话，最终 binlog 记录的是一样的，所以我暂时想不通为什么选择前者。

下面我们做一个测试，看看 DELETE + REPLACE 和 DELETE + INSERT 是不是等效？

首先准备一些测试数据：

drop table if exists t1;
create table t1 (id int not null auto_increment primary key comment 'pk',name char(4) not null default '' comment 'name');
insert into t1 values(1,1),(2,2);
set binlog_rows_query_log_events = 1;
flush binary logs;

这里要开启 binlog_rows_query_log_events 参数，目的是为了记录原始语句。

现在做如下测试，然后查看 binlog 的记录。

begin;
delete from t1 where id = 1 and name = 1;
insert into t1 value(1,1);
commit;

begin;
delete from t1 where id = 1 and name = 1;
replace into t1 value(1,1);
commit;

会发现解析 binlog 后的记录是下面这样:

BEGIN
# delete from t1 where id = 1 and name = 1

### DELETE FROM `dbzz_new_dbversion1`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1' /* STRING(16) meta=65040 nullable=0 is_null=0 */

# insert into t1 value(1,1)

### INSERT INTO `dbzz_new_dbversion1`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1' /* STRING(16) meta=65040 nullable=0 is_null=0 */
COMMIT/*!*/;

BEGIN
# delete from t1 where id = 1 and name = 1

### DELETE FROM `dbzz_new_dbversion1`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1' /* STRING(16) meta=65040 nullable=0 is_null=0 */

# replace into t1 value(1,1)

### INSERT INTO `dbzz_new_dbversion1`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1' /* STRING(16) meta=65040 nullable=0 is_null=0 */
COMMIT/*!*/;

• 第一个事务：原始语句是 DELETE + INSERT，实际执行的也是 DELETE + INSERT。
• 第二个事务：原始语句是 DELETE + REPLACE，但实际执行的是 DELETE + INSERT。

所以说同一个事务内，DELETE + REPLACE 和 DELETE + INSERT 应该是等效的。

4. 拷贝全量数据

# INSERT LOW_PRIORITY IGNORE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbzz_new_dbversion1`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 10363 copy table*/

### INSERT INTO `dbzz_new_dbversion1`.`_t1_new`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1' /* STRING(40) meta=65064 nullable=0 is_null=0 */
### INSERT INTO `dbzz_new_dbversion1`.`_t1_new`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2' /* STRING(40) meta=65064 nullable=0 is_null=0 */

这个操作需要解释一下，因为测试表数据较少，如果目标表数据行很多，超过 chunk-size 的大小，就会根据 主键（非空唯一键） 去遍历，每一次是按 chunk-size的大小。

另外这里可能会有疑问，pt-osc 采用 LOW_PRIORITY 用法来降低 INSERT 操作的优先级，在读密集型的系统可能比较友好，但是 InnoDB 是行级锁，能更好的控制读写并发问题，所以不要被迷惑了这里的限制是不生效，这里可能仅仅是为了兼容非 InnoDB 引擎。

下面这段话是官方文档的描述，说的是仅表级锁的存储引擎有效。

If you use the LOW_PRIORITY modifier, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting. It is possible, therefore, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time.

LOW_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).

另外，用过 pt-osc 的朋友应该都了解，在并发比较高的环境下改表，很容易产生死锁，在这里我们就简单分析一下死锁问题。

时间	会话1	会话2
t1	BEGIN; UPDATE old WHERE id = 5;
t2		INSERT LOW_PRIORITY IGNORE INTO new SELETE * FROM old WHERE id > 1 and id < 10 LOCK IN SHARE MODE;
t3	REPLACE INTO new xxx; ROLLBACK;

通过上面的表我们可以知道：

• t1 时刻，因为 old 表有触发器，所以对 old 表进行 UPDATE 会起一个事务，这时候 会话1 执行 UPDATE 操作会持有 old 表的 X 锁。
• t2 时刻，拷贝全量数据的任务正好包含业务需要更新的记录，这时候 会话2 持有 new 表的自增锁，但是需要等待 old 的 S 锁。
• t3 时刻，UPDATE 触发器 被触发，REPLACE 操作需要等待 new 的自增锁，死锁形成，MySQL 认为 会话1 事务开销小，所以被回滚，对业务来说很不友好。

我们可以发现，产生死锁是需要两个条件：业务 UPDATE SQL 执行结束后到触发器的 REPLACE 被触发之前，正在开启下一次 拷贝全量数据任务，且业务更新的记录正好在 拷贝全量数据任务 的拷贝的区间。

由此可见，我们通过改小 chunk-size 降低死锁被触发的概率，但是无法完全避免。

5. 更新统计信息

ANALYZE TABLE `dbzz_new_dbversion1`.`_t1_new` /* pt-online-schema-change */

不知道大家有没有疑问，为什么要做这个操作？

看起来这个操作是强制更新统计信息，其目的就是为了帮助优化器更好的选择执行计划。如果不做这个操作，改表结束以后，新表被业务流量访问，可能会因为统计信息不准，导致业务的查询选择了错误的执行计划出现大量的慢查询，进而影响到业务。

但是我们都知道，当目标表的数据行发生重大变化后统计信息一般都是自动重新计算，阈值是表中行数的 10%（默认情况下），所以这个操作看起来又有点多余了。

gh-ost 切表后没有这个操作。

还有一点，如果是大表，这个操作会不会阻塞原表的 DML？

(dba:3306)@[dbzz_new_dbversion1]>select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  4999999 |
+----------+
1 row in set (0.16 sec)

(dba:3306)@[dbzz_new_dbversion1]>ANALYZE TABLE sbtest1;
+-----------------------------+---------+----------+----------+
| Table                       | Op      | Msg_type | Msg_text |
+-----------------------------+---------+----------+----------+
| dbzz_new_dbversion1.sbtest1 | analyze | status   | OK       |
+-----------------------------+---------+----------+----------+
1 row in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>

经过测试发现，对一个近 500w 行的表做这个操作很快就返回结果了，最后也查了官方文档 确认是不会阻塞 DML。

ANALYZE TABLE removes the table from the table definition cache, which requires a flush lock. If there are long running statements or transactions still using the table, subsequent statements and transactions must wait for those operations to finish before the flush lock is released. Because ANALYZE TABLE itself typically finishes quickly, it may not be apparent that delayed transactions or statements involving the same table are due to the remaining flush lock. 

6. 切表

RENAME TABLE `dbzz_new_dbversion1`.`t1` TO `dbzz_new_dbversion1`.`_t1_old`, `dbzz_new_dbversion1`.`_t1_new` TO `dbzz_new_dbversion1`.`t1`

关于这一个操作，我原来是有一个很大的疑问的，也就是上面描述的 问题四。

首先，t1 表是存在三个触发器，切表后 _t1_new 表已经不存在。这时候切表后如果对 t1 做 DML 操作，触发器工作应该会出现异常，最终导致 t1 的 DML 操作失败。

但是我们都知道，实际上并不会出现这个问题。我就在想，是不是因为切表跟删除触发器之间隔的时间太短，且每个操作都是很快才没有触发这个问题的。后来经过测试发现并不是，真正的原因是 rename 的时候触发器也一并切走，即原表的触发器会变成 _t1_old 这个表的触发器。

(dba:3306)@[dbzz_new_dbversion1]>show triggers;
+-----------------------------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+
| Trigger                           | Event  | Table | Statement                                                                                                                                                                                                                                | Timing | Created                | sql_mode                                                                                                              | Definer  | character_set_client | collation_connection | Database Collation |
+-----------------------------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+
| pt_osc_dbzz_new_dbversion1_t1_ins | INSERT | t1    | REPLACE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)                                                                                                                                                | AFTER  | 2024-05-11 11:56:08.21 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | dba@10.% | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_general_ci |
| pt_osc_dbzz_new_dbversion1_t1_upd | UPDATE | t1    | BEGIN INSERT IGNORE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (OLD.`id`, OLD.`name`);UPDATE `dbzz_new_dbversion1`.`_t1_new`  set `id` = NEW.`id`, `name` = NEW.`NAME` where `id` = OLD.`id` and `name` = OLD.`NAME`;END | AFTER  | 2024-05-11 11:56:08.20 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | dba@10.% | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_general_ci |
| pt_osc_dbzz_new_dbversion1_t1_del | DELETE | t1    | DELETE IGNORE FROM `dbzz_new_dbversion1`.`_t1_new` WHERE `dbzz_new_dbversion1`.`_t1_new`.`id` <=> OLD.`id`                                                                                                                               | AFTER  | 2024-05-11 11:56:08.21 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | dba@10.% | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_general_ci |
+-----------------------------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+
3 rows in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>rename table t1 to _t1_old,_t1_new to t1;
Query OK, 0 rows affected (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>show triggers;
+-----------------------------------+--------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+
| Trigger                           | Event  | Table   | Statement                                                                                                                                                                                                                                | Timing | Created                | sql_mode                                                                                                              | Definer  | character_set_client | collation_connection | Database Collation |
+-----------------------------------+--------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+
| pt_osc_dbzz_new_dbversion1_t1_ins | INSERT | _t1_old | REPLACE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)                                                                                                                                                | AFTER  | 2024-05-11 11:56:08.21 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | dba@10.% | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_general_ci |
| pt_osc_dbzz_new_dbversion1_t1_upd | UPDATE | _t1_old | BEGIN INSERT IGNORE INTO `dbzz_new_dbversion1`.`_t1_new` (`id`, `name`) VALUES (OLD.`id`, OLD.`name`);UPDATE `dbzz_new_dbversion1`.`_t1_new`  set `id` = NEW.`id`, `name` = NEW.`NAME` where `id` = OLD.`id` and `name` = OLD.`NAME`;END | AFTER  | 2024-05-11 11:56:08.20 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | dba@10.% | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_general_ci |
| pt_osc_dbzz_new_dbversion1_t1_del | DELETE | _t1_old | DELETE IGNORE FROM `dbzz_new_dbversion1`.`_t1_new` WHERE `dbzz_new_dbversion1`.`_t1_new`.`id` <=> OLD.`id`                                                                                                                               | AFTER  | 2024-05-11 11:56:08.21 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | dba@10.% | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_general_ci |
+-----------------------------------+--------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+
3 rows in set (0.00 sec)

(dba:3306)@[dbzz_new_dbversion1]>

下面一段是官方文档 的描述，虽然没有明确写到 rename 会切触发器，但是侧面反应了 rename 操作其实是会连带触发器一起切走。

If a table has triggers, attempts to rename the table into a different database fail with a Trigger in wrong schema (ER_TRG_IN_WRONG_SCHEMA) error.

(dba:4202)@[dbzz_new_dbversion1]>rename table t1 to 58monitor.t1;
ERROR 1435 (HY000): Trigger in wrong schema
(dba:4202)@[dbzz_new_dbversion1]>

如果有修改库名的需求要十分注意，要提前检查好是否存在触发器，自定义函数，存储过程。

7. 删除旧表

DROP TABLE IF EXISTS `_t1_old` /* generated by server */

根据需求使用该功能，对于可能存在改表风险的场景，建议可以先保留 old 表。

8. 删除触发器

DROP TRIGGER IF EXISTS `dbzz_new_dbversion1`.`pt_osc_dbzz_new_dbversion1_t1_del`

DROP TRIGGER IF EXISTS `dbzz_new_dbversion1`.`pt_osc_dbzz_new_dbversion1_t1_upd`

DROP TRIGGER IF EXISTS `dbzz_new_dbversion1`.`pt_osc_dbzz_new_dbversion1_t1_ins`

至此一次完整的改表结束。

总结

经过上面的梳理分析，我们知道 pt-osc 在改表过程中做了很多事情，现在我们做个简单的总结，主要是想梳理一下这个工具的缺点，毕竟使用这个工具的朋友应该都知道这工具很优秀，很好用。但是往往缺点容易被忽视，希望能帮大家提供一些思路以及规避一些问题。

• 可能出现死锁，这个问题上面也分析了，这里就不过多介绍了。
• 触发器会导致业务的dml流量翻倍放大，对负载比较大或者并发比较高的环境可能存在很大的风险，这点需要提前跟业务沟通好，做好风险评估。
• 支持暂停，但是功能有限。我们知道增量是通过触发器去完成，所以暂停只能暂停拷贝全量数据的任务，对于触发器产生的流量没法暂停。
• 需要多次获取元数据锁。创建触发器、删除触发器、更新统计信息以及最后的切表都需要获取元数据锁。虽然元数据锁不会阻塞dml，但是获取元数据锁被阻塞，目标表后续的读写都会被阻塞，如果多次获取元数据锁无疑会加大了目标表被阻塞的风险。

以上问题可能还会互相影响，比如当前数据库正在使用 pt-osc 进行改表，这时候如果负载上来了，就可能出现大量慢查询。这种情况下要么暂停改表，要么终止改表。

• 暂停改表。对于业务的dml流量还是会继续触发触发器，可能不会缓解压力。
• 终止改表。终止会删除触发器，这时候系统又有大量慢查询，删除触发器可能会被阻塞，进而阻塞目标表的读写操作。

所以对于这种场景，进行干预操作的时候一定要十分谨慎，最好是改表前就准备好操作文档。

最后分享一下我们在改表中遇到的三个案例，也算是使用建议吧。

案例

我们的改表平台在改表上的方案是：满足 Only Modifies Metadata 的就优先使用原生的改表命令进行改表，其次出于风险考虑会优先使用 gh-ost*，对于没法使用 *gh-ost 的场景才会使用 pt-osc。

虽然 pt-osc 是被放在最后才会选择的方案，但是在使用中会发现很多场景使用 gh-ost 并不适用，或者说时间成本更高，对于部分场景在确保稳定及安全的前提下是可以使用 pt-osc 进行的，比如下面的 案例一 和 案例二。

案例一

执行改表命令所在的机器负载很高，使用 gh-ost 就会触发 throttled，然后一直卡着，直到负载降下来了才会继续。所以后来我们加了一个逻辑，如果在改表过程中，执行改表命令所在的机器负载很高就使用 pt-osc 进行。

我们在 DBA 管理机执行改表命令，管理机会有些跑批任务，可能会把机器负载拉起来。

案例二

对于定时改表工单，然后定的是凌晨 2-6 点执行，如果目标表很大，那么执行时间之外的时间就会被暂停，然后第二天这个时间会继续。然后这可能会出现一个问题，这个工单会执行很多天，甚至永远不会完成。

原因是 gh-ost 会优先完成增量数据部分。所以在暂停期间堆积了大量未应用 binlog*，只有当堆积的 *binlog 应用完了才会继续拷贝全量数据。所以这种定时工单场景建议使用 pt-osc 来完成。

案例三

对于添加唯一索引的工单，如果目标表本身存在重复数据，改表后会出现数据丢失，所以需要十分注意。建议关闭 pt-osc 添加唯一索引的功能，使用 gh-ost + hook 的方式添加唯一索引。

以上，仅供参考。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle