online ddl与pt-osc详解

Ⅰ、背景

优化sql的过程中发现表上少一个索引,直接加一个？会不会hang住？不加？sql又跑不好,由此引出一个问题——ddl操作怎么做？

Ⅱ、闲扯三两句

• 5.6版本之前的MySQL创建索引不支持online,会对表加一个读锁(S lock),只能select,insert会阻塞,5.6开始,MySQL原生支持了在线索引添加,在添加索引过程中,应用程序对表依然可读可写
• online ddl的这段时间内,对表做的操作会先记录到alter table的日志里,这个日志是内存的,如果内存大小太小记不下来就会报错

show variables like 'innodb%max%';
innodb_online_alter_log_max_size | 134217728

如果线上更新操作比较多,调大这个值 set global innodb_online_alter_log_max_size = 128M,这是个全局变量,在my.cnf中也配上

Ⅲ、老式DDL

3.1 锁模式与算法解析

1、两个参数：lock和algorithm
锁模式：

模式	含义
default	根据事务最大并发判断用什么模式
none	不加任何锁,不阻塞
shared	共享模式,和5.1的fast index creation一样,可读,但不支持dml
exclusive	排他模式,任何操作都不支持

算法：

算法	含义
default	根据old_alter_table决定用哪个算法,off为用新算法,即inplace
inplace	共享锁,只支持增加和删除索引两种操作
copy	需要拷贝数据,效率低

不管用什么模式,online ddl开始之前都会有一个短时间的排他锁,结束之前也一样,所以说,操作之前需要确保没有大事务执行,否则会出现严重阻塞

2、两种算法添加索引步骤对比(5.5版本)

-	copy	inplace
1	新建带索引的临时表	创建索引数据字典(只能是二级索引,如果是主键指定inplace也会转为copy)
2	锁原表,禁止DML,允许查询	加共享锁,禁止DML,允许查询
3	将原表数据拷贝到临时表	读取聚簇索引,构造新的索引项,排序并插入新索引
4	升级shared锁为exclusive,禁止读写,做rename(修改数据字典,很快)	等待打开当前表的所有只读事务提交
5	完成创建索引操作	创建索引结束

3、语法:

alter table tb_name ...
lock = xxx,algorithm = xxx
注意：多个ddl操作建议放到一条语句种执行,效率比分开执行高

tips:
以上分析是针对5.5及之前的情况,即那时候只有增加、删除索引不需要拷贝原表,但也不能操作DML

Ⅳ、现代online ddl的分类与实现细节

4.1 分类

online ddl包含copy和inplace两种

修改列类型和删除主键用copy

inplace又分为rebuild和no-rebuild两种

rebuild需要重建表,修改记录格式,添加、删除列、修改默认值都用rebuild

no-rebuild只需要修改元数据,添加、删除索引、修改列名则用no-rebuild

rebuild方式比no-rebuild方式实质多了一个ddl执行阶段

4.2 实现细节(三阶段)

先检测一些命名、长度等限制

-	prepare	ddl	commit
1	server层创建临时frm	降级exclusive-mdl锁,允许读写(copy不可写)	升级exclusive-mdl锁,禁止读写
2	持有exclusive-mdl锁,禁止读写	扫描原表的聚簇索引每条记录	应用最后row_log种产生的日志
3	根据alter类型,确定执行方式(copy,inplace-rebuild,inplace-norebuild)	遍历新表的聚簇索引和二级索引	更新innodb的数据字典
4	更新数据字典的内存对象	根据记录构造对应的索引项	提交事务(刷新事务的redo日志)
5	分配row_log对象记录增量	将索引项插入sort_buffer块	修改统计信息
6	innodb层生成临时ibd文件(rebuild情况下)	将sort_buffer块插入新的索引	rename临时idb、frm文件
7	数据字典上提交事务、释放锁	处理ddl执行过程种产生的增量(rebuild情况下)	变更完成

4.3 注意三个参数

参数	-
old_alter_table	默认off即用inplace模式
tmpdir	创建索引时排序的内存不够则在此目录做
innodb_online_alter_log_max_size	存row_log

tips:
②online ddl中inplace是优选项,ALGORITHM=COPY定会拷贝表,只读,但ALGORITHM=INPLACE也可能拷贝表,但可以并发DML(因为有row_log)
③5.6依然不支持online的ddl操作：修改列的数据类型,删除主键,变更表字符集
④inplace对dml的支持比较好,但消耗却比copy大

online ddl关键点小结

①数据完整性--->row_log
②online和数据一致性--->propare和commit时短暂mdl,几乎全程online
③server和innodb一致--->prepare时server生成frm,innodb生成临时ibd,ddl时原表拷贝到ibd,row_log应用到ibd,commit时innodb修改数据字典,提交,最后innodb和server重命名ibd和rfm

Ⅴ、pt-osc

5.1 为什么要用pt-osc

问题：
在线索引添加存在的一个问题——主从延时(MySQL逻辑复制,oracle物理复制不存在这个问题)

原因：
alter table是执行完之后才告诉从机要执行(事务),从库再顺序执行。
如果是copy的那种online ddl,执行到这个ddl,其他并行的dml语句则要等待这个ddl执行完毕后才能继续(看上文原理),如下图：

主从延迟的产生：
 +------------------------+
 |          master        |       o_ddl_5min
 +------------------------+
            |   |
            |log|        同步的是二进制日志,要等事务执行完之后才提交过去,和物理日志不同           
            |   |
 +------------------------+
 |          slave         |       o_ddl_5min
 +------------------------+

因此,即使5.7现在对越来越多的ddl操作读写不阻塞了,真正在线上也很少用alter table这种方式去执行ddl操作

目前我们常用的一个工具是pt-osc

这个工具做在线ddl,主从延迟非常小,它不是直接操作的,是通过触发器的机制来慢慢做,还有专门控制延迟的参数

5.2 安装与操作演示

yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-DBI perl-DBD-MySQL

cd /usr/local/src
wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/tarball/percona-toolkit-3.0.4_x86_64.tar.gz tar zxvf percona-toolkit-3.0.4_x86_64.tar.gz cd percona-toolkit-3.0.4 perl Makefile.PL make make install pt-online-schema-change --alter "convert to character set utf8b4" D=test,t=a 显示操作步骤,真正执行要加 --excute pt-online-schema-change --alter "alter table add index index_a (a)" D=test,t=a --excute 整个过程拆成很多小的步骤,一个一个传到从上去,所以延迟比较小,缺点是时间长

tips:
percona toolkit中最有用的就是pt-online-schema-change,其他工具官方工具包utlities里面都有了,尽量用官方的,另外官方也在做osc了

5.3 原理浅析

方案：

步骤	操作
step1	sysbench导入测试数据到test库sbtest1中
step2	开启general_log,并输出到mysql.general_log表
step3	osc给sbtest1表的c字段加一个索引(可以把execute换做--dry-run）
step4	分析glog

step1：略

step2：
(root@localhost) [(none)]> truncate mysql.general_log;
Query OK, 0 rows affected (1.65 sec)

(root@localhost) [(none)]> set global general_log = 1;
Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> set global log_output = 'table'; Query OK, 0 rows affected (0.01 sec) step 3： pt-online-schema-change --alter "add index index_c (c)" --socket=/tmp/mysql.sock --user=root --password=123 D=test,t=sbtest1 --execute No slaves found. See --recursion-method if host VM_221_162_centos 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`.`sbtest1`... Creating new table... Created new table test._sbtest1_new OK. Altering new table... Altered `test`.`_sbtest1_new` OK. 2017-11-30T18:28:19 Creating triggers... 2017-11-30T18:28:19 Created triggers OK. 2017-11-30T18:28:19 Copying approximately 493200 rows... 2017-11-30T18:28:41 Copied rows OK. 2017-11-30T18:28:41 Analyzing new table... 2017-11-30T18:28:41 Swapping tables... 2017-11-30T18:28:41 Swapped original and new tables OK. 2017-11-30T18:28:41 Dropping old table... 2017-11-30T18:28:41 Dropped old table `test`.`_sbtest1_old` OK. 2017-11-30T18:28:41 Dropping triggers... 2017-11-30T18:28:41 Dropped triggers OK. Successfully altered `test`.`sbtest1`. 上面已经可以看出个大概过程了 step 4： 这一步详细分5块分析如下： (root@localhost) [(none)]> set global log_output = 'file'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> set global general_log = 0; Query OK, 0 rows affected (0.01 sec) (root@localhost) [mysql]> select argument from mysql.general_log; root@localhost on test using Socket set autocommit=1 SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout' SET SESSION innodb_lock_wait_timeout=1 SHOW VARIABLES LIKE 'lock\_wait_timeout' SET SESSION lock_wait_timeout=60 SHOW VARIABLES LIKE 'wait\_timeout' SET SESSION wait_timeout=10000 SELECT @@SQL_MODE SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/ SELECT @@server_id /*!50038 , @@hostname*/ 说明： 1、设置session级的变量 SET SESSION innodb_lock_wait_timeout=1 SET SESSION lock_wait_timeout=60 SET SESSION wait_timeout=10000 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－ SHOW VARIABLES LIKE 'version%' SHOW ENGINES SHOW VARIABLES LIKE 'innodb_version' SHOW VARIABLES LIKE 'innodb_stats_persistent' SELECT @@SERVER_ID SHOW GRANTS FOR CURRENT_USER() SHOW FULL PROCESSLIST SHOW SLAVE HOSTS SHOW GLOBAL STATUS LIKE 'Threads_running' SHOW GLOBAL STATUS LIKE 'Threads_running' SELECT CONCAT(@@hostname, @@port) SHOW TABLES FROM `test` LIKE 'sbtest1' SELECT VERSION() SHOW TRIGGERS FROM `test` LIKE 'sbtest1' /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ USE `test` SHOW CREATE TABLE `test`.`sbtest1` /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ EXPLAIN SELECT * FROM `test`.`sbtest1` WHERE 1=1 SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test' AND referenced_table_name='sbtest1' SHOW VARIABLES LIKE 'wsrep_on' /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 说明： 1、查看变量,当前用户的权限,slave信息,版本信息等 2、检查sbtest1是否存在触发器 3、执行计划 4、检查sbtest1是否存在外键关联 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－ USE `test` SHOW CREATE TABLE `test`.`sbtest1` /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ CREATE TABLE `test`.`_sbtest1_new` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=latin1 ALTER TABLE `test`.`_sbtest1_new` add index index_c (c) /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ USE `test` SHOW CREATE TABLE `test`.`_sbtest1_new` /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'sbtest1' SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'sbtest1' SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'sbtest1' SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'sbtest1' SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'sbtest1' SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'test' AND EVENT_OBJECT_TABLE = 'sbtest1' CREATE TRIGGER `pt_osc_test_sbtest1_del` AFTER DELETE ON `test`.`sbtest1` FOR EACH ROW DELETE IGNORE FROM `test`.`_sbtest1_new` WHERE `test`.`_sbtest1_new`.`id` <=> OLD.`id` CREATE TRIGGER `pt_osc_test_sbtest1_upd` AFTER UPDATE ON `test`.`sbtest1` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_sbtest1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_sbtest1_new`.`id` <=> OLD.`id`;REPLACE INTO `test`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);END CREATE TRIGGER `pt_osc_test_sbtest1_ins` AFTER INSERT ON `test`.`sbtest1` FOR EACH ROW REPLACE INTO `test`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) 说明： 1、根据原表的表结构结创建一张新表 2、对新表上的c字段加索引,这里依然用的是alter 3、检查原表上触发器情况,5.6开始同一张表上不能存在同一个动作的触发器 4、针对新表创建三个触发器,DELETE,UPDATE和INSERT(重点看下三个触发器内容) －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－ EXPLAIN SELECT * FROM `test`.

转载于:https://www.cnblogs.com/DataArt/p/10175783.html