专栏首页数据库干货铺从MySQL的ibtmp1文件太大说起

从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 就表示会用到临时表,例如如下几种常见的情况通常就会用到:

3.1 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)


/**  group  by无索引字段*/
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 |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+




/**  group by 与order by字段不一致时,及时group by和order by字段有索引也会使用 */
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)

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

/**  例子中有无索引时会存在,如果2个字段都有索引会如何*/
mysql> alter table  test_tmp1 add key col2(col2);
Query OK, 0 rows affected (1.07 sec)
Records: 0  Duplicates: 0  Warnings: 0


/**   结果如下,其实该写法与group by +order by 一样*/
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已不使用临时表)

/**  先测一下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)


/**  再看一下union 作为对比,发现出现了使用临时表的情况*/
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)

3.4 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 ...的方式插入

/**  此方式将会使用临时表空间,且 tmp_table_size参数已调小为16M,本表当前有2G多,所以会使用临时表空间*/
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的数据,会不会异常,如果异常有什么表现?

/**   继续插入时 因临时表空间大小有限制,超过5G后将异常,信息如下*/
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

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

本文分享自微信公众号 - 数据库干货铺(database_gjc),作者:懂点IT的耿小厨

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-08-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL innodb表使用表空间ibd文件复制或迁移表

    MySQL InnoDB引擎的表通过拷贝物理文件来进行单表或指定表的复制,可以想到多种方式,今天测试其中2种:

    July
  • 开发同学,这么写不能走索引

    一个字符类型的、一个int类型的,查询的时候到底会不会走索引,其实很多工作了几年的开发人员有时也会晕,下面就用具体事例来测试一下。

    July
  • MySQL count知多少

    统计一个表的数据量是经常遇到的需求,但是不同的表设计及不同的写法,统计性能差别会有较大的差异,下面就简单通过实验进行测试(大家测试的时候注意缓存的情况,否则影响...

    July
  • 2018年一线互联网公司Java高级面试题总结

    1、hashcode相等两个类一定相等吗?equals呢?相反呢? 2、介绍一下集合框架? 3、hashmap hastable 底层实现什么区别?hashta...

    Java高级架构
  • 2018年一线互联网公司Java高级面试题总结JVM分布式专题架构福利

    美的让人心动
  • 2018年一线互联网公司Java高级面试题总结

    3、hashmap hastable 底层实现什么区别?hashtable和concurrenthashtable呢?

    java架构师
  • 2018“金三”之一线互联网公司Java高级面试题总结

    3、知道字节码吗?字节码都有哪些?Integer x =5,int y =5,比较x =y 都经过哪些步骤?

    技术zhai
  • 看看你是哪种级别的Python程序员(已跪)

    同一个问题 不同层次的Python程序员 编出的Python代码 编程新手 def factorial(x): if x == 0: ret...

    量化投资与机器学习微信公众号
  • 数据挖掘学习小组之(抽样分布篇)

    在参加数据挖掘学习小组之前,居士做了一个调查:每周能投入在学习上的时间是多少。我记得当时我选了3-5小时。我这是第六周了,可以说每周投入的时间都超过了这个数字。...

    数据处理与分析
  • Django 2.1.7 form处理select下拉菜单

    在开发的过程,会有很多form表单需要select下拉菜单的枚举类设置,如果一个个在前端中写死是很不合理的。应该要在models数据模型设置的时候就直接配置好,...

    Devops海洋的渔夫

扫码关注云+社区

领取腾讯云代金券