MySQL: 隐式临时表导致磁盘空间耗尽

最新推荐文章于 2024-06-03 19:48:30 发布

foxytale

最新推荐文章于 2024-06-03 19:48:30 发布

阅读量1k

点赞数 4

文章标签： mysql 数据库临时表

原文链接：https://www.percona.com/blog/mysql-disk-space-exhaustion-for-implicit-temporary-tables/

版权

隐式临时表

mysql需要创建隐式临时表解决一些语句查询。这些语句在排序阶段需要大量的时间在生成临时表上。举例来说，当你用到GROUP BY, ORDER BY 或 DISTINCT关键字时。这些语句执行了2个阶段：第一阶段生成数据放入临时表中，第二阶段是执行临时表上的排序。

临时表也会用在一些UNION语句的场景，像是VIEW无法合并，派生表使用子查询，多表update等等。

当临时表足够小，他会创建到内存中，否则就会在磁盘上，当然不用说，内存临时表要快得多。mysql先创建内存中的临时表，当他变得太大时，再将他转换成磁盘表。内存临时表的最大值受tmp_table_size或max_heap_table_size中的最小值控制。5.7中默认为16MB。当你的语句涉及大量数据，或者你没有优化他，你可以增加这些变量的值。设置阈值时应当考虑提供的内存空间和并发的连接数峰值。你不能无限制的使用该变量，有些时间mysql需要使用磁盘上的临时表。

注意：当临时表中有使用TEXT或者BLOB列时会创建在磁盘上，规模小于设置的值也一样。

临时表存储引擎

直到MySQL 5.6，所有的磁盘临时表使用MyISAM引擎。创建到内存或者磁盘以参数为准。然后会在语句结束时第一时间删除。从MySQL 5.7开始，默认的磁盘临时表引擎为InnoDB 。你可以信赖这些先进特性。

新的默认值对于性能来说是最好的选项，而且可以被用到大多数场景中。

提供了新的变量用于设置默认的临时表引擎：internal_tmp_disk_storage_engine. 这个变量可以被设置为innodb (默认) 或 myisam。

`InnoDB` 临时表的潜在问题

虽然使用InnoDB是获得了最好的性能，但是潜在的问题也随之出现。在一些特定的场景，你会遇到磁盘耗尽服务器停止。

像数据库中其他InnoDB的表一样，临时表使用他们自己的表空间文件。这个新文件和通常的表空间文件都在data目录下，命名为ibtmp1。他存储了所有的临时表。表空间文件无法被收缩，只要你不在手工执行OPTIMIZE TABLE，他会持续的增长。ibtmp1 也是一样，因为无法使用OPTIMIZE，重启server唯一缩小ibtmp1 到0的办法。

幸运的是，虽然他无法被收缩，当语句执行结束会自动清理出空间，这些空间可以被新来的语句重复使用。

我们来思考以下这些场景：

你没有优化语句，然后语句需要创建非常大的磁盘临时表。
你优化了语句，但是他仍要创建非常大的磁盘临时表，因为你正在一个非常大的数据集上进行专门的计算。(统计信息，分析)
你有大量的并发连接，跑着相同的语句，需要使用临时表。
你没有很大的磁盘空间。

这些场景中，很容易理解ibtmp1 文件增长非常多然后轻松的耗尽空间。这事一天能发生好多次，然后系统只能反复重启来收缩 ibtmp1表空间。

并发查询不是强制性的。当语句使用了大量的临时表，会花费数秒或数分的时间，这些语句完全可以在不同的时间发起，这个执行的时候前一个还没执行完。同时，你要考虑每个连接创建自己的临时表，这样相同的语句会直接复制临时表到表空间。在不能收缩的文件上，耗尽空间会变得十分容易！

所以说，我们如何避免磁盘耗尽停机呢？

笨的解法：使用大的磁盘

这个笨办法可以解决问题，但不是最优解。事实上，你不好确认磁盘需要的大小。你可以一点一点增加磁盘来试，这种在在大虚拟机或者云平台上简单。但是在实体机环境就不简单了。

不过这种法子，你可以规避不需要的风险，记住吧。.

你也可以把ibtmp1 文件移动到大的磁盘上。使用以下设置:

[mysqld]
innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend

MySQL 需要重启

注意，路径必须写一个关联data目录的路径。

设置ibtmp1 上限值

举例

[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

这个场景中，文件不会超过10GB，你可以规避停机风险，但是这也是个危险的解法。但你的数据文件达到10GB，语句会因为表满了而失败。这可能对你的应用不好。

回退到MyISAM磁盘临时表

这个法子看起来反常，但是是最好的避免短暂停服办法，这个也保证以使用所有需要的临时表。

你可以在使用如下变量在my.cnf:

internal_tmp_disk_storage_engine = MYISAM

这是个动态变量，你也可以运行时设置。

SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;

回退到MyISAM, 你会极大降低耗尽空间的概率。事实上，临时表空间文件创建为不同的文件，然后在语句结束及时删除。不需要考虑永远增长文件了。

虽然总是有可能看到相同的问题，避免在同一时间或者相近时间执行语句。在我的真实案例中，这是避免所有停机的解决方案。

优化你的语句

这事最重要的事。当你回退到MyISAM殷勤来处理停机的情况，你也要花时间优化语句。

目标是降低磁盘上排序临时表的规模。本文的目的不是讲如何分析这些sql的，你可以通过满日志， pt-query-digest 工具， EXPLAIN工具来解决。.

小贴士：

创建缺失的索引。
语句中增加过滤条件以减少数据量，把不需要的过滤掉。
重写语句来优化执行计划。
如果你确实有大量的语句，你可以在应用中用队列管理器序列化执行来减少并发。

这是最长的步骤。但是你也有希望在优化完后将临时表引擎改回InnoDB 以求更好的性能。

结论

一些时候进步会带来未预想的副作用。InnoDB引擎的磁盘历史表是很好的进步,但是在一些特定场景里，举例来说，如果你没有优化语句，然后你只有很少的磁盘空间，你就会因为”磁盘满“错误而停机。回退临时表引擎到MyISAM最快的办法避免停机, 但是优化语句更加重要，应当尽快去做，好让引擎可以改回 InnoDB。是的，笨办法大磁盘或者专属的磁盘一样有用。这虽然是个笨建议，但是真的很有用。

顺便一说，这个特性有一些情况: MySQL Bugs: #82556: Abort query when disk is filling up due to ibtmp1 file

原文地址：MySQL: Disk Space Exhaustion for Implicit Temporary Tables

~~翻译员碎碎念：这个文章是19年7月的，有点老黄历，而且8.0以后的临时表又迎来诸多特性没有提到，中间也有很多曲折，容我盘一个大合集出来。~~