MySQL5.7ibtmp暴涨导致磁盘打满问题

背景

MySQL从5.7.1版本开始，临时表的存放由之前的临时文件可替换成采用独立的临时表空间形式，命名为ibtmp1；由innodb_temp_data_file_path参数可配置临时表空间相关参数。MySQL将临时表空间从系统表空间 (system tablespace) 文件中独立出来，该共享临时表空间用于存储非压缩 InnoDB 临时表 (non-compressed InnoDB temporary tables)、关系对象 (related objects)、回滚段 (rollback segment) 等数据。

问题现象

某客户的mysql5.7的RDS实例磁盘使用率报警：HOME磁盘剩余空间不足，磁盘使用量已达95%。

原因分析

• 查看主库用户数据目录下磁盘占比95%，但是备库占比65%，因此判定主库有额外数据占用磁盘。
• 进一步排查：在主库数据目录下du -sh ./*命令查看文件目录大小，发现ibtmp1临时表空间高达167G。
• 进而排查发现用户连接中有1000+s的笛卡尔积查询，导致产生了大量的临时数据到临时表空间中。

解决方案

• 征得用户同意后kill查询语句，防止查询语句继续增大ibtmp1，导致磁盘打满业务不可用。
• 由于临时表空间扩大后不能自动收缩，因此需要用户重启RDS实例释放临时表空间。

案例复现

测试SQL语句：

1[baidu_dba] mysql>select count(*) from tb_1 order by id,c,pad,k;

参数设置：

1[baidu_dba]mysql>show variables like 'innodb_temp_data_file_path';
2+----------------------------+-----------------------+
3| Variable_name              | Value                 |
4+----------------------------+-----------------------+
5| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
6+----------------------------+-----------------------+
71 row in set (0.01 sec)

临时表空间初始化默认12M：

执行较大查询后生成的临时表在临时表空间中，使得表空间变大：

由于RDS实例中默认innodb_data_file_path参数没有设置最大临时表空间大小，因此会造成临时表空间过大导致磁盘打满的现象。

结论建议

1. MySQL5.7查询语句注意查询的方式是否合理（避免产生笛卡尔积导致临时表不断增大）；查询如果需要用到order by等语句导致使用到临时表空间排序，注意控制结果集大小，避免临时表中结果集过大导致临时表空间过大。
2. 对临时表的总结

• 对于MySQL 5.6

当开启参数innodb_file_per_table时，临时表在tmpdir指定的目录生产临时表数据文件和表结构文件，元数据存在ibdata1中。 当关闭参数innodb_file_per_table时，临时表在ibdata1中产生临时表数据，并且退出session后ibdata1大小不会缩小。

• 对于MySQL 5.7

当临时表是非压缩表时，临时表数据存储在ibtmp1临时表空间中，表结构数据放在tmpdir下； 当临时表是压缩表时，临时表数据、结构存储在tmpdir下。