导读

作者：蓝剑锋

TCL高级DBA，MySQL&Oracle OCP

知数堂MySQL实战/优化班第12期学员

微信：lanjian106103

个人公众号：DBARUN

一、引言

最近由于业务需求，需要将公有云RDS（业务库）的大表数据归档至私有云MySQL（历史库），以缩减公有云RDS的体积和成本。

那么问题来了，数据归档的方式有n种，选择哪种呢？经过一番折腾，发现使用percona的pt-archiver就可以轻松并优雅地对MySQL进行数据归档。

待我娓娓道来~

1.1 pt-archive是啥

属于大名鼎鼎的percona工具集的一员，是归档MySQL大表数据的最佳轻量级工具之一。

注意，相当轻，相当方便简单。

1.2 pt-archive能干啥

• 清理线上过期数据；

• 导出线上数据，到线下数据作处理；

• 清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器。

二、基本信息

2.1 MySQL环境

	源库	目标库
版本	MySQL 5.7.20 二进制	MySQL 5.7.20 二进制
OS	CentOS release 6.5 (Final)	CentOS release 6.5 (Final)
IP	10.73.129.187	10.73.129.188
port	3306	3306
配置	2c4g	2c4g
binlog	开启	开启

2.2 pt-archiver信息

版本	pt-ioprofile 3.0.4
OS	CentOS release 6.5 (Final)
IP	10.73.129.189
机器配置	2c4g

2.3 归档表信息

归档表	c1
记录数	1000000
体积	304M

注意：pt-archiver操作的表必须有主键

三、模拟场景

3.1 场景1-1：全表归档，不删除原表数据，非批量插入

pt-archiver \
--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --statistics --no-delete

3.2 场景1-2：全表归档，不删除原表数据，批量插入

pt-archiver \
--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --no-delete

3.3 场景2-1：全表归档，删除原表数据，非批量插入，非批量删除

pt-archiver \
--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --statistics --purge

3.4 场景2-2：全表归档，删除原表数据，批量插入，批量删除

pt-archiver \
--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',,D=test123,t=c1 \
--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --purge

四、小结

4.1 性能对比

通过下表可以看出，批量操作和非批量操作的性能差距非常明显，批量操作花费时间为非批量操作的十分之一左右。

模拟场景	非批量操作	批量操作	对比
归档全表100万行，不删除原表数据	486s	83s	0.17
归档全表100万行，删除原表数据	1024s	96s	0.09

模拟场景	insert	bulk_insert	delete	bulk_delete
归档全表100万行，不删除原表数据	420.68s	24.56s	/	/
归档全表100万行，删除原表数据	484.38s	24.89s	452.84s	11.39s

4.2 general log分析

场景2-1：全表归档，删除原表数据，非批量插入，非批量删除

• 从日志看起来，源库的查询和目标库的插入有先后顺序

• 从日志看起来，目标库的插入和源库的删除，并无先后顺序。在特定条件下，万一目标库插入失败，源库删除成功，咋搞？感觉这里并不十分严谨

• 删除采用DELETE FROM TABLE WHERE ... ，每次删除一行数据

• 插入采用INSERT INTO TABLE VALUES('...')，每次插入一行数据

源库general log：

1. set autocommit=0

2. 批量查询（对应参数limit）

SELECT /*!40001 SQL_NO_CACHE */ `uuid` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`uuid` >= '266431')) ORDER BY `uuid` LIMIT 10000

3. 逐行删除

DELETE FROM `test123`.`c1` WHERE (`uuid` = '000002f0d9374c56ac456d76a68219b4')

4. COMMIT(对应参数--txn-size，操作数量达到--txn-size，则commit)

目标库general log：

1. set autocommit=0

2. 逐行插入

INSERT INTO `test123`.`c1`(`uuid`) VALUES ('0436dcf30350428c88e3ae6045649659')

3. COMMIT(对应参数--txn-size，操作数量达到--txn-size，则commit)

场景2-2：全表归档，删除原表数据，批量插入，批量删除

• 从日志看起来，源库的批量查询和目标库的批量插入有先后顺序

• 从日志看起来，目标库的批量插入和源库的批量删除，并无先后顺序。

• 批量删除采用DELETE FROM TABLE WHERE ... LIMIT 10000

• 批量插入采用LOAD DATA LOCAL INFILE 'file' INTO TABLE ...

源库：

1. set autocommit=0

2. 批量查询（对应limit参数）

SELECT /*!40001 SQL_NO_CACHE */ `uuid` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`uuid` >= '266431')) ORDER BY `uuid` LIMIT 10000

 3. 批量删除

DELETE FROM `test123`.`c1` WHERE (((`uuid` >= '266432'))) AND (((`uuid` <= '273938'))) AND (1=1) LIMIT 10000

4. COMMIT(对应参数--txn-size，操作数量达到--txn-size，则commit)

目标库：

1. set autocommit=0

2. 批量插入

LOAD DATA LOCAL INFILE '/tmp/vkKXnc1VVApt-archiver' INTO TABLE `test123`.`c1`CHARACTER SET UTF8(`uuid`)

3. COMMIT(对应参数--txn-size，操作数量达到--txn-size，则commit)

五、附录

常用参数

--where 'id<3000'	设置操作条件
--limit 10000	每次取1000行数据给pt-archive处理
--txn-size 1000	设置1000行为一个事务提交一次
--progress 5000	每处理5000行输出一次处理信息
--statistics	结束的时候给出统计信息：开始的时间点，结束的时间点，查询的行数，归档的行数，删除的行数，以及各个阶段消耗的总的时间和比例，便于以此进行优化。只要不加上--quiet，默认情况下pt-archive都会输出执行过程的
--charset=UTF8	指定字符集为UTF8
--no-delete	表示不删除原来的数据，注意：如果不指定此参数，所有处理完成后，都会清理原表中的数据
--bulk-delete	批量删除source上的旧数据
--bulk-insert	批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的)
--purge	删除source数据库的相关匹配记录
--local	不把optimize或analyze操作写入到binlog里面（防止造成主从延迟巨大）
--analyze=ds	操作结束后，优化表空间（d表示dest，s表示source） 默认情况下，pt-archiver操作结束后，不会对source、dest表执行analyze或optimize操作，因为这种操作费时间，并且需要你提前预估有足够的磁盘空间用于拷贝表。一般建议也是pt-archiver操作结束后，在业务低谷手动执行analyze table用以回收表空间

扫码加入知数堂技术交流QQ群

（群号：579036588）

群内可@各位助教了解更多课程信息

---

知数堂

叶金荣与吴炳锡联合打造

领跑IT精英培训

行业资深专家强强联合，倾心定制

MySQL实战/MySQL优化 / Python/ SQL优化

数门精品课程

紧随技术发展趋势，定期优化培训教案

融入大量生产案例，贴合企业一线需求

社群陪伴学习，一次报名，可学3期

DBA、开发工程师必修课

上千位学员已华丽转身，薪资翻番，职位提升

改变已悄然发生，你还在等什么？

扫码下载知数堂精品课程试听视频

或点击“阅读原文”直达下载地址

（MySQL 实战/优化、Python开发，及SQL优化等课程）

密码：hg3h