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


该参数是5.7的新特性。

针对临时表及相关对象引入新的“non-redo” undo log,存放于临时表空间。该类型的undo log非 redolog 因为临时表不需崩溃恢复、也就无需redo logs,但却需要 undo log用于回滚、MVCC等。

默认的临时表空间文件为ibtmp1,位于数据目录在每次服务器启动时被重新创建,可通过innodb_temp_data_file_path指定临时表空间。

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


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

当EXPLAIN 查看执行计划结果的 Extra 列中,如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到:
1、UNION查询(MySQL 5.7起,执行UNION ALL不再产生临时表,除非需要额外排序);
2、用到TEMPTABLE算法或者是UNION查询中的视图;
3、ORDER BY和GROUP BY的子句不一样时;
4、表连接中,ORDER BY的列不是驱动表中的;
5、DISTINCT查询并且加上ORDER BY时;
6、SQL中用到SQL_SMALL_RESULT修饰符的查询;
7、FROM中的子查询(派生表);
8、子查询或者semi-join时创建的表;

9、评估多表UPDATE语句。

实例分析

1、问题现象

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

2、问题分析

通过了解了ibtmp1是非压缩的innodb临时表的独立表空间,而且检查配置发现为ibtmp1:12M:autoextend,也就是支持无限扩大的,才导致了ibtmp1文件增加打了非常大的一个数值。

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 |+----------------------------+-------------------------------+

临时表使用的几点建议

1、设置innodb_temp_data_file_path选项,设定文件最大上限(innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:200M),超过上限时,需要生成临时表的SQL无法被执行(一般这种SQL效率也比较低,可借此机会进行优化)。
2、检查INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO,找到最大的临时表对应的线程,kill之即可释放,但ibtmp1文件则不能释放(除非重启)。
3、择机重启实例,释放ibtmp1文件,和ibdata1不同,ibtmp1重启时会被重新初始化而ibdata1则不可以。
4、定期检查运行时长超过N秒(比如N=300)的SQL,考虑清理,避免垃圾SQL长时间运行影响业务。

本文作者:徐林

本文来源:IT那活儿(上海新炬王翦团队)