转载

怎么解决ibtmp1文件空间占用的问题?

一、临时表空间

MySQL 5.7在临时表空间上做了改进，已经实现将临时表空间从 ibdata（共享表空间文件）中分离。并且可以重启重置大小，避免出现像以前 ibdata 过大难以释放的问题。

MySQL启动时 datadir 下会创建一个 ibtmp1 文件，默认配置为 ibtmp1:12M:autoextend，即初始大小为 12M，默认值下会无限扩展。

通常来说，查询导致的临时表（如group by）如果超出 tmp_table_size、max_heap_table_size 大小限制则创建 innodb 磁盘临时表（MySQL5.7默认临时表引擎为 innodb），存放在共享临时表空间。

如果某个操作创建了一个大小为100M的临时表，则临时表空间数据文件会扩展到100M大小以满足临时表的需要。当删除临时表时，释放的空间可以重新用于新的临时表，但 ibtmp1 文件保持扩展大小。

共享临时表空间用于存储非压缩InnoDB临时表(non-compressed InnoDB temporary tables)、关系对象(related objects)、回滚段(rollback segment)等数据；

tmpdir 用于存放指定临时文件(temporary files)和临时表(temporary tables)，与共享临时表空间不同的是，tmpdir存储的是压缩的InnoDB临时表。

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象，当 explain 查看执行计划结果的 extra 列中，如果包含 Using Temporary 就表示会用到临时表，例如如下几种常见的情况通常就会用到：

1、万能的重启大法，找个合适的时间，切换数据库，重启老的主库。

因为临时表空间使用动态的表空间ID，因此每次重启时都会变化（每次重启时，都会重新初始化临时表空间文件）。

2、通过配置 innodb_temp_data_file_path 控制 ibtmp1 文件的最大值，避免表空间大小无限增加。

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

12M是文件的初始大小，10G是文件的最大值，超过最大值则系统会提示报错

ERROR 1114 (HY000): The table '/data/msb_5_7_31/tmp#sql_xxxxx_0' is full

正文到此结束

所属分类：数据库

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。