MySQL 临时表空间数据过多致使磁盘空间不足的问题排查

1、事件背景

咱们的合做客户，驻场人员报告说一个 RDS 实例出现磁盘不足的告警，须要排查。html

告警信息：mysql

告警内容：
数据库 data 磁盘不足，磁盘占用 80% 以上
数据库 binlog 磁盘不足，磁盘占用 80% 以上sql

2、排查过程

登录告警的服务器，查看磁盘空间，并寻找大容量文件后，发现端口号为 4675 的实例临时表空间 ibtmp1 的大小有 955G，致使磁盘被使用了 86%；数据库

猜想和库里执行长 SQL 有关系，产生了不少临时数据，并写入到临时表空间。服务器

看到有这样一条 SQL，继续分析它的执行计划；less

很明显看到图中标记的这一点为使用了临时计算，说明临时表空间的快速增加和它有关系。这条 SQL 进行了三表关联，每一个表都有几十万行数据，三表关联并无在 where 条件中设置关联字段，造成了笛卡尔积，因此会产生大量临时数据；并且都是全表扫描，加载的临时数据过多;还涉及到排序产生了临时数据；这几方面致使 ibtmp1 空间快速爆满。运维

3、解决办法

和项目组沟通后，杀掉这个会话解决问题；优化

可是这个 SQL 停下来了，临时表空间中的临时数据没有释放；spa

最后经过重启 mysql 数据库，释放了临时表空间中的临时数据，这个只能经过重启释放。操作系统

4、分析原理

经过查看官方文档，官方是这么解释的：

翻译：

根据官网文档的解释，在正常关闭或初始化停止时，将删除临时表空间，并在每次启动服务器时从新建立。重启可以释放空间的缘由在于正常关闭数据库，临时表空间就被删除了，从新启动后从新建立，也就是重启引起了临时表空间的重建，从新初始化，因此，重建后的大小为 12M。

从错误日志里能够验证上面的观点：

5、官网对于 ibtmp1 大小的说明

6、如何避免

1. 对临时表空间的大小进行限制，容许自动增加，但最大容量有上限,本例中因为 innodb_temp_data_file_path 设置的自动增加，但未设上限，因此致使 ibtmp1 有 955G。正确方法配置参数 innodb_temp_data_file_path：[mysqld]innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M参考官方文档：

设置了上限的大小，当数据文件达到最大大小时，查询将失败，并显示一条错误消息，代表表已满，查询不能往下执行，避免 ibtmp1 过大。

2. 在发送例如本例中的多表关联 SQL 时应确保有关联字段并且有索引，避免笛卡尔积式的全表扫描，对存在 group by、order by、多表关联的 SQL 要评估临时数据量，对 SQL 进行审核，没有审核不容许上线执行。

3. 在执行前经过 explain 查看执行计划，对 Using temporary 须要格外关注。

7、其余补充

1> 经过字典表查看执行的 SQL 产生临时表、使用临时表空间的状况：查询字典表：sys.x$statements_with_temp_tablesselect * from sys.x$statements_with_temp_tables where query like 'select%' and db='test' order by tmp_tables_to_disk_pct,disk_tmp_tables descG;

查询字典表：sys.statements_with_temp_tablesselect * from sys.statements_with_temp_tables where query like 'select%' and db='test' order by tmp_tables_to_disk_pct,disk_tmp_tables descG;

这两个表查询的结果是同样的，各列含义以下：
query：规范化的语句字符串。
db：语句的默认数据库， NULL 若是没有。
exec_count：语句已执行的总次数。
total_latency：定时出现的语句的总等待时间。
memory_tmp_tables：由该语句的出现建立的内部内存临时表的总数。disk_tmp_tables：由该语句的出现建立的内部磁盘临时表的总数。
avg_tmp_tables_per_query：每次出现该语句建立的内部临时表的平均数量。
tmp_tables_to_disk_pct：内部内存临时表已转换为磁盘表的百分比。
first_seen：第一次看到该声明的时间。
last_seen：最近一次发表该声明的时间。
digest：语句摘要。

参考连接：https://dev.mysql.com/doc/ref...
经过字典表 tmp_tables_to_disk_pct 这一列结果可知，内存临时表已转换为磁盘表的比例是 100%，说明经过复现这个查询，它的临时计算结果已经都放到磁盘上了，进一步证实这个查询和临时表空间容量的快速增加有关系。

2> 对于 mysql5.7 中 kill 掉运行长 SQL 的会话，ibtmp1 容量却没有收缩问题的调研；来源连接：http://mysql.taobao.org/month...

从文章中的解释看，会话被杀掉后，临时表是释放的，只是在 ibtmp1 中打了删除标记，空间并无还给操做系统，只有重启才能够释放空间。

3> 下面，进一步用 mysql8.0 一样跑一下这个查询，看是否有什么不一样;mysql 版本：8.0.18

当这个 sql 将磁盘跑满以后，发现与 5.7 不一样的是这个 SQL 产生的临时数据保存到了 tmpdir，mysql5.7 是保存在 ibtmp1 中，并且因为磁盘满，SQL 执行失败，很快磁盘空间就释放了；

问题：如何使用到 8.0 版本的临时表空间？

经过查看 8.0 的官方文档得知，8.0 的临时表空间分为会话临时表空间和全局临时表空间，会话临时表空间存储用户建立的临时表和当 InnoDB 配置为磁盘内部临时表的存储引擎时由优化器建立的内部临时表，当会话断开链接时，其临时表空间将被截断并释放回池中；也就是说，在 8.0 中有一个专门的会话临时表空间，当会话被杀掉后，能够回收磁盘空间；而原来的 ibtmp1 是如今的全局临时表空间，存放的是对用户建立的临时表进行更改的回滚段，在 5.7 中 ibtmp1 存放的是用户建立的临时表和磁盘内部临时表；
也就是在 8.0 和 5.7 中 ibtmp1 的用途发生了变化，5.7 版本临时表的数据存放在 ibtmp1 中，在 8.0 版本中临时表的数据存放在会话临时表空间，若是临时表发生更改，更改的 undo 数据存放在 ibtmp1 中；