Write by lyc at 2021-1-6
参考博文：
从MYSQL的ibtmp1文件太大说起
故障分析 | MySQL 临时表空间数据过多导致磁盘空间不足的问题排查

1.ibtmp1 是什么

  • ibtmp1 是非压缩的 innodb 临时表的独立表空间，通过 innodb_temp_data_file_path 参数指定文件的路径，文件名和大小。
  • ibtmp1 默认配置为 ibtmp1:12M:autoextend，==也就是说在支持大文件的系统这个文件大小是可以无限增长的==。
  • ibtmp1 每次mysqld服务器启动时会被重新创建

ibtmp1 文件位于MySQL数据目录下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$ ls -lh
total 5.3G
-rw-r----- 1 mysql mysql   56 Nov 20  2018 auto.cnf
-rw-r----- 1 mysql mysql  20K Jan  8  2020 ddl_log.log
-rw-r----- 1 mysql mysql 6.4M Dec  9  2018 ib_buffer_pool
-rw-rw---- 1 mysql mysql 1.0G Jan  6 14:56 ibdata1
-rw-r----- 1 mysql mysql 2.0G Jan  6 14:56 ib_logfile0
-rw-r----- 1 mysql mysql 2.0G Jan  6 14:56 ib_logfile1
-rw-r----- 1 mysql mysql 204M Jan  6 14:17 ibtmp1       # <== ibtmp1 文件
drwxr-x--- 2 mysql mysql  185 Dec  9  2018 ipdata
-rw-r--r-- 1 mysql mysql 3.6K Apr 11  2019 my.cnf
drwxr-x--- 2 mysql mysql 4.0K Nov 20  2018 mysql
-rw-r----- 1 mysql mysql    4 Dec  9  2018 mysql_3360.pid
srwxrwxrwx 1 mysql mysql    0 Dec  9  2018 mysql_3360.sock
-rw------- 1 mysql mysql    4 Dec  9  2018 mysql_3360.sock.lock
drwxr-x--- 2 mysql mysql 8.0K Nov 20  2018 performance_schema
drwxr-x--- 2 mysql mysql 8.0K Nov 20  2018 sys
drwxr-x--- 2 mysql mysql  162 Dec  9  2018 test
....

查看 ibtmp1 配置

1
2
3
4
5
6
7
mysql >show global variables like "innodb_temp_data_file_path";
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

2.如何修改 ibtmp1 默认值

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

my.cnf 下，12M代表文件初始大小，5G代表最大size

1
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

重启MySQL服务生效

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

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

个人认为这些值保持默认即可。

1
2
3
4
5
6
# 以下均是5.7默认值
innodb_temp_data_file_path=ibtmp1:12M:autoextend
tmp_table_size=16M
max_heap_table_size=16M
default_tmp_storage_engine=InnoDB
internal_tmp_disk_storage_engine=InnoDB

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

当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表。

  1. GROUP BY 无索引字段或 GROUP BY + ORDER BY 的子句字段不一样时
    1
    2
    3
    4
    5
    6
    7
    /**  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 |
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
  2. order by 与 distinct 共用，其中 distinct 与 order by 里的字段不一致（主键字段除外）
  3. UNION 查询（MySQL5.7后union all已不使用临时表）
  4. insert into select ...from ...

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

5.ibtmp1 设置了最大值对业务有什么影响？

如果设置了 ibtmp1 最大值

  • 好处是限制了 ibtmp1 文件不会无限增长
  • 坏处是一旦某个业务需要使用超过最大值的临时表，那就会报错，并且errorlog也会报错。
1
2
3
4
5
6
# sql报错
mysql> insert into  test_tmp3 select  * from test_tmp3;
ERROR 1114 (HY000): The table '/app/data/mysql3306/tmp/#sql_32469_0' is full

# errorlog报错
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

我们可以把 ibtmp1 最大值设置成 5G，10G或20G，参考当前实例的最大的表来设置，防止 ibtmp1 无限增长下去。

6.如何释放 ibtmp1 空间

重启 mysqld

根据官网文档的解释，==在正常关闭或初始化中止时，将删除临时表空间，并在每次启动服务器时重新创建==。重启能够释放空间的原因在于正常关闭数据库，临时表空间就被删除了，重新启动后重新创建，也就是重启引发了临时表空间的重建，重新初始化，所以，==重建后的大小为 12M。==

  • 在 mysql5.7 时，杀掉长事务PID的会话，临时表会释放，但是仅仅是在 ibtmp 文件里标记一下，空间是不会释放回操作系统的。如果要释放空间，需要重启数据库；
  • 在 mysql8.0 中可以通过杀掉会话来释放临时表空间

7.如何规避这个问题

  1. 对临时表空间的大小进行限制，允许自动增长，但最大容量有上限
    1
    2
    [mysqld]
    innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
  2. 设置了上限的大小，当数据文件达到最大大小时，查询将失败，并显示一条错误消息，表明表已满，查询不能往下执行，避免 ibtmp1 过大。
  3. 在发送例如本例中的多表关联 SQL 时应确保有关联字段而且有索引，避免笛卡尔积式的全表扫描，对存在 group by、order by、多表关联的 SQL 要评估临时数据量，对 SQL 进行审核，没有审核不允许上线执行。
  4. 在执行前通过 explain 查看执行计划，对 Using temporary 需要格外关注。

8.个人看法

一般业务我认为没必要特地去限制这个临时表独立空间大小，让业务自由发挥。定时巡检时如果发现有临时表空间特别大，甚至超过实例总空间几倍的情况下，再排查：

  • 结合程序员侧来确认他们是否有经常使用临时表空间的sql，减少这类sql的使用
  • 业务低峰期重启mysqld释放临时表独立空间
  • 最后在斟酌一个合适的ibtmp1 max 配置上去来限制。