本文主要向大家介绍了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数据库频道!