MySQL 5.7 临时表空间文件ibtmp1暴增原因及解决方法

MySQL 5.7 临时表空间文件ibtmp1暴增原因及解决方法

1. MySQL临时表空间介绍

关于临时表、临时表空间的概念参考官方解释，临时表一种是用户通过create temporary table创建的显式临时表，另一种是复杂SQL执行时临时创建的隐式辅助表，当临时表需要存储的数据超过了tmp_table_size或max-heap-table-size中的较大值，那么临时表数据将会存储到磁盘，在MySQL 5.7中就是基于ibtmp1文件的临时表空间中。显式临时表数据和undo存于ibtmp1时，用户断开连接虽然释放了临时表，但实际的使用空间并不会释放，只有重启数据库才能真正释放这部分空间（这部分的功能bug在MySQL 8.0中得到解决）。另外，关于临时表的具体占用情况可以通过INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO查看。

MySQL 5.7中临时表空间通过innodb_temp_data_file_path参数控制，可以在配置文件my.cnf中根据需要设置临时表空间的路径、名称、大小，默认是ibtmp1:12M:autoextend，默认初始大小为12M，自增无上限，每次数据库重启之后会被删除重建。

mysql> show variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.01 sec)

2. 临时表空间文件ibtmp1暴增原因及解决方法

运维同事反应zabbix监测到数据库服务器/home使用率突然暴增到80%，通过查询发现是暴增源头是ibtmp1文件。其实在发现这个警告之前一个小时在这台机上做查询操作的同事反应查询卡死，原因为复杂查询与索引造成的死锁问题。后续排查定位问题是几个低效的SQL造成的，需要优化处理。

经查询ibtmp1已高达839G，解决办法只能是重启数据库释放这部分空间。这台数据库服务器为MySQL的备库，有一个项目读写分离的读操作放在这台机，先看下当前状态：

先修改一下innodb_temp_data_file_path参数，将自增无上限改为上限阈值100G。

[root@mysqlplus2 ~]# vim /etc/my.cnf
 innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:100G

stop slave然后重启数据库，重建备库连接：