优雅地使用pt-archiver进行数据归档

时间：2022-09-23 08:17:32

导读

作者：蓝剑锋

TCL高级DBA，MySQL&Oracle OCP

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

微信：lanjian106103

个人公众号：DBARUN



优雅地使用pt-archiver进行数据归档

一、引言

最近由于业务需求，需要将公有云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操作的表必须有主键

优雅地使用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

优雅地使用pt-archiver进行数据归档

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

优雅地使用pt-archiver进行数据归档

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

优雅地使用pt-archiver进行数据归档

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

优雅地使用pt-archiver进行数据归档

四、小结

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用以回收表空间



优雅地使用pt-archiver进行数据归档


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

（群号：579036588）

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

优雅地使用pt-archiver进行数据归档


优雅地使用pt-archiver进行数据归档优雅地使用pt-archiver进行数据归档优雅地使用pt-archiver进行数据归档优雅地使用pt-archiver进行数据归档


知数堂

叶金荣与吴炳锡联合打造

领跑IT精英培训

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

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

数门精品课程

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

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

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

DBA、开发工程师必修课

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

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


优雅地使用pt-archiver进行数据归档


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

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

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

密码：hg3h


优雅地使用pt-archiver进行数据归档

优雅地使用pt-archiver进行数据归档优雅地使用pt-archiver进行数据归档




标签：

相关文章