专栏首页MySQLBeginner优雅地使用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:

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

本文分享自微信公众号 - MySQLBeginner(MySQLBeginner),作者:蓝剑锋@知数堂

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-11-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

    wubx
  • MySQL统计信息简介

    MySQL执行SQL会经过SQL解析和查询优化的过程,解析器将SQL分解成数据结构并传递到后续步骤,查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优...

    wubx
  • MySQL 统计信息简介

    MySQL执行SQL会经过SQL解析和查询优化的过程,解析器将SQL分解成数据结构并传递到后续步骤,查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优...

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

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

    wubx
  • Java的Web应用故障诊断之glassbox配置

    (tomcat文件夹)/webapps/glassbox/install/glassbox/glassbox.properities文件。

    Criss@陈磊
  • 如何永久删除Kafka的Topic

    使用kafka-topics --delete命令删除topic时并没有真正的删除,而是把topic标记为:“marked for deletion”,导致重新...

    Fayson
  • Pytorch 学习笔记之自定义 Module

    pytorch 是一个基于 python 的深度学习库。pytorch 源码库的抽象层次少,结构清晰,代码量适中。相比于非常工程化的 tensorflow,py...

    丁科
  • 【RL-TCPnet网络教程】第25章 DHCP动态主机配置协议基础知识

    本章节为大家讲解DHCP(Dynamic Host Configuration Protocol,动态主机配置协议),通过前面章节对TCP和UDP的学习,需要大...

    armfly
  • 如何在 Debian 中安装 DHCP 服务器

    动态主机配置协议(DHCP)是一种用于使主机能够从服务器自动分配 IP 地址和相关的网络配置的网络协议。DHCP 服务器分配给 DHCP 客户端的 IP 地址处...

    Debian社区
  • wareshark网络协议分析之DHCP

    声明:本文关于DHCP协议介绍部分摘自百度百科 一、DHCP协议介绍:     DHCP(Dynamic Host Configuration Protocol...

    用户1215536

扫码关注云+社区

领取腾讯云代金券