ibtmp1 占用空间过大分析

一 问题描述
描述 因为ibtmp1 占用空间过大,导致磁盘使用率100%
2. ibtmp1 的大小是由参数 innodb_temp_data_file_path
默认是初始化值12M，自动扩展
mysql> show variables like '%innodb_temp_data_file_path%'
    -> ;
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| 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 联合语句
6. 官方描述的特别点


默认情况下，临时表空间数据文件是自动扩展的，并且会根据需要增加其大小以容纳磁盘上的临时表。例如，如果某个操作创建的临时表大小为20MB，则临时表空间数据文件（在创建时默认为12MB）会扩展大小以容纳该表。删除临时表后，释放的空间可用于新的临时表，但数据文件保持扩展大小。


也就是对应的会话产生了临时表大小，如果小于 ibtmp，则ibtmp 不会变大，如果大于 ibtmp，则ibtmp也会变大，不会截断为默认的12M


三 解决办法
1.限制ibtmp1文件大小：innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G


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


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




参考链接
https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html


复制