pt-online-schema-change的原理解析与应用说明

2018-06-17 23:39:18来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

 
 


 
    如果转载,请注明博文来源: www.cnblogs.com/xinysu/   ,版权归 博客园 苏家小萝卜 所有。望各位支持!
  


 
    PERCONA提供了若干管理维护MySQL的小工具,集成在 PERCONA Toolkit工具中,有慢查询分析、主从差异对比、主从差异修复及在线表结构修改等工具,个人觉得挺好用的。本文简单介绍 pt-online-schema-change 工具。
    前篇博文:http://www.cnblogs.com/xinysu/p/6732646.html ,讲解过5.6引入的 Online DDL,在5.7版本已较成熟,但毕竟这是5.6版本后才支持,之前版本没有这特性加持,所以本文简单说下pt-online-schema-change工具,名字好长,业界有时取首字母简称它为pt-osc,在使用5.6版本之前的版本或者DDL不适用Online DDL的时候(比如修改数据类型),你可以测试后衡量是否使用该工具来执行DDL SQL,pt-online-schema-change可以在执行ALTER语句中,不堵塞数据库表格的写操作。

1 原理介绍

    表格必须带有主键或者唯一索引!!
    假设现有tbosc需要做ALTER操作,使用pt-online-schema-change的时候,根据tbddl表结构及索引情况,创建一个新的空表_tbosc_new,然后从原始表格 tbosc 中拷贝数据到新的表格 _tbosc_new,copy data结束后,使用_tbosc_new替换tbddl,同时,删除旧表。
 
     简单流程如上描述,那么详细流程是怎么样的呢?
 
     带着这几个问题来了解:
  • ALTER操作期间,表格是否支持DML?
  • 如果支持DML,是如何把DML同步到新的临时表上?
  • 整个操作流程锁情况是怎么样的?
  • 执行期间有什么性能影响?
  • 该工具有什么限制情况?

1.1 详细执行流程

    如何查看其详细的执行流程呢?数据库开启general log,然后执行pt-online-schema-change,它对数据库的所有操作,就都呈现在眼前。
 
     详细执行流程如下:
  1. 相关环境参数检查
  2. 检查该表格是否存在
  3. show create table tbosc
  4. create table _tbosc_new
  5. alter table _tbosc_new 
  6. 创建删除触发器 pt_osc_dbddl_tbosc_del (如果数据修改的时候,还没有拷贝过来,修改后再拷贝则是覆盖,正确;如果是已经拷贝过来,再修改,也是正确,这里同时会检查是否具有主键或者唯一索引,如果都没有,这一步会报错,提示The new table `dbosc`.`_tbosc_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.)
  7. 创建更新触发器 pt_osc_dbddl_tbosc_upd
  8. 创建插入触发器 pt_osc_dbddl_tbosc_ins
  9. 按块拷贝数据到新表,拷贝过程对数据行持有S锁
  10. analyze 新表
  11. rename 表名,RENAME TABLE `dbddl`.`tbosc` TO `dbddl`.`_tbosc_old`, `dbddl`.`_tbosc_new` TO `dbddl`.`tbosc`
  12. 删除旧表
  13. 删除新表上的删除、更新、插入 触发器

1.2 问题解答

   根据其执行流程,可以对一开始的提问一 一来解答。
  • ALTER操作期间,表格是否支持DML?
    • ALTER过程采用Copy Table To New Table的方式,新建一个表格,然后在原表上创建3个触发器:DELETE\UPDATE\INSERT触发器,一旦新表,拷贝数据到新表的过程中,如果原表数据发生变化,则会通过触发器更新到新表上。
    • 如果支持DML,是如何把DML同步到新的临时表上?
    • ALTER过程采用Copy Table To New Table的方式,新建一个表格,然后在原表上创建3个触发器:DELETE\UPDATE\INSERT触发器,一旦新表,拷贝数据到新表的过程中,如果原表数据发生变化,则会通过触发器更新到新表上。
    • INSERT原表的时候,触发器根据其主键ID把新纪录INSERT到新表上;
    • UPDATE原表的时候,触发器根据其主键ID判断新旧ID是否一致,如果一致则删除,然后在REPLACE INTO新纪录到新表
    • DELETE原表的时候,触发器根据其主键ID直接删除行记录
    • 如果数据修改的时候,还没有拷贝到新表,修改后再拷贝,虽然重复覆盖,但是数据也没有出错;如果是数据已经拷贝,原表发生修改,这时触发器同步修改数据,两种情况下都保证了数据的一致性;
  • 整个操作流程锁情况是怎么样的?
    • 创建新表后,按照每一个chunk的大小拷贝数据到新表,每次SELECT都是share mode,带S锁,但是每个chunk都比较小,所以锁时间不大
    • 最后数据拷贝结束,会有一个rename操作,这个操作过程中,是不支持DML操作的,但其速度很快,不会造成长时间锁表情况
    • 该工具会设置该DDL操作的锁等待超时为1s,当出现异常的时候,会是ALTER操作异常,而不是其他业务操作异常,这样可以最大程度的不影响其他事务的进行
  • 执行期间有什么性能影响?
    • 总体而言,对数据库的锁影响降低到了最小,执行期间允许DML操作
    • 但是注意,任何DDL SQL在这里,都是转换成copy table to new table的形式,这个过程中,会极大占用磁盘的IO跟CPU资源,同时跟住从延时带来一定的影响,还是那句老话,重复了解DDL的影响程度后,再选择合适时机执行。
    • copy data过程中,如果主从延迟异常超过 max-lag则停止copy data,等待主从延迟恢复,默认为1min,可以通过--max-lag设置
    • 检测到服务器负载异常,也会停止操作,可以通过 --max-load,--critical-load设置
  • 该工具有什么限制情况?
    • 表格必须带有主键或者唯一索引
    • 存在复制过滤掉表格,ALTER操作
    • copy data过程中,如果主从延迟异常超过 max-lag则停止copy data,等待主从延迟恢复,默认为1s,可以通过--max-lag设置
    • 检测到服务器负载异常,也会停止操作,可以通过 --max-load,--critical-load设置
    • 设置操作的锁等待超时为1s,当出现异常的时候,ALTER操作异常,而不是其他业务操作异常,这样可以最大程度的不影响其他事务的进行
    • 默认情况下,存在 被外键引用的表格是不支持ALTER操作的,除非手动指定参数--alter-foreign-keys-method
    • 不支持修改 Percona XtraDB Cluster (PXC)上节点的 myisam表格

2 环境准备

    工具包下载网页:https://www.percona.com/downloads/percona-toolkit/LATEST/  (目前最新版本为3.0.2)
   rpm下载地址:https://www.percona.com/downloads/percona-toolkit/3.0.2/binary/redhat/7/x86_64/percona-toolkit-3.0.2-1.el7.x86_64.rpm
   官方使用说明文档地址: https://www.percona.com/doc/percona-toolkit/LATEST/index.html
   环境安装依赖项目: Perl, DBI, DBD::mysql
 
   安装非常简单,在安装好相关的环境依赖项后,执行rpm安装即可
   rpm -ivh percona-toolkit-3.0.2-1.el7.x86_64.rpm

3 语法说明

3.1 主要选项

  • --alter
    • 指定ALTER 语句,正常的ALTER TABLE TBNAME [ ADD | MODIFY | DROP | ALTER ] COLUMN COLUMN_NAME ...,去除前面的ALTER TABLE TABLE那么,直接指定后部分的内容
    • 注意事项
      • rename不支持,请直接使用RENAME TABLE tablename TO new_tablename;
      • 如果表格有数据,创建非空无默认值的列,会失败,如果非空,需要指定默认值;
      • 如果表格有数据,为一个可空的列添加默认值时,旧数据为NULL的是不会被修改,依旧为NULL,以后新加入到数据则会默认为设置的默认值
      • 对于外键的删除情况,由于执行是在新表上执行DDL,所以其外键值的命名跟原表的命名不一样,假设删除原表的外键名是 fk_foo,那么新表的外键名就为 _fk_foo,所以删除的ALTER语句是: drop foreign key _fk_foo;
  • --alter-foreign-keys-method
    • 如果修改的表格,是其他表格外键reference的表格,那么,最后rename的过程,需要确保一定成功,要不然这些子表就没能成功reference到其指定的表格名,对子表的操作将会报错。比如 tba有一个外键 fk_tba引用表格 tbb,这个时候tbb需要做DDL操作,根据pt工具的原理得知,最后会有一个rename环节,这个环节可能会导致约束失效或者执行堵塞等问题。
    • 所以,针对最后rename的这个环节,该工具提供了4中处理方法:
      • auto
        • 自动选择 rebuild_constraints 或者 drop_swap,优先选择rebuild_constraints
        • rebuild_constraints
        • rename table 前,先删除子表的外键约束,然后重建外键约束指向到新表(ALTER TABLE语句添加),最后执行rename操作
        • 这个rename操作即使不成功,它也rename到新表,不会出现reference的表格不存在情况
        • 弊端:如果子表过大,添加外键约束的过程中,可能会对子表造成堵塞
      • drop_swap
        • 执行rename之前禁用外键检查,然后删除原表,rename新表为原表名
        • 这个过程非常快并且没有堵塞
        • 这个方法需要强制指定 --no-swap-tables 跟 --no-drop-old-table.
        • 弊端:当把原表删除而新表还没rename为原表的名字时,这段时间实际非常短,但是这段时间内,等于原表名的表格时不存在的,子表做一些DML的时候,可能会出现错误。rename期间,如果新表rename原表失败,但是已经删除原表,那么这段期间,其子表的操作将会出现大面积问题,直到人工修复
      • none
        • 类似drop_swap操作,不同在于对原表的处理。
        • 按正常的pt工具流程,禁用外键约束,rename原表为临时表,rename新表为原表名,删除临时表
        • 弊端: 当把原表rename为临时表,而新表还没rename为原表的名字时,这段时间实际非常短,但是这段时间内,等于原表名的表格时不存在的,子表做一些DML的时候,可能会出现错误
      • --drop-old-table
        • 操作成功后,原表是否保留,默认是删除,
        • default:yes,可选:--no-drop-old-table
  • --dry-run
    • 仅创建新表格,但是不执行触发器、拷贝数据跟替换原表
  • --execute
    • 确定执行ALTER操作,注意,这个操作如果不指定,则仅做安全检查然后推出。
    • 充分了解工具的使用情况后,才执行,不要啥都不了解测试就线上执行,会挖坑的...
  • --host
    • 连接主机名
  • --max-lag
    • 默认1s
    • 检查从库延迟的时间,如果超过,则停止copy data,休息--check-interval秒后,再重新开始copy数据
    • 查看通过延迟时间,是通过从库show slave status,查看Seconds_Behind_Master
    • 如果指定--check-slave-lag,该工具只检查该服务器的延迟,而不是所有服务器。
  • --check-interval
    • 从库延迟超过指定的--max-lag,中断copy data休息的时间
    • 默认为1s
  • --max-load
    • copy data的过程,监控数据库当前正在运行的thread,如果超过指定的Threads_running值,则停止拷贝数据,会在输出的内容中答应 Pausing because Threads_runing=15,直到运行的线程数小于给定的值,恢复copy data,如此循环,知道拷贝数据结束。
    • Threads_runing默认为25
    • 举例:--max-load=Thread_running=15
  • --password
    • 数据库用户名的密码
  • --port
    • 数据库端口号
  • --socket
    • 数据库socket文件
  • --user
    • 数据库用户名
  • --recursion-method
    • MASTER寻找SLAVE的方式(这个选项基本在涉及主从问题的pt工具中广泛应用到)
    • 有4个选项
    • processlist,使用show processlist查找从库
    • hosts,如果不是使用默认端口号3306,那么使用hosts方式来查找从库会更可靠
    • dsn,使用表格 tdsn存储从库信息(DSN的具体参数选项可以详细查看 3.3 DSN选线)
      • 需要手动在需要DDL的数据库内,创建 dsns 表格
      • CREATE TABLE `dsns` (`id` int(11) NOT NULL AUTO_INCREMENT,`parent_id` int(11) DEFAULT NULL,`dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`));
      • 存储从库信息
      • insert into dsns(dsn) values(h=slave_host,u=repl_user,p=repl_password,P=port );
      • 该参数使用的时候,按以下格式(假设 dsns表格建立在数据库 dbosc)
      • --recursion-method dsn=D=dbosc,t=dsns
    • none,不查找从库

3.2 输出 

  • --statistics 增加影响行数打印,可以查看copy进度
  • --print 详细打印alter过程,不指定的时候,简略打印

3.3 DSN选项

  • A
    • 字符集设置
  • D
    • 需要Alter的表格是在哪个数据库,指定数据库
  • F
    • mysql read default file,如果数据源的相关选项存储在文件中,则通过 F 来指定
  • h
    • host,数据库主机名或IP
  • p
    • password,数据库用户的密码
  • P
    • port,数据库实例端口号
  • S
    • socket,实例socket文件
  • t
    • 表格名
  • u
    • 用户名

4 使用举例

4.1 常规DDL

    常规情况,如果有被外键引用的表格,注意对--alter-foreign-keys-method的设置,本小节不考虑从库延迟情况及外键情况。
    pt-online-schema-change也支持同个表格的多个SQL合并为一个SQL,由于所有DDL都是采用COPY TABLE TO NEW TABLE方式,所以使用的时候,不需要对DDL SQL做分类。
 
 1 CREATE TABLE `tbosc` (
 2   `id` int(11) NOT NULL AUTO_INCREMENT,
 3   `name` varchar(100) DEFAULT NULL,
 4   `age` int(11) DEFAULT NULL,
 5   PRIMARY KEY (`id`)
 6 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
 7  
 8 #添加列
 9 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "add column stunum int "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
10  
11 #删除列
12 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "drop column stunum "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
13  
14 #修改列数据类型
15 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "modify column age varchar(10)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
16  
17 #加大列长度
18 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "modify column age varchar(100)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
19  
20 #创建索引
21 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "ADD INDEX IX_AGE(AGE)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
22  
23 #删除索引
24 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "DROP INDEX IX_AGE"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
25  
26 #设置默认值
27 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "ALTER column age SET DEFAULT 100"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
28  
29 #能否多个合成一个
30 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=****  D=dbosc,t=tbosc --alter "ADD COLUMN onecol int ,add column twocol varchar(100),add index ix_onecol(onecol),alter column name set default 'xinysu' "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute

4.2 考虑从库延迟情况

    考虑从库延迟情况 ,意味这要注意这几个选项的设置
  • --max-lag
  • --check-interval
  • --recursion-method
  • --check-slave-lag
    从库延迟超过max-lag则停止copy data,等待 check-interval 秒后再开始copy data。check-slave-lag指定slave的机器,只会对比这台slave的延迟情况。recursion-method是主库寻找从库的方法,有四个方法:processlist,hosts,dsn,none,具体查看上部分选项详细说明,本节详细描述dsn及check-slave-lag的使用。
 
     假设需要在dbosc库上的表格tbddl添加一列:hobby varchar(100) ,需要考虑从库的延迟情况
#创建表格dsns,记录从库信息
CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
 
#insert从库信息,有2个从库,分别为242服务器上的 3310跟3320
insert into dsns(dsn) select "h=192.168.9.244,u=repl,p=****,P=3310";
insert into dsns(dsn) select "h=192.168.9.244,u=repl,p=****,P=3320";
 
    如果需要考虑多个从库的延迟情况,则可以考虑使用 dsns表格来记录从库信息,如果只需要考虑某一台从库的延迟情况,则既可以使用dsns表格也可以使用参数--check-slave-lag指定从库。
 
    不考虑外键关系,考虑从库影响程度,检查到从库延迟超过1s,则休息5s,具体指令如下
 
pt-online-schema-change -P3310 --user=root --password=****  D=dbosc,t=tbddl --max-lag=1s --check-interval=10s --alter "ADD hobby varchar(100) NOT NULL DEFAULT 'sleep' "  --recursion-method dsn=D=dbosc,t=dsns  --alter-foreign-keys-method auto --execute
     
     如果检测到slave的 Seconds_Behind_Master超过1s,则会休息10s后再监测,这个过程会在输出文件中打印出:
 
    Replica lag is 395 seconds on sutest244.  Waiting.
   Replica lag is 425 seconds on sutest244.  Waiting.
   Replica lag is 456 seconds on sutest244.  Waiting.
 
    说明现在主从延时了多少秒,现在copy线程停止,正在等待中。
    
     如果是仅指定一个从库查看延迟情况,使用--check-slave-lag的指令如下:
 
pt-online-schema-change -P3310 --user=root --password=****  D=dbosc,t=tbddl --max-lag=1 --check-interval=10 --check-slave-lag=h=192.168.9.244,u=root,p=****,P=3310  --alter "ADD hobby varchar(100) NOT NULL DEFAULT 'sleep' "  --recursion-method  --alter-foreign-keys-method auto --print --execute

5 pt-osc还是online DDL?

    经过这一篇介绍pt-online-schema-change的原理说明及测试,以及上篇的online ddl说明,可明白pt-osc无论是什么DDL SQL,都会新建新表来替换,不分DDL类型,但是执行期间允许DDL操作,而ONLINE DDL则分为了好几类DDL,有的DDL仅需修改元数据,有的DDL仅需在本身ibd文件上新建索引页,有的需要rebuild table,这三种类型执行期间支持DML操作,但是COPY TABLE 类型不支持DML操作。
因此,可以有以下几个判断:
  • 如果MySQL版本是5.6之前,不支持online ddl操作的,pt-online-schema-change是一个非常好的选择;
  • 如果MySQL的版本是5.6以上的,支持online-ddl的,优先考虑使用online ddl,但是如果是ddl SQL 在online DDL中 需要copy table to tmp table,则建议使用pt-online-schema-change来处理,比如修改列数据类型的DDL,online DDL则是需要copy table to tmp table,期间仅支持查询,不支持DML操作,这个时候,就可以使用pt-online-schema-change来处理,因为它也是拷贝临时表格,并且执行期间支持DML操作;
  • 如果执行Online DDL,但是对从库的延迟非常敏感,针对需要copy table 跟rebuild table这两类DDL SQL,需要考虑是否可以在从库设置并行复制,如果不行,则优先选择pt-online-schema-change。

参考文档:https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql重复数据查询删除

下一篇:MYSQL中LIMIT用法