无语了，直到今天，我才揪出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 --sourceh=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

简单说下常用的参数：

文章插图

Warning:警告这里就又有个小坑了，的确，我们使用bulk-delete参数能够增加删除速率，相比不使用bulk-delete速度能够提升10倍左右，但问题也就显现出来，在使用上述命令期间，发现binlog每秒写入量激增，这又回到了我们说的，哪些情况会导致binlog转为row格式。