揪出MySQL磁盘消耗迅猛的真凶

已认证账号

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

为了提升数据库资源利用率，一个实例中，在不互相影响，保证业务高效的前提下，我们会将同一个大业务下的不同小业务放在一个实例中，我们的磁盘空间是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(）操作对线上造成的影响。

案例分析

与业务沟通了解后得知，该表可以清理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

简单说下常用的参数：

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

首先我们需要了解到使用bulk-delete时，sql是如下执行的：

mysql> delete from tablename_v2 where id >xxx and id < xxx limit 10000.

如果您之前关注过笔者的文章，应该知道，当使用了delete from xxx where xxx limit 语法时，会将binlog_format从mixed转为row，这样的话，删除的同时，binlog由于转为了row格式也在激增，这与我们的预期是不符的。

因此最终的命令为：

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  --statistics --max-lag=20
--check-slave-lag c3-helei-db02.bj

去掉了bulk-delete，这样的话就能够保证正常的delete，而不加limit，binlog不会转为row格式导致磁盘消耗继续激增。

对于Innodb引擎来说，delete操作并不会立即释放磁盘空间，新的数据会优先填满delete操作后的“空洞”，因此从监控来看就是磁盘不会进一步消耗了，说明我们的pt-archiver工具删除是有效的。

Part4:困惑

首先我们要知道，当你面对一张数据量庞大的表的时候，有些东西就会受限制，例如：

不能alter操作，因为这会阻塞dml操作。
对于本案例，空间本就不足，也不能使用pt-online工具来完成。

对于不能alter其实是比较要命的，比如开发要求在某个时间段尽快上线新业务，而新业务需要新增列，此时面对这么庞大的量级，alter操作会异常缓慢。

因此，笔者与研发沟通，尽快采用物理分表的方式解决这个问题，使用物理分表，清理表的操作就会很容易，无需delete，直接drop 老表就可以了。其次，物理分表让alter语句不会卡住太久，使用pt-online工具也不会一次性占据过多的磁盘空间诱发磁盘空间不足的告警。

再有就是迁移TiDB，TiDB相较MySQL更适合存储这类业务。

Part5:再谈binlog_format

我们选取其中高峰期的binlog发现其update操作转为了row格式，记录了所有列变更前后的所有信息，而binlog中并未出现update xxx limit这种操作，那又会是什么引发的row格式记录呢？

这里这篇文章又抛出一个新的案例，在官网那篇何时mixed转row格式中又一个没有记录的情况

官方文档：

When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:
When a DML statement updates an NDBCLUSTER table.
When a function contains UUID().
When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.
When any INSERT DELAYED is executed.
When a call to a UDF is involved.