mysql ibtmp1使用空间突增反应的问题和处理--生产案例

mysql ibtmp1使用空间突增反应的问题和处理生产案例

一、问题现象：
某系统上线运行一段时间后管理员发现备份的数据才19G，但是ibtmp1 就149G，此文件占用大量空间。
提出以下几个问题：
1、是什么原因导致此问题
2、如何释放
3、如何避免此文件过大

系统背景架构介绍：
此系统使用mysql 5.7.17 版本，系统用于绩效考核数据分析，属于批处理系统。架构采用两个物理机Ha,加共享存储，mysql 放在共享存储上面。

二、是什么原因导致此问题
ibtmp1 介绍：
在mysql 5.7引用了ibtmp1,非压缩的、用户创建的临时表和磁盘上的内部临时表是在共享的临时表空间中创建。这innodb_temp_data_file_path配置选项定义临时表空间数据文件的相对路径、名称、大小和属性。
如果没有为指定值innodb_temp_data_file_path，默认行为是创建一个名为ibtmp1在innodb_data_home_dir略大于12MB的目录。
mysql> SELECT @@innodb_temp_data_file_path;
±-----------------------------+
| @@innodb_temp_data_file_path |
±-----------------------------+
| ibtmp1:12M:autoextend |
±-----------------------------+

创建时默认大小为12MB的临时表空间数据文件会自动扩展。删除临时表时，释放的空间可以重新用于新的临时表，但ibtmp1数据文件仍保持扩展大小。----ibtmp1不断变大的根本原因

近期系统现象：
跑批慢变慢
和项目组同事查询生产跑批作业执行时间日志记录，发现是某上周五变更后跑批变慢，分析原因是新加程序进行报表加工作业，处理了十几天多维度的报表。

ibtmp1不断的增长和系统属性和ibtmp1本身机制有很大关系，此系统属于批处理系统，对数据进行大量的加工处理，分组排序，相关sql 尤其大表涉及order by,group by发现有using temporary或者using filesort，每天反复批处理，也使得ibtmp1不断增长。

三、如何释放ibtmp1空间：
系统管理员询问能不能在线释放ibtmp1文件呢？
主要是重启数据库释放ibtmp1
机制如下：
临时表空间在正常关闭或中止初始化时被删除，并在每次服务器启动时重新创建。临时表空间在创建时会收到动态生成的空间标识。如果无法创建临时表空间，将拒绝启动。如果服务器意外停止，不会删除临时表空间。
在这种情况下，数据库管理员可以手动删除临时表空间或重新启动服务器，这将自动删除并重新创建临时表空间。

要回收临时表空间数据文件占用的磁盘空间，请重新启动MySQL服务器。重新启动服务器会根据定义的属性删除并重新创建临时表空间数据文件innodb_temp_data_file_path。

四、如何处理ibtmp1不断增长情况：

1、限制ibtmp1文件大小是否可行
影响当数据文件达到最大大小时，查询失败，并出现错误，表明ibtmp1已满，重新启动服务器，经过讨论不采用此种策略。

2、批处理优化
针对处理了十几天多维度的报表，跨越天数多，导致跑批时间变长，经讨论采用颗粒度小，数据集小灵活方式实现此功能。

3、sql语句优化
打开系统慢日志，log记录了执行时间超过120秒的sql语句，尤其是出现频率高的，对出现的慢sql语句多explain分析，尤其大表涉及order by,group by发现有using temporary或者using filesort请及时关注，分析是否需要建立合适的索引还是索引没有使用。尽量减少额外的排序，避免临时文件ibtmp1增长过快。

4、异常复杂场景迁移
如果后续需要多机构，多指标的报表建议放到其它数据库比中处理，mysql不擅长处理报表跑批。

五、ibtmp1现有情况：
经过反复优化，长期运行后，现有ibtmp1大小为51G，不再爆增，后续将不断再优化。