空间碎片化处理

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

一、发现问题

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


下一篇:浅谈MySQL的七种锁