mysql数据库的磁盘_MySQL数据库之揪出MySQL磁盘消耗迅猛的真凶

本文主要向大家介绍了MySQL数据库之揪出MySQL磁盘消耗迅猛的真凶 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

Part1:写在最前

当一张单表10亿数据量的表放在你面前，你将面临着什么？

Part2:背景介绍

为了提升数据库资源利用率，一个实例中，在不互相影响，保证业务高效的前提下，我们会将同一个大业务下的不同小业务放在一个实例中，我们的磁盘空间是2T，告警阈值为当磁盘剩余空间10%时发出短信告警。笔者接到某业务主库磁盘剩余空间告警的短信后，经过一番查探，发现从几天前开始，有一张表的数据量增长非常快，而在之前，磁盘空间下降率还是较为平缓的，该表存在大字段text，其大批量写入更新，导致磁盘消耗迅猛。

我们首先来看下该表的表结构：mysql> CREATE TABLE `tablename_v2` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`No` varchar(64) NOT NULL DEFAULT '',

`Code` varchar(64) NOT NULL DEFAULT '' ,

`log` varchar(64) DEFAULT '' ,

`log1` varchar(64) DEFAULT '' ,

.....

`Phone` varchar(20) DEFAULT '',

`createTime` bigint(20) unsigned NOT NULL DEFAULT '0',

`updateTime` bigint(20) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

UNIQUE KEY `uk_mailNo` (`No`,`Code`),

KEY `idx_Phone` (`Phone`)

) ENGINE=InnoDB AUTO_INCREMENT=9794134664 DEFAULT CHARSET=utf8;

与业务了解得知，该表几乎没有删除操作，由于数据量过大，我们模糊使用auto_increment来作为表数量预估值，避免count()操作对线上造成的影响。

Part3:案例分析

与业务沟通了解后得知，该表可以清理4个月以前的老旧数据，因此可以使用delete的方式清除，而我们通过表结构可以看出，该表在设计之初，并没有对updateTime列创建索引，因此以时间为范围去进行delete操作，这显然是不合理的。

经过与业务协商，我们确定了可以将id作为删除条件，删除id<2577754125之前的数据

也就是说，此时的delete语句变为了：mysql> delete from tablename_v2 where id <2577754125;

且不说delete操作有多慢，直接执行这样的SQL也会有诸如长事务告警，从库大量延迟等并发症产生，因此绝不能在生产库上进行这种大批量的危险操作。

实战

Part1:监控

从监控图我们能看出磁盘下降的趋势：

监控显示，从6月14日-6月18日期间，磁盘消耗最为严重，与业务沟通得知，618期间大促引发该表存储量激增导致。

Part2:实战操作

我们通过查看binlog发现，集群中binlog的刷新量虽不说像笔者上个案例那样多么迅猛，但也绝不是老实本分

我们可以看出，在高峰期间，binlog的刷新间隔最短达到了2分钟写满1.1GB的binlog。因此笔者与业务沟通后，首先清理binlog日志，将 expire_logs_days从7天调整至3天。

同时，清理一些能够清理的无用日志、废旧文件等等。

我们也能在上面的监控图看到在做完这些清理操作后，磁盘空间剩余从4%提升至12%,但随后依旧保持原有速率下降。

Part3:pt-archiver

真凶找到了，我们怎么办，别急，使用pt-archiver。pt-archiver工具是percona工具集的一员，是归档MySQL大表数据的最佳轻量级工具之一。他可以实现分chunk分批次归档和删除数据，能避免一次性操作大量数据带来的各种问题。

闲话不多说，一向本着实战的原则，我们直接上命令：

pt-archiver --source

h=c3-helei-db01.bj,D=helei,t=tablename_v2,u=sys_admin,p=MANAGER

--where 'id<2577754125' --purge --progress 10000 --limit=10000

--no-check-charset --txn-size=10000 --bulk-delete --statistics --max-lag=20

--check-slave-lag c3-helei-db02.bj

简单说下常用的参数：  source目标节点

where条件

purge删除source数据库的相关匹配记录

progress每处理多少行显示一次信息

limit每次取出多少行处理

no-check-charset不检查字符集

txn-size每多少行提交一次

bulk-delete并行删除

statistics结束后输出统计信息

本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助，更多内容请关注职坐标数据库MySQL数据库频道！