MYSQL的ibtmp1文件太大问题及解决办法

ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在支持大文件的系统这个文件大小是可以无限增长的。

临时表释放后，空间会释放，但是磁盘空间不会释放，空闲空间可以被复用。释放磁盘空间只能重启。

ibtmp1增长的原因

ibtmp1增长主要与SQL有关，尤其是大量的分组聚合，排序，join查询SQL.通常如下情况会造成iptmp1上涨：

1.查询语句会先查询temp_table_size(内存分配)的量，当临时存储的量超过这个参数限制时，就会在iptmp1中申请占用空间。

2.select order group by GROUP BY 无索引字段或group by + order by 的子句字段不一样时。

3.select (select) 子查询

4.insert into select ... from ... 表数据复制

5.select union select 联合语句

临时表空间相关的参数:tmp_table_size ；max_heap_table_size ；innodb_temp_data_file_path

#查看ibtmp1文件大小

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE/1024/1024

AS TotalSize_MB, DATA_FREE/1024/1024 as FreeSize_MB, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES

WHERE TABLESPACE_NAME = 'innodb_temporary';

解决办法

1.限制ibtmp1文件大小：innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

2.优化SQL，避免使用临时表。

3.重启mysql实例释放ibtmp1文件

宝塔设置定时重启数据库方法

新建SHELL脚本，填入以下代码：service mysqld restart

常见的使用tmp临时表空间的场景

9、评估多表UPDATE语句。

实例分析

1、问题现象

Mysql服务磁盘空间告警，经过排查发现ibtmp1文件非常大，已经超过TB级别，其他能清理的数据已经做了清理。
ll -h ibtmp1-rw-r----- 1 mysql mysql 1.2T Aug 15 16:17 ibtmp1

2、问题分析

3、解决方案

重启数据库，释放临时表空间，同时为了避免临时表空间再次膨胀，可以将其设置一个最大的值。
--操作流程：
1）关闭数据库实例。
关闭后ibtmp1文件会自动清理。
2）修改my.cnf配置文件，限制tmp表空间的大小。
为了避免ibtmp1文件无止境的暴涨导致再次出现此情况,可以修改参数，限制其文件最大尺寸。
如果文件大小达到上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）。
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G # 12M代表文件初始大小，50G代表最大size
3）启动mysql服务，查一下是否生效。
show  variables like 'innodb_temp_data_file_path';+----------------------------+-------------------------------+| Variable_name | Value |+----------------------------+-------------------------------+| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:50G |+----------------------------+-------------------------------+

临时表使用的几点建议