MySQL 如何清理海量数据

数据库关系型数据库技术服务知识库
问题描述

在数据日常管理/巡检中,应该监控数据量的增长情况,对于一些冷数据,应该及时进行归档/清理,避免数据库越来越大,最终导致性能问题。同时从业务的角度来说,不同类型的数据应该放置到最合适的数据库中,如一些监控数据我们可以使用 Elasticsearch,如果日常分析任务较重,可以使用数据仓库。

问题分析

删除大表中大量数据时需要特别注意,有两方面的考量:

  1. 产生大量的 binlog,可能导致磁盘空间急剧下降,业务停摆。
  2. 对实例性能产生影响,影响线上业务。

因此,在删除大量数据行时,应该分批次,缓慢的执行删除动作,让这个过程尽量平滑,减少对实例的影响。

解决方案

pt-archiver

pt 工具不只是能实现数据归档,还能实现平滑的删除大量数据,具体使用如下: 1.使用 --dry-run,查看生成的删除语句是否符合预期

(base) [root@ip-10-0-0-22 ~]# pt-archiver --source h=111.62.119.86,P=3306,u=rudonx,p='ASDFasdf@2020',D=sbtest,t=sbtest4 --purge --charset=utf8mb4 --where "id <= 1000000" --progress=200  --limit=200 --sleep=1 --txn-size=200  --statistics  --dry-run

SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest4` FORCE INDEX(`PRIMARY`) WHERE (id <= 1000000) AND (`id` < '17196993') ORDER BY `id` LIMIT 200

SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest4` FORCE INDEX(`PRIMARY`) WHERE (id <= 1000000) AND (`id` < '17196993') AND ((`id` >= ?)) ORDER BY `id` LIMIT 200

DELETE FROM `sbtest`.`sbtest4` WHERE (`id` = ?)

2.去掉 --dry-run 之后真实执行语句

(base) [root@ip-10-0-0-22 ~]# pt-archiver --source h=111.62.119.86,P=3306,u=rudonx,p='ASDFasdf@2020',D=sbtest,t=sbtest4 --purge --charset=utf8mb4 --where "id <= 1000000" --progress=200  --limit=200 --sleep=1 --txn-size=200  --statistics  
TIME                ELAPSED   COUNT
2022-03-15T10:17:39       0       0
2022-03-15T10:17:45       5     200
2022-03-15T10:17:51      12     400

2022-03-15T10:22:23     284    8800
2022-03-15T10:22:30     290    9000
2022-03-15T10:22:36     297    9200
2022-03-15T10:22:40     301    9311
Started at 2022-03-15T10:17:39, ended at 2022-03-15T10:22:42
Source: A=utf8mb4,D=sbtest,P=3306,h=111.62.119.86,p=...,t=sbtest4,u=rudonx
SELECT 9311
INSERT 0
DELETE 9311
Action        Count       Time        Pct
deleting       9311   249.2245      82.38
sleep            47    47.0157      15.54
select           48     1.8596       0.61
commit           47     1.2743       0.42
other             0     3.1441       1.04
```
`
参数说明:

1. --purge:使用此选项意味着清除数据而不是进行归档
2. --progress:每多少行打印进度信息
3. --limit:每次 select 多少行进行删除
4. --sleep:指定休眠时间
5. --txn-size 指定每多少行进行一次事务提交
6. --statistics:打印执行时间的统计信息

**注意**:建议在测试环境中充分进行测试,然后应用到生产环境中,同时建议在业务低峰期执行此操作。
# 参考文档
[1] [https://www.percona.com/doc/percona-toolkit/3.0/pt-archiver.html](https://www.percona.com/doc/percona-toolkit/3.0/pt-archiver.html)
**如果您有其他问题,欢迎您联系火山引擎**[技术支持服务](https://console.volcengine.com/ticket/createTicketV2/)

53
0
0
0
相关产品
评论
未登录
看完啦,登录分享一下感受吧~
暂无评论