优雅地使用pt-archiver进行数据归档
优雅地使用pt-archiver进行数据归档
导读
作者：蓝剑锋 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：
- set autocommit=0
- 批量查询（对应参数limit）
SELECT /*!40001 SQL_NO_CACHE */ `uuid` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`uuid` >= '266431')) ORDER BY `uuid` LIMIT 100003. 逐行删除
DELETE FROM `test123`.`c1` WHERE (`uuid` = '000002f0d9374c56ac456d76a68219b4')4. COMMIT(对应参数--txn-size，操作数量达到--txn-size，则commit)
目标库general log：
- set autocommit=0
- 逐行插入
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 ...
源库：
- set autocommit=0
- 批量查询（对应limit参数）
SELECT /*!40001 SQL_NO_CACHE */ `uuid` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`uuid` >= '266431')) ORDER BY `uuid` LIMIT 100003. 批量删除
DELETE FROM `test123`.`c1` WHERE (((`uuid` >= '266432'))) AND (((`uuid` <= '273938'))) AND (1=1) LIMIT 100004. COMMIT(对应参数--txn-size，操作数量达到--txn-size，则commit)
目标库：
- set autocommit=0
- 批量插入
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用以回收表空间 |