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的操作，更多的细节稍后再来一篇继续补充完善。

时间： 2024-11-09 02:49:17

MySQL中的online DDL(第一篇）(r11笔记第3天)的相关文章

mysql中使用UDF自动同步memcached效率笔记_Mysql

接上篇:mysql使用mysql-udf-http效率测试笔记 ,这次不使用rest架构,而是使用:libmemcached和memcached_functions_mysql,测试版本是: libmemcached-0.34.tar.gz和memcached_functions_mysql-0.9.tar.gz,其它版本配对都有问题,我安装测试过有问题的版本有: 复制代码 代码如下: memcached_functions_mysql-1.1在: libmemcached-0.49\libme

MySQL中的binlog和redo浅析(r12笔记第5天)

   有一个小问题可能很多人都想起过,那就是MySQL中既然已经有了binlog,为什么还需要redo,这个问题看起来好像很简单,但是细细品来,还是有不少值得注意的地方.     对于数据恢复,尤其是异常宕机的情况下,再次启动的时候,如何恢复,恢复的数据依据,这个尤为重要,在MySQL中是有checkpoint的技术来做一个基本的检查点控制,也就是常说的LSN,对于事务性数据库,大都会采用write ahead log的策略,即当前事务提交的时候,先写redo,在修改相应的页,如果发生宕机导致数

MySQL 5.7 Group Replication错误总结(r11笔记第84天）

   今天来总结下MySQL 5.7中的一些问题处理,相对来说常规一些.搭建的过程我就不用多说了,昨天的文章里面可以看到一个基本的方式,在测试环境很容易模拟,如果在多台物理机环境中搭建是不是也一样呢,答案是肯定的,我自己都一一试过了.     因为搭建的环境官方建议也是single_primary的方式,即一主写入,其它做读,也就是读写分离,当然支持multi_primary理论上也是可行的,但是还是有点小问题,我们就以single_primary来举例.  问题1: 读节点加入组的时候,sta

MySQL中BOOL/BOOLEAN 与 TINYINT 区别 - 测试笔记

(一) 数据类型测试 (1). 布尔类型BOOL/BOOLEAN 与 微整型TINYINT a). 创建测试表结构 root@localhost : test 05:12:49> CREATE TABLE boolean_test(ID INT NOT NULL AUTO_INCREMENT, ->                           Online_Flag BOOL, ->                           Lock_Flag BOOLEAN, -&g

MySQL和Oracle行值表达式对比(r11笔记第74天)

行值表达式也叫作行值构造器,在很多SQL使用场景中会看到它的身影,一般是通过in的方式出现,但是在MySQL和Oracle有什么不同之处呢.我们做几个简单的测试来说明一下. MySQL 5.6,5.7版本的差别 首先我们看一下MySQL 5.6, 5.7版本中的差别,在这一方面还是值得说道说道的. 我们创建一个表users,然后就模拟同样的语句在不同版本的差别所在. 在MySQL 5.6版本中. create table users( userid int(11) unsigned not nu

Oracle中的PGA监控报警分析(r11笔记第97天)

最近接到一个数据库报警,让我颇有些意外,这是一个PGA相关的报警.听起来感觉是应用端的资源调用出了问题. 报警内容大体如下: 报警内容: PGA Alarm on alltest ------------------------------------报警级别: PROBLEM ------------------------------------监控项目: PGA:6118.6 这是一个12cR1的环境,是一套测试环境,确切的说是多套环境整合后的一套大的测试环境,里面含有近8个PDB,也就是

MySQL Online DDL（二）（r11笔记第88天）

对于Online DDL,之前简单分析了一些场景MySQL中的Online DDL(第一篇)(r11笔记第3天),其实有一个很关键的点没提到,那就是online DDL的算法,目前有三个操作选项,default,inplace,copy可选 具体可以参考  https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html > select count(*) from newtest; +----------+ | count(*) |

《C语言解惑》—— 第一篇 C语言编程中的对与错

第一篇 C语言编程中的对与错 本篇主要采用分析编程中存在的典型错误.对比正确与错误程序的方法,使读者加深印象并提高分辨语法对错及编程的能力,进而达到尽快掌握C语言编程基础知识的目的.

Android通过json向MySQL中读写数据的方法详解【读取篇】_Android

本文实例讲述了Android通过json向MySQL中读取数据的方法.分享给大家供大家参考,具体如下: 首先 要定义几个解析json的方法parseJsonMulti,代码如下: private void parseJsonMulti(String strResult) { try { Log.v("strResult11","strResult11="+strResult); int index=strResult.indexOf("[");