共计 3108 个字符，预计需要花费 8 分钟才能阅读完成。
作者：宗杨
爱可生产品交付团队成员，次要负责公司运维平台和数据库运维故障诊断。青睐数据库、容器等技术，喜好历史、追剧。
本文起源：原创投稿
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
一、事件背景
咱们的单干客户，驻场人员报告说一个 RDS 实例呈现磁盘有余的告警，须要排查。
告警信息：
告警内容：
数据库 data 磁盘有余，磁盘占用 80% 以上
数据库 binlog 磁盘有余，磁盘占用 80% 以上
二、排查过程
登陆告警的服务器，查看磁盘空间，并寻找大容量文件后，发现端口号为 4675 的实例长期表空间 ibtmp1 的大小有 955G，导致磁盘被应用了 86%；
猜想和库里执行长 SQL 有关系，产生了很多长期数据，并写入到长期表空间。
看到有这样一条 SQL，持续剖析它的执行打算；
很显著看到图中标记的这一点为应用了长期计算，阐明长期表空间的快速增长和它有关系。这条 SQL 进行了三表关联，每个表都有几十万行数据，三表关联并没有在 where 条件中设置关联字段，造成了笛卡尔积，所以会产生大量长期数据；而且都是全表扫描，加载的长期数据过多; 还波及到排序产生了长期数据；这几方面导致 ibtmp1 空间疾速爆满。
三、解决办法
和项目组沟通后，杀掉这个会话解决问题；
然而这个 SQL 停下来了，长期表空间中的长期数据没有开释；
最初通过重启 mysql 数据库，开释了长期表空间中的长期数据，这个只能通过重启开释。
四、剖析原理
通过查看官网文档，官网是这么解释的：
翻译：
依据官网文档的解释，在失常敞开或初始化停止时，将删除长期表空间，并在每次启动服务器时从新创立。重启可能开释空间的起因在于失常敞开数据库，长期表空间就被删除了，重新启动后从新创立，也就是重启引发了长期表空间的重建，从新初始化，所以，重建后的大小为 12M。
从谬误日志里能够验证下面的观点：
五、官网对于 ibtmp1 大小的阐明
六、如何防止
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 须要分外关注。
七、其余补充
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 中；
试验验证：将之前的查问后果保留成长期表，对应会话是 45 号，通过查看对应字典表，可知 45 号会话应用了 temp_8.ibt 这个表空间，通过把查问保留成长期表，能够用到会话长期表空间，如下图：
下一步杀掉 45 号会话，发现 temp_8.ibt 空间开释了，变为了初始大小，状态为非流动的，证实在 mysql8.0 中能够通过杀掉会话来开释长期表空间。
总结：在 mysql5.7 时，杀掉会话，长期表会开释，然而仅仅是在 ibtmp 文件里标记一下，空间是不会开释回操作系统的。如果要开释空间，须要重启数据库；在 mysql8.0 中能够通过杀掉会话来开释长期表空间。
八、参考文档
https://dev.mysql.com/doc/ref…://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.htmlhttp://mysql.taobao.org/month…