专栏首页杨建荣的学习笔记MySQL中使用pt-osc的一些小结

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

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-06-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 海量数据迁移之使用shell启用多个动态并行(r2笔记81天)

    在数据迁移中,可能有成百上千个表,有些表很大,有些表又很小。 如果启用了多个并行的进程,可能会有资源分配上的问题。 比如下面有10个表,100代表预计的时间为1...

    jeanron100
  • 由drop datafile导致的oracle bug(r6笔记第56天)

    今天碰到了一个dataguard在10gR2的bug,不管怎么样确实是在特定的时间做了特定的操作结果碰到了特定的问题。 这个问题是在10gR2的版本10.2.0...

    jeanron100
  • MySQL误操作数据恢复的简单实践(r11笔记第67天)

    前几天有个同事碰到了一个MySQL数据恢复的问题,他运行了一条update语句,结果忘记了加where条件,结果等反应过来已经晚了。我简单确认了下,是否...

    jeanron100
  • 使用webhooks自动部署项目

    目前大部分git托管服务都提供了webhooks功能。 那么,什么是webhooks呢?

    zhangheng
  • Python -- str 类

    def capitalize(self):   # 全部字母变小写只有首字母变大写;

    py3study
  • Arthas - Java 线上问题定位处理的终极利器

    在使用 Arthas 之前,当遇到 Java 线上问题时,如 CPU 飙升、负载突高、内存溢出等问题,你需要查命令,查网络,然后 jps、jstack、jmap...

    未读代码
  • Nginx用户认证与域名重定向

    如果再次添加用户的话就不需要加上-c选项了,加上-c选项会覆盖原来的htpasswd 文件。

    端碗吹水
  • 2 手写实现SpringMVC,第二节:自定义注解及反射赋值

    可以发现,这里面使用了大量的自定义注解,并且还有autuwire的属性也需要被赋值(Spring的IOC功能)。

    天涯泪小武
  • 校招大幕已开启!整理一堆面经助你一臂之力!

    昨天相继看到腾讯、Oppo等公司的校招提前批已经开始,你们是否感受到了压力呢?没关系,小编今天给你整理一下咱公众号和其他公众号发布的一些有关校招面经的帖子,助你...

    石晓文
  • 手把手教你从零开始搭建SpringBoot后端项目框架

    打开IDE,点击File -> New Project。在左侧的列表中的选择Maven项目,点击Next。

    SH的全栈笔记

扫码关注云+社区

领取腾讯云代金券