一个关于MySQL中隐式的临时表完全耗尽磁盘空间的问题

发布时间:2020-05-13 浏览次数:312

背景

最近遇到了一个关于 MySQL 完全耗尽磁盘空间的问题。这是个严重的问题,因为会导致服务不断地中断,客户会不断的重启服务并在不久后再次宕机。

究竟发生了什么?在本文中,将解释原因并找到解决方案。


隐式临时表

MySQL 需要创建隐式临时表来解决某些类型的查询。往往查询的排序阶段需要依赖临时表。例如,当您使用 GROUP BY,ORDER BY 或DISTINCT 时。这样的查询分两个阶段执行:首先是收集数据并将它们放入临时表中,然后是在临时表上执行排序。

对于某些 UNION 语句,不能合并的 VIEW,子查询时用到派生表,多表 UPDATE 以及其他一些情况,还需要使用临时表。

如果临时表很小,可以到内存中创建,否则它将在磁盘上创建。MySQL 在内存中创建了一个表,如果它变得太大,就会被转换为磁盘上存储。内存临时表的最大值由 tmp_table_size 或 max_heap_table_size 值定义,以较小者为准。MySQL 5.7 中的默认大小为 16MB。如果运行查询的数据量较大,或者尚未查询优化,则可以增加该值。设置阈值时,请考虑可用的 RAM 大小以及峰值期间的并发连接数。你无法无限期地增加变量,因为在某些时候你需要让 MySQL 使用磁盘上的临时表。

注意:如果涉及的表具有 TEXT 或 BLOB 列,则即使大小小于配置的阈值,也会在磁盘上创建临时表。


临时表的存储引擎

在 MySQL 5.6 之前,所有磁盘上的临时表都默认创建为 MyISAM 类型。临时表是在内存中,还是在磁盘上创建,具体取决于配置,并在查询结束时立即删除。从 MySQL 5.7 开始,它们默认创建为 InnoDB 类型。

新默认值可提升整体性能,大多数情况下都是最佳选择。

可以使用新的配置项来设置临时表的存储引擎:internal_tmp_disk_storage_engine ,可选值为 InnoDB(默认)或 MyISAM。


InnoDB 类型的临时表存在的潜在问题

尽管使用 InnoDB 是性能最佳的,但可能会出现新的潜在问题。在某些特定情况下,您可能会出现磁盘耗尽和服务器中断。

与数据库中的任何其他 InnoDB 表一样,临时表具有自己的表空间文件。新文件与通用表空间一起位于数据目录中,名称为 ibtmp1。它存储所有 tmp 表。不运行手动运行 OPTIMIZE TABLE,表空间文件就会不断增长。如果你不能使用 OPTIMIZE,那么唯一能将 ibtmp1 大小缩小为零的方法,就是重新启动服务器。

幸运的是,即使文件无法减小,在执行查询后,临时表也会自动删除,表空间可回收使用。

现在,我们想一想以下情境:

  • 存在未优化的查询,需要在磁盘上创建非常大的的临时表

  • 存在优化的查询,但他们正在磁盘上创建非常大的临时表,因为你正在对此数据集进行计算(统计,分析)

  • 高并发连接时,运行相同的查询,伴随临时表的创建

  • 没有很多可用空间

在这些情况下,文件 ibtmp1 大大增加,很容易耗尽可用空间。这种情况每天发生几次,并且必须重启服务器才能完全缩小 ibtmp1 表空间。使用不可收缩的文件可以轻松耗尽磁盘空间!

那么,如何避免磁盘耗尽和中断呢?


简单的解决方案:使用更大的磁盘

虽然可以暂时解决问题,但这不是最佳解决方案。实际上,您可以通过逐步增加磁盘大小,来猜测具体需要的空间。如果环境位于云中,或者在非常大的虚拟平台,这很容易实现。但是使用这种解决方案,您可能会面临不必要的开支。

您还可以通过设置以下配置变量将 ibtmp1 文件移动到专用大型磁盘上:

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

需要重启 MySQL 。注意,必须将路径指定为相对于数据目录。


设置 ibtmp1 大小的上限

例如:

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

在这种情况下,文件不能超过 10GB。可以降低宕机概率,但也是一个危险的解决方案。当数据文件达到最大值时,会查询失败并显示一个错误,提示表已满。


退回 MyISAM 将临时表存储在磁盘上

这个解决方案似乎违反直觉,但它可能是快速避免中断的最佳方法,并保证使用所有需要的临时表。

    internal_tmp_disk_storage_engine = MYISAM

由于变量是动态的,您也可以在运行时设置它:

    SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;

回到 MyISAM,您将大大降低写满磁盘空间的可能性。实际上,临时表将创建到不同的文件中,并在查询结束时立即删除。

虽然总是有可能看到相同的问题,以防你可以在同一时间运行查询或非常接近。在我的实际案例中,这是避免所有中断的解决方案。


优化你的查询

在将存储引擎退回到 MyISAM 以减轻中断发生后,必须花时间分析查询。

目标是减小磁盘上临时表的大小。本文的目的不是解释如何调查查询,而是可以依赖慢速日志,像 pt-query-digest 和 EXPLAIN 这样的工具。

一些技巧:

  • 在表上创建缺少的索引

  • 如果不需要,可以在查询中添加更多过滤条件以更少收集的数据

  • 重写查询以优化执行计划

  • 可以在应用程序中使用队列管理器来序列化它们的执行或减少并发性

但希望在所有优化之后,您可以返回将临时存储引擎设置为 InnoDB 以获得更好的性能。


结论

有时这些改进会产生意想不到的副作用。用于磁盘上临时表的 InnoDB 存储引擎是一个很好的改进,但在某些特定情况下,例如,如果您有未优化查询和很少的可用空间,则可能因“磁盘已满”错误而中断。将 tmp 存储引擎退回到 MyISAM 是避免中断的最快方法,但是为了返回到 InnoDB,查询的优化是更重要的事情。更大或专用的磁盘也可能有所帮助。但这是一个微不足道的建议。

上一篇: tcmalloc解决MySQLd实例引发的cpu过高问题

下一篇: MySQL中遇到binlog 异常暴涨分析

产品试用 产品试用
400-820-6580 免费电话