MySQL中的Online DDL(第一篇)(r11笔记第3天)

记得有一天快下班的时候,一位开发同事找到我说,需要对一个表做变更,数据量据说有上千万,而当时是使用的MySQL版本是5.5,这可如何是好,对于在线业务要求高的情况下,这种需求真是让人头疼。

而在早期的版本中,这种问题就更让人无语了。在Oracle中这个问题解决的较早,当然在很多技术实现细节上,Oracle和MySQL还是蛮大的差距。Oracle中有在线重定义的方案物化视图prebuilt和在线重定义 (r10笔记第25天),而且本身对于一些DDL的操作代价要比MySQL低。不过在碰到添加字段且加默认值的情况,在Exadata上跑性能测试,问题依旧是很棘手。以至于在有些业务中,对某些核心的大表,有些公司是使用视图来达到这种动态的字段扩展而非直接添加字段。这,应该算是一场技术上的硬仗。

这类问题的根本和数据的存储也密不可分。有兴趣可以看看。MySQL和Oracle的添加字段的处理差别 (r10笔记第73天)

MySQL中这类问题有了一种叫OSC的工具之后,情况有了很大的改观。最早是facebook来做的这件事情,后来Percona进行了改变,使用perl实现,因为功能全面,支持的完善,现在基本上成了标准的行业工具。简称pt-osc。

在MySQL 5.5中,这类问题使用pt-osc来处理就很有效了,在MySQL 5.6推出的online DDL中,已经原生支持,在5.7中已经发展很不错了,如此一来,pt-osc的支持算是一种可选的方式。而也可以由此看出,技术上的重大突破会逐步降低维护的复杂度,所以水航船高,各行各业都有相似之处。

pt工具本身的安装部署很简单,可以参考 Percona-toolkit的安装和配置(r8笔记第86天)

简单的使用pt-table-checksu和pt-table-sync可以参考MySQL主从不一致的修复过程(r10笔记第96天)

首先说明不是所有的DDL都会持续很长时间,比如修改表名,这是一个很有意思的操作,无论表大小,操作效率都很高。

比如我们存在一个表 t_user_login_record,数据量2000万。

-rw-rw---- 1 mysql mysql 8840 Oct 13 17:04 t_user_login_record.frm -rw-rw---- 1 mysql mysql 3162505216 Oct 13 17:09 t_user_login_record.ibd

如果想修改为newtest

> alter table t_user_login_record rename to newtest; Query OK, 0 rows affected (0.03 sec)

这个过程本质上就是数据字典信息的修改。如果你可以理解的更通俗一点,就是修改文件名。

-rw-rw---- 1 mysql mysql 8840 Oct 13 17:04 newtest.frm -rw-rw---- 1 mysql mysql 3162505216 Oct 13 17:09 newtest.ibd

MySQL 5.5原生的DDL代价

为什么MySQL5.5中很多DDL操作的代价很高呢。因为很多场景的处理都是在做数据的复制。

比如我们添加一个字段,添加默认值。

alter table newtest add column newcol varchar(10) default '';

MySQL原生的操作就是创建一个临时的表,开始表数据的复制。

-rw-rw---- 1 mysql mysql 8840 Oct 13 17:04 newtest.frm -rw-rw---- 1 mysql mysql 3162505216 Oct 13 17:09 newtest.ibd -rw-rw---- 1 mysql mysql 8874 Dec 5 11:25 #sql-2931_4807af.frm -rw-rw---- 1 mysql mysql 58720256 Dec 5 11:25 #sql-2931_4807af.ibd

在MySQL5.5中,如果在DDL执行的过程中,在另外一个窗口中做一个insert操作,不好意思,这类操作就会阻塞,持续时间会很长。

insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2013-08-16 16:22:10','150581500032','572031626','183.128.143.113');

如果查看show processlist的结果,就会发现临时表复制的信息和锁的信息。

State                           | Info                                                         
--------------------------------+--------------------------------------------------------------
Waiting on empty queue          | NULL                                                         
copy to tmp table               | alter table newtest add column newcol varchar(10) default '' 
Waiting for table metadata lock | insert into newtest(game_type,login_time,login_account,cn_mas
NULL                            | show processlist      

如果查看show engine innodb status\G的结果,会发现一些很细致的锁信息。

---TRANSACTION 481BF2, not started MySQL thread id 4721141, OS thread handle 0x7f99780bc700, query id 18882493 localhost root Waiting for table metadata lock insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2013-08-16 16:22:10','150581500032','572031626','183.128.143.113') ---TRANSACTION 4821A2, ACTIVE 1 sec inserting, thread declared inside InnoDB 253

mysql tables in use 2, locked 2 46 lock struct(s), heap size 6960, 6887 row lock(s), undo log entries 6887 MySQL thread id 4720559, OS thread handle 0x7f93710b9700, query id 18881573 localhost root copy to tmp table alter table newtest add column newcol varchar(10) default '' TABLE LOCK table `test`.`newtest` trx id 4821A2 lock mode IS TABLE LOCK table `test`.`#sql-2931_4807af` trx id 4821A2 lock mode IX TABLE LOCK table `test`.`#sql-2931_4807af` trx id 4821A2 lock mode AUTO-INC

可以看到锁的信息比我们想的要复杂一些。

当然这个阻塞的时长还是很不乐观的,可能十分钟,数十分钟,取决于DDL的时长。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2013-08-16 16:22:10','150581500032','572031626','183.128.143.113'); Query OK, 1 row affected (5 min 33.04 sec)

MySQL 5.7中的DDL对比

在MySQL 5.7中差别就很大了,一模一样的操作,在MySQL 5.7中还是创建一个临时数据表的数据复制。

-rw-r----- 1 mysql mysql 8874 Dec 5 16:47 newtest.frm -rw-r----- 1 mysql mysql 3900702720 Dec 5 17:05 newtest.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 17:33 #sql-6273_9989e.frm -rw-r----- 1 mysql mysql 46137344 Dec 5 17:33 #sql-ib276-3638407390.ibd

同样的DML语句全然没有压力。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2013-08-16 16:22:10','150581500032','572031626','183.128.143.113'); Query OK, 1 row affected (0.01 sec)

查看show engine innodb status\G的结果就有很大的差别。

mysql tables in use 1, locked 1 0 lock struct(s), heap size 1136, 0 row lock(s) MySQL thread id 628894, OS thread handle 140140882102016, query id 9565763 localhost root altering table alter table newtest drop column newcol Trx read view will not see trx with id >= 909683, sees < 909682 怎么去理解online DDL的一些实现原理呢。我们还是可以使用pt-osc来做。

我们就配置一个用户,在5.7下面的语句有了改进,最好使用create user的方式。

GRANT ALL ON *.* TO 'pt_osc'@'test%' identified by 'pt_osc';

然后使用pt-online-schema-change来完成。这里我们需要给表newtest添加一个索引,基于login_time字段

./pt-online-schema-change --host=10.11.128.99 -u pt_osc -p pt_osc --alter='add index ind_login_time_newtest(login_time)' --print --execute D=test,t=newtest

这个时候看看数据目录,内容就很丰富了。-rw-r----- 1 mysql mysql 8840 Dec 5 17:33 newtest.frm -rw-r----- 1 mysql mysql 3527409664 Dec 5 17:37 newtest.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 18:13 _newtest_new.frm -rw-r----- 1 mysql mysql 15728640 Dec 5 18:13 _newtest_new.ibd -rw-r----- 1 mysql mysql 1213 Dec 5 18:13 newtest.TRG -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_del.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_ins.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_upd.TRN

可以很明显看到创建了3个触发器(针对增删改操作),创建了临时的表复制数据。

命令的部分输出如下:

Altering `test`.`newtest`... Creating new table... CREATE TABLE `test`.`_newtest_new` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键,自增长', 。。。, PRIMARY KEY (`id`), KEY `ind_tmp_account1` (`login_account`) ) ENGINE=InnoDB AUTO_INCREMENT=22681849 DEFAULT CHARSET=utf8 Created new table test._newtest_new OK. Waiting forever for new table `test`.`_newtest_new` to replicate to teststd.test.com... Altering new table... ALTER TABLE `test`.`_newtest_new` add index ind_login_time_newtest(login_time) Altered `test`.`_newtest_new` OK. 2016-12-05T18:13:31 Creating triggers... CREATE TRIGGER `pt_osc_test_newtest_del` AFTER DELETE ON `test`.`newtest` FOR EACH ROW DELETE IGNORE FROM `test`.`_newtest_new` WHERE `test`.`_newtest_new`.`id` <=> OLD.`id` 。。。创建INSERT,DELETE TRIGGER 2016-12-05T18:13:31 Created triggers OK. 2016-12-05T18:13:31 Copying approximately 22571280 rows... INSERT LOW_PRIORITY IGNORE INTO `test`.`_newtest_new` (`id`, `game_type`, `login_time`, `login_account`, `cn_master`, `client_ip`) SELECT `id`, `game_type`, `login_time`, `login_account`, `cn_master`, `client_ip` FROM `test`.`newtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 5214 copy nibble*/ SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`newtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/ Copying `test`.`newtest`: 1% 27:54 remain Copying `test`.`newtest`: 3% 27:43 remain

Copying `test`.`newtest`: 98% 00:25 remain 2016-12-05T18:45:16 Copied rows OK. 2016-12-05T18:45:16 Analyzing new table... 2016-12-05T18:45:16 Swapping tables... RENAME TABLE `test`.`newtest` TO `test`.`_newtest_old`, `test`.`_newtest_new` TO `test`.`newtest` 2016-12-05T18:45:16 Swapped original and new tables OK. 2016-12-05T18:45:16 Dropping old table... DROP TABLE IF EXISTS `test`.`_newtest_old` 2016-12-05T18:45:17 Dropped old table `test`.`_newtest_old` OK. 2016-12-05T18:45:17 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`; 2016-12-05T18:45:17 Dropped triggers OK. Successfully altered `test`.`newtest`.

这个过程用Percona的一张图来说明,用流程化的方式来解读。

简单题几个问题来加深对于online DDL的理解。

1.如果创建索引,这个过程中创建的索引是在源表上还是新表上?

答:要简单来论证可以使用strings来解读临时创建的数据表,这里是_newtest_new.frm,新创建的索引ind_login_time_newtest赫然在列。

# strings _newtest_new.frm PRIMARY ind_tmp_account1 ind_login_time_newtest InnoDB ....

2. pt-osc在系统层面文件的变化情况是怎么样的?

答:我们可以去一些临界点来验证。

开始pt-osc的操作时,文件的情况如下。

-rw-r----- 1 mysql mysql 8840 Dec 5 17:33 newtest.frm -rw-r----- 1 mysql mysql 3527409664 Dec 5 18:36 newtest.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 18:13 _newtest_new.frm -rw-r----- 1 mysql mysql 4345298944 Dec 5 18:45 _newtest_new.ibd -rw-r----- 1 mysql mysql 1213 Dec 5 18:13 newtest.TRG -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_del.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_ins.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_upd.TRN

在变更完成前的一瞬间,文件情况如下,可以看到newtest.ibd和_newtest_new.ibd的切换。

-rw-r----- 1 mysql mysql 8840 Dec 5 17:33 newtest.frm -rw-r----- 1 mysql mysql 3527409664 Dec 5 18:36 newtest.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 18:13 _newtest_new.frm -rw-r----- 1 mysql mysql 4345298944 Dec 5 18:45 _newtest_new.ibd -rw-r----- 1 mysql mysql 1213 Dec 5 18:13 newtest.TRG -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_del.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_ins.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_upd.TRN

再次查看,触发器都会一一删除。

-rw-r----- 1 mysql mysql 8840 Dec 5 18:13 newtest.frm -rw-r----- 1 mysql mysql 4353687552 Dec 5 18:45 newtest.ibd 通过这个过程可以加深对于online DDL的实现原理的理解,不过MySQL 5.7中原生的online DDL原理和pt-osc还是有一些差别,仅仅作为一个参考。

所测试的场景都是使用了默认的选项copy而非inplace

ALTER TABLE的补充语法为:ALGORITHM [=] {DEFAULT|INPLACE|COPY}

对于online DDL的操作,更多的细节稍后再来一篇继续补充完善。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-12-05

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

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

生产环境sql语句调优实战第十篇(r3笔记第39天)

陆陆续续写了九篇关于生产环境sql语句的调优案例,发现了不少问题,可能有些问题回头来看是比较低级的错误,稍加改动就能够运行在秒级,有些可能是在秒级到毫秒级的小步...

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

生产环境sql语句调优实战第二篇(r2第38天)

在生产环境通过sql monitor监控到有一条sql执行效率很差。执行了大约5个小时,得到的sql monitor报告如下: Global Informat...

2787
来自专栏idba

死锁案例之六

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋...

852
来自专栏idba

死锁案例之三

一 前言 死锁其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能...

652
来自专栏Jerry的SAP技术分享

SAP CRM Survey调查问卷的存储模型

数据库表CRM_SVY_DB_SVS,通过如下的函数CRM_SVY_DB_SVS_CREATE插入:

1603
来自专栏数据和云

DBA入门之路:察微知渐细致入微

在DBA的职业生涯中,要面临无数的艰难险阻、排忧解难,所以细致入微,严谨认真的风格必不可少。养成了察微知渐的习惯,才能在分析诊断故障时层剖缕析,直指核心;而我也...

2113
来自专栏进击的程序猿

The Clean Architecture in PHP 读书笔记(十)

这是clean architecture的第十篇,也是具体案例的第二篇,本篇会通过使用laravel框架,来开发我们的应用。

1033
来自专栏数据和云

SQL审核:OR展开与子查询优化案例详解

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

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

sql monitor的使用(一) (r2第30天)

在sql调优中,对于sql语句的实时监控显得尤为重要,如果某条sql语句的性能比较差。可能从前端的直观感觉就是执行时间比较长。 对于dba来说,可能关注的相关因...

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

Oracle中的段(r10笔记第81天)

Oracle的体系结构中,关于存储结构大家应该都很熟悉了。 估计下面这张图大家都看得熟悉的不能再熟悉了。 ? 简单来说,里面的一个重要概念就是段,如果是开发...

3618

扫码关注云+社区