MySQL 5.7 临时表空间文件ibtmp1暴增原因及解决方法

1. MySQL临时表空间介绍
2. 临时表空间文件ibtmp1暴增原因及解决方法
3. 监控ibtmp1文件大小

1. MySQL临时表空间介绍

关于临时表、临时表空间的概念参考官方解释，临时表一种是用户通过create temporary table创建的显式临时表，另一种是复杂SQL执行时临时创建的隐式辅助表，当临时表需要存储的数据超过了tmp_table_size或max-heap-table-size中的较大值，那么临时表数据将会存储到磁盘，在MySQL 5.7中就是基于ibtmp1文件的临时表空间中。显式临时表数据和undo存于ibtmp1时，用户断开连接虽然释放了临时表，但实际的使用空间并不会释放，只有重启数据库才能真正释放这部分空间（这部分的功能bug在MySQL 8.0中得到解决）。另外，关于临时表的具体占用情况可以通过INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO查看。
MySQL 5.7中临时表空间通过innodb_temp_data_file_path参数控制，可以在配置文件my.cnf中根据需要设置临时表空间的路径、名称、大小，默认是ibtmp1:12M:autoextend，默认初始大小为12M，自增无上限，每次数据库重启之后会被删除重建。

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

2. 临时表空间文件ibtmp1暴增原因及解决方法

运维同事反应zabbix监测到数据库服务器/home使用率突然暴增到80%，通过查询发现是暴增源头是ibtmp1文件。其实在发现这个警告之前一个小时在这台机上做查询操作的同事反应查询卡死，原因为复杂查询与索引造成的死锁问题。后续排查定位问题是几个低效的SQL造成的，需要优化处理。

[root@mysqlplus2 ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/cl-root   50G  4.5G   46G   9% /
devtmpfs              63G     0   63G   0% /dev
tmpfs                 63G     0   63G   0% /dev/shm
tmpfs                 63G  386M   63G   1% /run
tmpfs                 63G     0   63G   0% /sys/fs/cgroup
/dev/sda1           1014M  141M  874M  14% /boot
`/dev/mapper/cl-home  1.1T  1.1T   26G  98% /home`
tmpfs                 13G     0   13G   0% /run/user/0

[root@mysqlplus2 ~]# ll -h /home/mysql/data/
total 840G
-rw-r----- 1 mysql mysql 332M Oct 11 14:21 ibdata1
-rw-r----- 1 mysql mysql  48M Oct 11 14:21 ib_logfile0
-rw-r----- 1 mysql mysql  48M Oct 11 03:00 ib_logfile1
`-rw-r----- 1 mysql mysql 839G Oct 11 14:21 ibtmp1`
-rw-r----- 1 mysql mysql  147 Oct 11 14:21 master.info

经查询ibtmp1已高达839G，解决办法只能是重启数据库释放这部分空间。这台数据库服务器为MySQL的备库，有一个项目读写分离的读操作放在这台机，先看下当前状态：

[root@mysqlplus2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 43717582
Server version: 5.7.18-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 197.168.1**.***
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000342
          Read_Master_Log_Pos: 415664119
               Relay_Log_File: slave-relay-bin.000619
                Relay_Log_Pos: 415664332
        Relay_Master_Log_File: mysql-bin.000342
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

先修改一下innodb_temp_data_file_path参数，将自增无上限改为上限阈值100G。

[root@mysqlplus2 ~]# vim /etc/my.cnf
 innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:100G

stop slave然后重启数据库，重建备库连接：

[root@mysqlplus2 ~]# vim /etc/init.d/mysqld
[root@mysqlplus2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL........................... SUCCESS!
Starting MySQL. SUCCESS!
[root@mysqlplus2 ~]# /etc/init.d/mysqld status
 SUCCESS! MySQL running (196297)
[root@mysqlplus2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 21
Server version: 5.7.18-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 197.168.1**.***
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000342
          Read_Master_Log_Pos: 416155712
               Relay_Log_File: slave-relay-bin.000621
                Relay_Log_Pos: 181933
        Relay_Master_Log_File: mysql-bin.000342
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
mysql> show variables like 'innodb_temp_data_file_path';
+----------------------------+--------------------------------+
| Variable_name              | Value                          |
+----------------------------+--------------------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:100G |
+----------------------------+--------------------------------+

3. 监控ibtmp1文件大小

至此，看似解决的问题仍需要进一步控制它的周边影响，监控ibtmp1文件的大小及时释放空间，以防止它暴增至上限影响业务查询，这里通过在zabbix上添加ibtmp1文件监控项，创建监控阈值的触发器。
先创建监控项，每2小时查询一次：

这里使用的是监控文件大小的键值：

接下来对该监控项创建触发器，触发器阈值为70G，注意一下这里的单位是bytes：

触发器表达式可以测试一下：

接下来就可以在最新数据中看到该监控项的数据了：

至此，该问题告一段落，是时候规划升级数据库到8.0了。

MySQL 5.7 临时表空间文件ibtmp1暴增原因及解决方法

Linux相关栏目本月热门文章