专注于 JetBrains IDEA 全家桶,永久激活,教程
持续更新 PyCharm,IDEA,WebStorm,PhpStorm,DataGrip,RubyMine,CLion,AppCode 永久激活教程

从MYSQL的ibtmp1文件太大说起

1、 啥情况呀

测试环境机器磁盘空间不足的告警打破了下午的沉寂,一群人开始忙活着删数据。但是,不久前刚清理了一波数据,测试环境在没做压测的情况下不至于短短一个月不到就涨了200G数据,于是,我悄悄的进入数据目录下,发现一个不寻常的点,ibtmp1文件有192G

 ll -h  ibtmp1 
-rw-r----- 1 mysql mysql 192G Aug 12 16:20 ibtmp1

2、 怎么处理

2、1 简单说明

ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径,文件名和大小,默认配置为ibtmp1:12M:autoextend,也就是说在支持大文件的系统这个文件大小是可以无限增长的。

2、2 解决办法

a) 找个空闲时间关闭数据

#  设置innodb_fast_shutdown参数
SET GLOBAL innodb_fast_shutdown = 0;  # 此步骤可以省略
#  关闭数据库实例
shutdown;   #  因本实例为MySQL5.7  可以直接在SQL命令行中shutdown关闭

关闭后ibtmp1文件会自动清理

b) 修改my.cnf配置文件

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

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

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

c) 启动mysql服务

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

mysql> show  variables like 'innodb_temp_data_file_path';
+
| Variable_name              | Value                         |
+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:5G |
+
1 row in set (0.01 sec)

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

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

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


mysql> show create table test_tmp1\G *************************** 1. row *************************** Table: test_tmp1 Create Table: CREATE TABLE `test_tmp1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `col2` varchar(25) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from test_tmp1 group by col2 ; + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + | 1 | SIMPLE | test_tmp1 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary; Using filesort | + mysql> explain select name from test_tmp1 group by name order by id desc; + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + | 1 | SIMPLE | test_tmp1 | NULL | range | name | name | 153 | NULL | 3 | 100.00 | Using index for group-by; Using temporary; Using filesort | + 1 row in set, 1 warning (0.02 sec)

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


mysql> alter table test_tmp1 add key col2(col2); Query OK, 0 rows affected (1.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select distinct col2 from test_tmp1 order by name; + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + | 1 | SIMPLE | test_tmp1 | NULL | index | col2 | col2 | 78 | NULL | 8 | 100.00 | Using temporary; Using filesort | + 1 row in set, 1 warning (0.00 sec)

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



mysql> explain select name from test_tmp1 union all select name from test_tmp1 where id <10; + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + | 1 | PRIMARY | test_tmp1 | NULL | index | NULL | name | 153 | NULL | 8 | 100.00 | Using index | | 2 | UNION | test_tmp1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8 | 100.00 | Using where | + 2 rows in set, 1 warning (0.01 sec) mysql> explain select name from test_tmp1 union select name from test_tmp1 where id <10; + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + | 1 | PRIMARY | test_tmp1 | NULL | index | NULL | name | 153 | NULL | 8 | 100.00 | Using index | | 2 | UNION | test_tmp1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8 | 100.00 | Using where | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | + 3 rows in set, 1 warning (0.00 sec)

d) insert into select …from …



mysql> explain insert into test_tmp1(name,col2) select name,col2 from test_tmp1; + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + | 1 | INSERT | test_tmp1 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | SIMPLE | test_tmp1 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary | + 2 rows in set (0.00 sec)

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

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

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

innodb_temp_data_file_path 
tmp_table_size
max_heap_table_size
default_tmp_storage_engine
internal_tmp_disk_storage_engine

5、 下面来模拟一个ibtmp1文件快速膨胀的例子

5、1 调整参数值

上面列出了主要的参数,那么先调整一下参数,以便于模拟

tmp_table_size = 16M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

调整后重启数据库

5、2 造一批数据


mysql> create table test_tmp3 select * from db1.tbname; Query OK, 15948372 rows affected (2 min 27.24 sec) Records: 15948372 Duplicates: 0 Warnings: 0

此时查看一下ibtmp1文件的大小

ll -h ibtmp1 
-rw-r----- 1 mysql mysql 12M Aug 15 16:06 ibtmp1  /**  此时是默认的初始大小*/

5、2 使用insert into … select * from …的方式插入


mysql> insert into test_tmp3 select * from test_tmp3 ; Query OK, 15948372 rows affected (2 min 7.40 sec) Records: 15948372 Duplicates: 0 Warnings: 0

此时 查看一下ibtmp1文件的大小

 ll -h ibtmp1 
-rw-r----- 1 mysql mysql 2.8G Aug 15 16:17 ibtmp1  /**  此时已使用了2.8G*/

此时该表的size如下

 ll -h bak_db/test_tmp3*                          /**   结果中已有5.8G*/
-rw-r----- 1 mysql mysql 8.9K Aug 15 16:04 bak_db/test_tmp3.frm
-rw-r----- 1 mysql mysql 5.8G Aug 15 16:16 bak_db/test_tmp3.ibd

5、3 继续测试,看看会发生什么

因为ibtmp1当前设置的最大值为5G,继续复制一个5.8G的数据,会不会异常,如果异常有什么表现?


mysql> insert into test_tmp3 select * from test_tmp3; ERROR 1114 (HY000): The table '/app/data/mysql3306/tmp/#sql_32469_0' is full

此时 查看一下ibtmp1文件的大小

 ll -h ibtmp1 
-rw-r----- 1 mysql mysql 5.0G Aug 15 16:17 ibtmp1 /** 此时已使用了5.0G,已达到上限*/

数据库日志里也会记录本次异常

2019-08-15T08:23:47.016495Z 3 [ERROR] /usr/local/mysql5.7/bin/mysqld: The table '/app/data/mysql3306/tmp/#sql_32469_0' is full

以上测试实例因不同的版本可能会有差异,建议大家亲自测试一下。

另外,大家帮忙关注一下我的微信公众号: 数据库干货铺 ,将不定期有书籍及学习资料赠送

90_1.png

文章永久链接:https://tech.souyunku.com/37755

未经允许不得转载:搜云库技术团队 » 从MYSQL的ibtmp1文件太大说起

JetBrains 全家桶,激活、破解、教程

提供 JetBrains 全家桶激活码、注册码、破解补丁下载及详细激活教程,支持 IntelliJ IDEA、PyCharm、WebStorm 等工具的永久激活。无论是破解教程,还是最新激活码,均可免费获得,帮助开发者解决常见激活问题,确保轻松破解并快速使用 JetBrains 软件。获取免费的破解补丁和激活码,快速解决激活难题,全面覆盖 2024/2025 版本!

联系我们联系我们