MySQL ibtmp1 文件(隐式临时表)增长磁盘空间耗尽

by

最近遇到了一个很严重的问题，MySQL 的磁盘空间完全耗尽了,导致无法访问并崩溃。因此许多操作都报错。/var/lib/mysql 目录中一个名为 ibtmp1 的超大文件占用了硬盘空间。ibtmp1 是 MySQL 的一个临时工作文件。客户不得不不断重启服务器.

# ls -ltrlh | grep ibtmp1

-rw-r----- 1 vcap vcap  16G Feb  8 02:46 ibtmp1

关闭并重新启动 MySQL 服务器后，该文件将被删除，服务器将再次正常运行，直到该文件再次变得过大。

隐式临时表

MySQL 需要创建隐式临时表来解决某些类型的查询。大多数情况下，需要排序阶段的查询都需要依赖临时表。例如，当您使用GROUP BY、ORDER BY或DISTINCT 时。  此类查询分两个阶段执行：第一阶段是收集数据并将其放入临时表中，第二阶段是对临时表执行排序。

在某些UNION语句评估的情况下，对于不能使用合并的VIEW ，对于使用子查询时的派生表，对于多表 UPDATE以及其他一些情况，也需要临时表。

如果临时表很小，则可以创建到内存中，否则，则创建在磁盘上。不用说，内存临时表更快。内存临时表的最大大小由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 的性能最好，但可能会出现新的潜在问题。在某些特殊情况下，可能会出现磁盘耗尽和服务器中断的情况。

与数据库中的任何其他 InnoDB 表一样，临时表也有自己的表空间文件。新文件与通用表空间一起位于数据目录中，名称为ibtmp1。它存储所有临时表。表空间文件无法缩小，即使文件无法缩小，执行查询后临时表也会自动删除，并且表空间中的空间可供另一个传入查询重新使用。

全局临时表空间（ibtmp1）存储对用户创建的临时表所做更改的回滚段。该innodb_temp_data_file_path 变量定义全局临时表空间数据文件的相对路径、名称、大小和属性。如果未指定 值 ，则默认行为是在 目录中innodb_temp_data_file_path创建一个指定的自动扩展数据文件。初始文件大小略大于 12MB。 

必须重启服务器才能完全缩小ibtmp1表空间。全局临时表空间在正常关机或初始化中止时会被删除，并在每次服务器启动时重新创建。

检查 innodb_temp_data_file_path 设置：

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

检查全局临时表空间数据文件的大小

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
       AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
       WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912
      DATA_FREE: 6291456
   MAXIMUM_SIZE: NULL

解决方案

1,使用更大的磁盘 ,以通过设置以下配置变量将ibtmp1文件移动到专用大磁盘上：

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

2,设置 ibtmp1 大小的上限

配置变量：innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

可以根据您的服务器设置和需求调整此值,当数据文件达到最大大小时，查询将失败，并显示表已满的错误。

3,优化查询，减少临时表使用

目标是减少磁盘临时表的大小，可以依靠慢日志、pt-query-digest之类的工具以及 EXPLAIN。

4，使用MyISAM存储引擎

SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;

由于变量是动态的，您也可以在运行时设置它.回到 MyISAM，您将大大降低磁盘空间被完全填满的可能性。事实上，临时表将被创建到不同的文件中，并在查询结束时立即删除。不再需要担心文件大小不断增加。但希望在完成所有优化之后，您可以返回将临时存储引擎设置为 InnoDB 以获得更好的性能。

结论

InnoDB 存储引擎用于磁盘临时表是一个很好的改进，但在某些特殊情况下，例如，如果您的查询未优化且可用空间不足，则可能会因为“磁盘已满”错误而导致中断。将临时存储引擎迁移到 MyISAM 是避免中断的最快方法，但为了恢复到 InnoDB，更重要的是尽快优化查询。 同样适用于基于MySQL的万里开源和teleDB， 但在GoldenDB中更像是oracle的temp tablespace的管理方式

References

https://www.percona.com/blog/mysql-disk-space-exhaustion-for-implicit-temporary-tables

Leave a Comment