MySQL中使用pt-osc的一些小结

Percona的pt-osc工具算是DBA的一个福利工具。想想一个数据量有些大的表，在上面做DDL操作真是一种煎熬，我们也基本理解了这是一种空间换时间的策略，尽可能保证一些准备和同步工作能够离线进行，而正式的切换是一个最小粒度的rename操作。

但是这样一个很柔性的操作，其实有一些问题还需要我们更深层次的分析和理解，否则我们使用pt-osc就是一个执行者而已，还没有掌握这种思路的核心。

比如有一个表newtest，我们需要给它加上一个索引，可以使用pt-osc的dry-run选项和print组合来得到执行的一些细节信息。

DDL语句类似这样：

alter table newtest add index idx_newtest_name(name)，使用pt-online-schema-change，命令如下：

[root@localhost bin]# ./pt-online-schema-change --host=127.0.0.1 -u pt_osc -p xxxx -P3306 --alter='add index idx_newtest_name(name)' --print D=test,t=newtest --dry-run

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. `test`.`newtest` will not be altered. Specify --execute instead of --dry-run to alter the table.

Creating new table...

CREATE TABLE `test`.`_newtest_new` (

`id` int(11) NOT NULL,

`name` varchar(30) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Created new table test._newtest_new OK.

Altering new table...

ALTER TABLE `test`.`_newtest_new` add index idx_newtest_name(name)

Altered `test`.`_newtest_new` OK.

Not creating triggers because this is a dry run.

Not copying rows because this is a dry run.

INSERT LOW_PRIORITY IGNORE INTO `test`.`_newtest_new` (`id`, `name`) SELECT `id`, `name` FROM `test`.`newtest` LOCK IN SHARE MODE /*pt-online-schema-change 4358 copy table*/

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 `test`.`pt_osc_test_newtest_del`

DROP TRIGGER IF EXISTS `test`.`pt_osc_test_newtest_upd`

DROP TRIGGER IF EXISTS `test`.`pt_osc_test_newtest_ins`

2018-06-24T23:30:52 Dropping new table...

DROP TABLE IF EXISTS `test`.`_newtest_new`;

2018-06-24T23:30:52 Dropped new table OK.

Dry run complete. `test`.`newtest` was not altered.

通过这种方式我们可以很清晰的看到一个变更的思路，是创建一个影子表_newtest_new,然后新的DDL变更部署在这个上面，因为这个时候表里还没有数据，所以这个过程很快。

接下来会在原表上添加三个触发器，然后开始数据的复制，基本原理就是insert into _newtest_new select *from newtest这种形式。

然后数据复制完成之后，开启rename模式，这个过程会分为两个步骤，把表newtest改名为一个别名 _newtest_old,同时把_newtest_new修改为newtest

最后来清理战场，删除原来的旧表，删除原来的触发器。

这个过程我相信做过pt-osc的同学，简单看下日志也能够明白这个原理和过程，但是显然上面的信息是很粗略的，而且有些信息是经不起推敲的。我们需要了解更深层次的细节来看看触发器的方式是否可行。

如果用触发器的方式可以直接变更，我们直接手工触发整个变更是否可行，有什么瓶颈？带着这个问题我们来逐个分析一下。

首先创建的三个触发器，delete,insert,update他们是怎么把增量数据写入到新表中的。因为新表的数据复制是一个离线的过程，目标是insert,delete,update操作不应该被阻塞，我们来逐个分析一下。打开代码来看一下：

先来看一下insert trigger,整个过程的思路就是replace into，如果在数据复制期间，有insert请求进来，那么replace into就类似于insert,如果复制流程已经完成，那么insert请求进来，就会是一个replace into实现的类似update的过程。

my $insert_trigger

= "CREATE TRIGGER `${prefix}_ins` AFTER INSERT ON $orig_tbl->{name} "

. "FOR EACH ROW "

. "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals)";

而update trigger的作用和上面的类似，如果数据复制的还没有完成，那么也会转换为一个replace into的insert 操作，如果复制已经完成，那么就会是一个update操作。这里需要注意的一点是，如果复制还没有完成的时候，处理update请求，我们直接insert，那么稍后表里就会生成两条记录，显然这是不合理的（实际上确实不可行），所以我们需要保证一个delete操作能够避免这种尴尬的数据冲突出现。

my $update_trigger

= "CREATE TRIGGER `${prefix}_upd` AFTER UPDATE ON $orig_tbl->{name} "

. "FOR EACH ROW "

. "BEGIN "

. "DELETE IGNORE FROM $new_tbl->{name} WHERE !($upd_index_cols) AND $del_index_cols;"

. "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals);"

. "END ";

然后就是delete操作，这个过程相比前面的过程会略微简单一些，使用了delete ignore的方式，基本能够杜绝潜在的性能问题。

my $delete_trigger

= "CREATE TRIGGER `${prefix}_del` AFTER DELETE ON $orig_tbl->{name} "

. "FOR EACH ROW "

. "DELETE IGNORE FROM $new_tbl->{name} "

. "WHERE $del_index_cols";

所以如此看来触发器的过程是一系列隐式的操作组成，但是实际上这个表很大的情况下，这个操作的代价就很高了。如果存在1000万数据，整个阻塞的过程会把这个时间无限拉长，显然也不合理，所以这里做到了小步快走的方式，把一个表的数据拆分成多份，也叫chunk，然后逐个击破。这样一来数据做了切分，粒度小了，阻塞的影响也会大大降低。

所以pt-osc工具实现了一个切分的思路，这个是原本的触发器不可替代的。整个数据的复制中增量DML的replace into处理很巧妙，加上数据的粒度拆分，让这个事情变得可控可用。

当然实际的pt-osc工具的逻辑远比这个复杂，里面考虑了很多额外的因素，比如对于外键，或者是表中的约束的信息等。

最后来一个基本完整的变更日志。

[root@localhost bin]# ./pt-online-schema-change --host=127.0.0.1 -u pt_osc -p pt_osc -P33091 --alter='add index idx_newtest_name(name)' --print D=test,t=newtest --execute

No slaves found. See --recursion-method if host localhost.localdomain has slaves.

Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

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`.`newtest`...

Creating new table...

CREATE TABLE `test`.`_newtest_new` (

`id` int(11) NOT NULL,

`name` varchar(30) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Created new table test._newtest_new OK.

Altering new table...

ALTER TABLE `test`.`_newtest_new` add index idx_newtest_name(name)

Altered `test`.`_newtest_new` OK.

2018-06-24T23:35:54 Creating triggers...

2018-06-24T23:35:54 Created triggers OK.

2018-06-24T23:35:54 Copying approximately 4 rows...

INSERT LOW_PRIORITY IGNORE INTO `test`.`_newtest_new` (`id`, `name`) SELECT `id`, `name` FROM `test`.`newtest` LOCK IN SHARE MODE /*pt-online-schema-change 4424 copy table*/

2018-06-24T23:35:54 Copied rows OK.

2018-06-24T23:35:54 Analyzing new table...

2018-06-24T23:35:54 Swapping tables...

RENAME TABLE `test`.`newtest` TO `test`.`_newtest_old`, `test`.`_newtest_new` TO `test`.`newtest`

2018-06-24T23:35:54 Swapped original and new tables OK.

2018-06-24T23:35:54 Dropping old table...

DROP TABLE IF EXISTS `test`.`_newtest_old`

2018-06-24T23:35:54 Dropped old table `test`.`_newtest_old` OK.

2018-06-24T23:35:54 Dropping triggers...

DROP TRIGGER IF EXISTS `test`.`pt_osc_test_newtest_del`

DROP TRIGGER IF EXISTS `test`.`pt_osc_test_newtest_upd`

DROP TRIGGER IF EXISTS `test`.`pt_osc_test_newtest_ins`

2018-06-24T23:35:54 Dropped triggers OK.

Successfully altered `test`.`newtest`.