MySQL的ibtmp1文件太大引起磁盘空间爆满

最新推荐文章于 2024-04-19 11:08:54 发布

angela_bigdata

最新推荐文章于 2024-04-19 11:08:54 发布

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库服务器

原文链接：https://www.cnblogs.com/gjc592/p/11358935.html

版权

8 篇文章 0 订阅

订阅专栏

1. 现象

MySQL测试服务器磁盘空间不足，想着前两天才关注过磁盘空间，还有一半的可用空间，突然爆满不合乎常理，进入数据MySQL数据目录，看到文件ibtmp1占了一半以上的空间

2. 解决

2.1 关闭数据库实例：

shutdown;   #  因本实例为MySQL5.7  可以直接在SQL命令行中shutdown关闭

2.2 修改my.cnf配置文件

为了避免ibtmp1文件无止境的暴涨导致再次出现此情况,可以修改参数，限制其文件最大尺寸。

如果文件大小达到上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G # 12M代表文件初始大小，5G代表最大size

2.3 启动mysql服务

启动数据库后可以查一下是否生效：

show  variables like 'innodb_temp_data_file_path';

3 什么情况下会用到临时表

当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到：

a) GROUP BY 无索引字段或GROUP BY+ ORDER BY 的子句字段不一样时

b) order by 与distinct 共用，其中distinct与order by里的字段不一致（主键字段除外）

c) UNION查询（MySQL5.7后union all已不使用临时表）

d) insert into select ...from ...

小结：上面列举的是最常见的使用临时表的情况,其中基本都是引起慢查询的因素，因此，如果遇到临时表空间文件暴涨是需要查看一下是否有大量的慢查询。

4. 和临时表空间相关的参数有哪些

各参数之间相互影响,其中直接影响临时表空间的参数如要有如下几个

innodb_temp_data_file_path 
tmp_table_size
max_heap_table_size
default_tmp_storage_engine
internal_tmp_disk_storage_engine

关注