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)

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏恰童鞋骚年

Hadoop学习笔记—20.网站日志分析项目案例(三)统计分析

  为了能够借助Hive进行统计分析,首先我们需要将清洗后的数据存入Hive中,那么我们需要先建立一张表。这里我们选择分区表,以日期作为分区的指标,建表语句如下...

1382
来自专栏携程技术中心

干货 | 一个MySQL 5.7 分区表性能下降的案例分析

作者简介 姜宇祥,2012年加入携程,10年数据库核心代码开发经验,相关开发涉及达梦,MySQL数据库。现致力于携程MySQL的底层研发,为特殊问题定位和处理提...

4907
来自专栏MYSQL轻松学

MySQL 面试选择题15道(单选)

1、MySQL数据库四种特性,不包括() A.原子性 B.事务性 C.一致性 D.隔离性 2、MySQL报错error 1062 的意思是() A.连接数据库失...

6876
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1.7K2
来自专栏Hadoop实操

如何使用Phoenix在CDH的HBase中创建二级索引

3482
来自专栏杨建荣的学习笔记

MySQL中的derived table(r12笔记第47天)

初始MySQL中的derived table还是在一个偶然的问题场景中。 下面的语句在执行的时候抛出了错误。 UPDATE payment_data rr ...

4005
来自专栏帘卷西风的专栏

MySQL存储过程where条件执行失败的问题

      前几天对服务器实体做了属性缓存机制,当时测试也没有出现大的问题,昨天有人跟我说,登陆的时候角色等级显示错误,我复测了一下,发现不只是等级错误,进入...

882
来自专栏从ORACLE起航,领略精彩的IT技术。

Oracle数据库该如何着手优化一个SQL

3344
来自专栏idba

insert 语句加锁机制

之前的文章里面总结了很多死锁案例,其实里面有几篇文章对于insert加锁流程表述的不准确,而且微信公众号又无法修改,所以通过本文重新梳理insert...

1333
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1492

扫码关注云+社区

领取腾讯云代金券