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

Myth丶恋晨 2024-02-18 23:22 14阅读 0赞

传统方法修改表结构 类似alter table xx modify，在修改表结构时需要锁表，如果表很大，则操作时间会较长。目前，绝大多数业务要求24\*7无间断服务，而此过程中，如果造成较长时间数据库无法更新，则会严重影响业务。最近了解到两种可行的方案：1.替换表结构方法，2.采用percona在线修改表结构工具

#### 模拟场景说明： ####

mysql5.6.25（mysql5.6相对于mysql5.5已经改进很多了，在mysql5.6中参考了percona-toolkit，增加删除索引不会锁表，同一个session里面，增删查改也不受影响，但是不同session是会受影响的。）

### pt-online-schema-change工具的使用限制： ###

1）、如果修改表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行

2）、被修改表必须要有主键，否则报错：Cannot chunk the original table \`houyi\`.\`ga\`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.

3）、被修改表上不能有针对after delete|insert|update三个触发器，否则修改表结构操作失败

### pt-online-schema-change 工具介绍 ###

PERCONA提供了若干管理维护MySQL的小工具，集成在 PERCONA Toolkit工具中，有慢查询分析、主从差异对比、主从差异修复及在线表结构修改等工具， 本文简单介绍 pt-online-schema-change 工具。

## **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/][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_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][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个选项
        
         *  ![技术分享][20180110231249956107.png]

*   *  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=ycf.com  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=ycf.com  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=ycf.com  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=ycf.com  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=ycf.com  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=ycf.com  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=ycf.com  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=ycf.com  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=ycf.com 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=ycf.com D=dbosc,t=tbddl --max-lag=1 --check-interval=10 --check-slave-lag=h=192.168.9.244,u=root,p=ycf.com,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][https_www.percona.com_doc_percona-toolkit_LATEST_pt-online-schema-change.html]

[https_www.percona.com_downloads_percona-toolkit_LATEST]: https://www.percona.com/downloads/percona-toolkit/LATEST/
[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/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]: https://www.percona.com/doc/percona-toolkit/LATEST/index.html
[20180110231249956107.png]: http://image.mamicode.com/info/201704/20180110231249956107.png
[https_www.percona.com_doc_percona-toolkit_LATEST_pt-online-schema-change.html]: https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html