空间碎片化处理

2021-12-29 04:17:15

当数据库实例磁盘空间使用率比较大时，我们优先选择的方法一定是将可以清理的无用数据进行清理，如果实在是没有数据可清理，我们才会考虑对磁盘进行扩容，必将扩容磁盘要！花！钱！本篇主要将一下空间碎片化清理的一个案例。

一、发现问题

每日巡检数据库时，发现某一实例的数据量增长比较明显，起初并没有太在意，以为是业务上的正常增长。（切记，以后发现类似问题，一定要向业务方确认，不要自己瞎猜）。随着日子一天天过去，终于，磁盘空间的使用率达到了二级警戒线。此时我们再去观察近一个月的空间增长趋势，从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