乐趣区

关于运维:MySQL空间碎片化问题处理

jiezi

一、空间碎片化重大案例分享

1.1 问题形容

实例磁盘空间近 1 个月上涨趋势显著，次要是个别日志表存储较大且局部表存在空间碎片化的景象。

1.2 解决流程

1、通过日常巡检以及监控发现某实例磁盘空间近 1 月上涨趋势显著

2、在询问业务方是否为失常的业务增长外，查看该实例 top 表空间的表，排查是否存在异样较大的表

1）查看数据存储量较大的 schema

SELECT table_Schema  , round(SUM((DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024), 2) AS Total_Size FROM tables GROUP BY table_schema ORDER BY Total_Size DESC;

2）查看具体 schema 下 top 表的空间应用状况 

SELECT table_schema, table_name  , round(DATA_LENGTH / 1024 / 1024, 2) AS Data_Size , round(INDEX_LENGTH / 1024 / 1024, 2) AS Index_Size , round(DATA_FREE / 1024 / 1024, 2) AS Free_Size FROM tables WHERE table_schema = 'db_rim' ORDER BY Data_Size DESC LIMIT 10;

3、从以上截图中咱们能够失去信息：db_rim 库库下，rim_user_msg_log 表的数据量很大，可查看是否可对该表中有效的数据进行清理？rim_user_msg_analyse 这张表的碎片化将尽 17G，可思考对这些碎片化空间进行回收

4、最终采取的解决办法为：rim_user_msg_log 表间接清空，rim_user_msg_analyse 进行碎片化回收

1）清理前

2）清理后

3）实例磁盘空间应用趋势

二、表数据量过大清理案例

2.1 问题形容

通过对 TOP 表数据量的监控，咱们能够看到 sys_rest_server_log 的数据量曾经达到 7000w, 除空间占用外，咱们更关注的是该表对数据库带来的性能隐患。若业务上或者人为不当的查问该表数据，其操作的资源耗费在肯定水平上肯定会对失常业务造成影响。

2.2 问题解决

1、该表以后的体现次要有两点，一点是某刹时写入量极大，另外一点是表日增可达到 300w 记录数。对于数据库而言这种表行为或业务设计是不合理的，咱们须要搞清楚该表到底是用来记录哪些信息，业务设计是否正当？

通过观察表数据以及与相干开发人员沟通，咱们得悉该表会记录利用端所有的接口调用信息，咱们所察看到的数据增长尖峰也正是因为业务量上涨而导致该表霎时并发写入大量日志信息。

2、对于这种大数据量的日志记录信息，倡议最好应用 ELK 这种日志剖析服务，而不是应用数据库进行存储

3、限于开发人员开发能力无限，无奈革新业务架构。针对该表日增长、霎时增长都极大的状况，咱们与开发人员沟通该表记录的数据是否可从源端上进行缩小。只记录重要信息、非重要信息的接口日志入库进行屏蔽

4、确定表数据记录可从最基本增产后，咱们仍须要对目前 7000w 的数据进行解决，将历史有效数据进行清理，并制订适合的数据清理策略

5、有效历史数据清理 / 数据清理策略 

1、创立备份表：create table sys_rest_server_log_bak_0228_1 like sys_rest_server_log;  
2、表名替换 将源表 sys_rest_server_log 重命名为备份表，备份表 sys_rest_server_log_bak_0208_1 重命名为源表 sys_rest_server_log，业务数据会写入空的新表，然而在 rename 期间这部分业务日志写入会失败【需关注】rename table sys_rest_server_log to sys_rest_server_log_bak_0228_2,sys_rest_server_log_bak_0228_1 to sys_rest_server_log;  
3、将备份表 sys_rest_server_log_bak_0208_2 中的依然须要的数据从新写入到 sys_rest_server_log，分批次写入，sys_rest_server_log 保留近 15 天数据。程序上可保障主键 fd_id 惟一，防止主键抵触。insert into sys_rest_server_log select * from sys_rest_server_log_bak_0228_2 where fd_start_time between 'xxx' and 'xxx';  
4、调整平台数据清理策略，将服务运行日志备份天数改为 15 天，革除服务运行日志备份天数改为 30 天  
5、sys_rest_server_log_bak_0228_2 表保留 80+ 天记录数据，临时能够不做清理，等程序保护的 sys_rest_server_log_backup 有齐备的数据后，将 sys_rest_server_log_bak_0228_2 表革除；

三、总结演绎

2.1 如何判断表碎片化问题是否重大

表数据的频繁更新容易对表空间造成肯定的碎片化，咱们能够通过 information_schema.table 表中的 data_free 字段判断该表碎片化是否重大。

2.2 如何解决碎片化问题 

optimize table tableName; alter table tablName engine=innodb;

2.3 大表历史数据分明过程中的关注点 

1、通过创立 bak 表与源表进行 rename 替换，rename 期间会造成业务对该表数据写入失败 
2、bak 表创立的时需关注 primayr key 
1）程序上保障主键惟一可不须要关注 
2）若主键应用自增长，倡议在创立 bak 表创立时进步以后的 auto_increment 指，空余一部分 buffer 空间，保障后续将源表历史数据写入 rename 后的新表时主键不抵触 
3、对于此类常常须要进行数据清理的表，倡议应用分区表进行存储，后续对数据的解决仅仅须要删除指定分区

更多技术信息请查看云掣官网 https://www.dtstack.com/dtsmart/

退出移动版