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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 一、引言 最近由于业务需求,需要将公有云RDS(业务库)的大表数据归档至私有云MySQL(历史库),以缩减公有云RDS的体积和成本。 那么问题来了,数据归档的方式有n种,选择哪种呢?经过一番折腾,发现使用percona的pt-archiver就可以轻松并优雅地对MySQL进行数据归档。
7317b2bf9c910bcf1ecb7c629ead4ac2ad8d6df5

一、引言

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

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

待我娓娓道来~

1.1 pt-archive是啥

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

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

1.2 pt-archive能干啥

  • 清理线上过期数据;
  • 导出线上数据,到线下数据作处理;
  • 清理过期数据,并把数据归档到本地归档表中,或者远端归档服务器。

二、基本信息

2.1 MySQL环境

0943088ab6ea0b21de9535ace36fbe33c85b9dff

2.2 pt-archiver信息

a503ee9975545f14614ba196999d1767ec516057

2.3 归档表信息

aca6666fb35f4edd98f881c77e5b05b78ad84a9d

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

d4c8f64a3e0ada4a2210dd5c690fa60663554d00

三、模拟场景

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

f522591bb4ee06259c6c2b60eff97ada10d5f15d

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

ae7bb93984172b6abfb9e11224de271fc61ff093

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

d50354a4cf8086406b7fe1d9eefbe17debbd1700

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


d98ed12f1a0deb91147e8830b7e7dc63afdf07f8

四、小结

4.1 性能对比

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

68d0ecbe4dd224e4f8c141fa8b5bc4afe3cf3cd1

场景2-1:全表归档,删除原表数据,非批量插入,非批量删除4.2 general log分析

  • 从日志看起来,源库的查询和目标库的插入有先后顺序
  • 从日志看起来,目标库的插入和源库的删除,并无先后顺序。在特定条件下,万一目标库插入失败,源库删除成功,咋搞?感觉这里并不十分严谨
  • 删除采用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)

五、附录

常用参数

a2a6fa0100cd34c226f95c9649e9bdbb628fd115


原文发布时间为:2017-11-28

本文作者:蓝剑锋@知数堂

本文来自云栖社区合作伙伴“老叶茶馆”,了解相关信息可以关注“老叶茶馆”微信公众号

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
Kubernetes 应用服务中间件 nginx
提升K8S故障排除效率:详解Pod内抓包的高效策略!
提升K8S故障排除效率:详解Pod内抓包的高效策略!
505 0
|
关系型数据库 MySQL 数据库
|
消息中间件 NoSQL Cloud Native
对Confluent一些解读
对Kafka母公司Confluent IPO、产品、技术一些看法
4695 0
对Confluent一些解读
|
存储 关系型数据库 MySQL
|
8月前
|
SQL 关系型数据库 MySQL
阿里面试:1000万级大表, 如何 加索引?
45岁老架构师尼恩在其读者交流群中分享了如何在生产环境中给大表加索引的方法。文章详细介绍了两种索引构建方式:在线模式(Online DDL)和离线模式(Offline DDL),并深入探讨了 MySQL 5.6.7 之前的“影子策略”和 pt-online-schema-change 方案,以及 MySQL 5.6.7 之后的内部 Online DDL 特性。通过这些方法,可以有效地减少 DDL 操作对业务的影响,确保数据的一致性和完整性。尼恩还提供了大量面试题和解决方案,帮助读者在面试中充分展示技术实力。
|
11月前
Vue3步骤条(Steps)
这是一个基于 Vue2 的步骤条(Steps)组件,支持多种自定义属性,如步骤数组、宽度、大小、垂直显示、标签位置等。通过 `v-model` 可实现步骤的动态切换和点击交互。提供了丰富的样式调整选项,适用于各种场景下的多步骤流程引导。组件内详细展示了如何创建和使用步骤条,并提供了多个示例代码片段。
1164 4
Vue3步骤条(Steps)
|
11月前
|
缓存 关系型数据库 MySQL
分享一个实用的MySQL一键巡检脚本
分享一个实用的MySQL一键巡检脚本
199 0
|
存储 Prometheus Kubernetes
一文搞懂基于 Helm 部署 Prometheus Stack 全家桶
Hello folks! 今天我们介绍一下基于 Helm 快速部署安装 Prometheus Stack 的文章,在本文中,我们将讨论 Prometheus 和 Grafana,以及如何使用 Helm 图表为任何 Kubernetes 集群设置监控。我们还将学习如何将 Prometheus 和 Grafana 连接在一起,并在 Grafana 上设置一个基本的仪表板来监控 Kubernetes 集群上的资源。
3230 0
|
SQL 存储 关系型数据库
一文读懂 MySQL Explain 执行计划
一文读懂 MySQL Explain 执行计划
1827 0