1、 问题现象

ibtmp1 临时表大小达到了7G。

du -sh ibtmp1 
7.3G    ibtmp1

2、 问题分析

2.1 查看自动扩展参数innodb_temp_data_file_path

 

show variables like '%innodb_temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)
SET GLOBAL innodb_fast_shutdown = 0;  
#InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down

 

2.2 修改自动扩展参数innodb_temp_data_file_path

cat /etc/my.cnf | grep temp
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:2G

2.3 重启mysqld服务，ibtmp表空间会自动释放

 

du -sh ibtmp1 
201M    ibtmp1