MySQL在线修改大表神器:PT工具

缘由

最近我们公司的慢SQL特别多,特别是某个固定的表,每天基本都有好几千的慢查询,但是这个表有特别大,不能随意加索引,索引我们就需要一个工具来协助我们进行改DDL的时候,不影响业务,那就是 PT工具


效果图

如下是慢SQL数量折线图 明显可以看到大幅度下降了

image.png


使用场景

在线数据库的维护中,总会涉及到研发修改表结构的情况,修改一些小表影响很小,而修改大表时,往往影响业务的正常运转,如表数据量超过500W,1000W,甚至过亿时

在线修改大表的可能影响

在线修改大表的表结构执行时间往往不可预估,一般时间较长

由于修改表结构是表级锁,因此在修改表结构时,影响表写入操作

如果长时间的修改表结构,中途修改失败,由于修改表结构是一个事务,因此失败后会还原表结构,在这个过程中表都是锁着不可写入

修改大表结构容易导致数据库CPU、IO等性能消耗,使MySQL服务器性能降低

在线修改大表结构容易导致主从延时,从而影响业务读取


pt-online-schema-change介绍

pt-online-schema-change是percona公司开发的一个工具,在percona-toolkit包里面可以找到这个功能,它可以在线修改表结构

原理:

1.建立一个与需要操作的表相同表结构的空表

2.给空表执行表结构修改

3.在原表上增加delete/update/insert的after trigger

4.copy数据到新表

5.将原表改名,并将新表改成原表名

6.删除原表

7.删除trigger

pt-osc限制条件

1.表要有主键,否则会报错;

2.表不能有trigger

pt-online-schema-change安装

wget percona.com/get/percona-toolkit.tar.gz
tar -zxvf percona-toolkit.tar.gz
cd percona-toolkit-3.1.0/
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
#提示缺少perl-DBI模块
yum install perl-DBD-MySQL
#Can't locate Digest/MD5.pm
yum -y install perl-Digest-MD5
perl Makefile.PL
make && make install

使用

pt-online-schema-change --help 可以查看参数的使用,我们只是要修改个表结构,只需要知道几个简单的参数就可以了

场景1:增加列

pt-online-schema-change --no-version-check --host=192.168.0.0 -uroot --ask-pass --alter "add column age int(11) default null" D=test,t='test_tb' --execute 

场景2:删除列

pt-online-schema-change  --no-version-check --host=192.168.0.0 -uroot --ask-pass --alter "drop column age" D=test,t='test_tb' --execute 

场景3:更改列

pt-online-schema-change  --no-version-check --host=192.168.0.0 -uroot --ask-pass --alter "CHANGE id id_num int(20)" D=test,t='test_tb' --execute 

场景4:创建索引
pt-online-schema-change  --no-version-check --host=192.168.0.0 -uroot --ask-pass --alter "add index indx_ukid(address_ukid)" D=test,t='address_tb' --execute 

场景5:删除索引
pt-online-schema-change  --no-version-check --host=192.168.0.0 -uroot --ask-pass --alter "drop index indx_ukid" D=test,t='address_tb' --execute

建议

pt-online-schema-change工具还有很多其他的参数,可以有很多限制,比如限制CPU、线程数量、从库状态等等,不过我做过一个超过6000W表的结构修改,发现几乎不影响性能,很稳定很流畅的就修改了表结构,所以,对以上常规参数的使用基本能满足业务

一定要在业务低峰期做,这样才能确保万无一失

参考手册

  1. https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-recursion-method

  2. https://www.cnblogs.com/hxlasky/p/11555271.html