当数据库实例磁盘空间使用率比较大时,我们优先选择的方法一定是将可以清理的无用数据进行清理,如果实在是没有数据可清理,我们才会考虑对磁盘进行扩容,必将扩容磁盘要!花!钱!本篇主要将一下空间碎片化清理的一个案例。
一、发现问题
每日巡检数据库时,发现某一实例的数据量增长比较明显,起初并没有太在意,以为是业务上的正常增长。(切记,以后发现类似问题,一定要向业务方确认,不要自己瞎猜)。随着日子一天天过去,终于,磁盘空间的使用率达到了二级警戒线。此时我们再去观察近一个月的空间增长趋势,从19号开始磁盘空间就开始按照这个速度增长,如果一直这样下去将是很危险的!立马向业务方反馈该情况,并协助排查处理。
二、问题排查
对于空间使用率大的问题我们的处理思路一般两种:1.处理不必要的数据,释放空间 2. 磁盘扩容。我们优先使用第一个方法。
1、先排查具体是哪个schema占用空间比较大,可以看到db_rim库的数据相对其他库数据量明显要大很多
select table_schema,sum(DATA_LENGTH+ INDEX_LENGTH + DATA_FREE)/1024/1024 as data_total from tables group by table_schema order by total_size desc;
2、排查到库后我们继续往下排查具体哪些表比较大。我们根据表的数据空间占用大小倒叙查看top10。从这张图中我们可以看到两个问题。
1.rim_user_msg_log表的数据量已经达到了37G,这些数据都是有用的吗?对于早期的数据是否可以做清理或者归档来释放磁盘空间?
2.rim_user_msg_analyse表的data_free达到17G,碎片化非常严重,可以是对这些空间进行回收。
select table_name,DATA_LENGTH/1024/1024 as data_size , INDEX_LENGTH/1024/1024 as index_size, DATA_FREE/1024/1024 as free_size from tables where TABLE_SCHEMA='db_rim' order by data_size desc limit 5;
三、问题处理
1、rim_user_msg_log表的数据可以全部清理掉
2、rim_user_msg_analyse在业务低峰对其空间碎片进行回收
碎片化回收前:
碎片化回收后:
磁盘空间趋势上:
四、总结
1、如何回收空间碎片化?
1.optimize table tbl_name;
2.alter table tbl_name engine=innodb;
2、两者之间的异同?
相同点:两者都会对表的数据和索引数据进行重建,对空间碎片进行回收,提高I/O资源。两者执行的大致流程如下:
1)获取MDL写锁
2)降级为MDL读锁,重建表(时间消耗最长)
3)升级MDL写锁
4)释放MDL锁
这个过程怎么去验证呢?
Session1 | Session2 | Session3 |
---|---|---|
begin;update t1 set beizhu1='aaaaaa' where id<1000; | ||
optimize table t1;被阻塞 | 事物未提交 | 图1 |
rollback | ||
开始执行 | 事务已回滚释放锁资源 | 图2 |
执行中 | begin;update t1 set beizhu1='aaaaaa' where id<1000; | show processlist |
执行中 | 事务未提交 | 图3 |
执行中 | 事物提交 | |
已完成 | 图4 |
alter table engine=innodb;的操作步骤相同,只是Info列中显示信息不同。
图1
图2
图3
图4
不同点:optimize相当于recreate + analyze,alter相当于recreate