作者：杨奇龙

网名“北在北方”，资深 DBA，次要负责数据库架构设计和运维平台开发工作，善于数据库性能调优、故障诊断。

本文起源：原创投稿

*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

一 前言

某个客户反馈查询数据库发现 information_schema.tables 的 data_free 值突发异样，达到 13G 左右。如图：

须要排查什么起因导致的，本文梳理排查的过程和和解决问题的办法。

二 排查

2.1 剖析

首先 data_free 的含意是 表空间 ibd 文件通过写入和删除之后，留下的没有回收的碎片空间大小。

让现场的同学同时查看主备库，比照有没有文件大小和配置上的差别。 发现主库的data_free 值是 13G 左右， 备库失常。

看后果猜想和主库上的某些申请动作无关，空洞是 MySQL 因为 sql 写入而申请调配的空间没有主动回收的后果。基于火线给的信息，没有其余思路，再看火线发的截图:

意外从 截图的 ibtmp1 文件大小找到一些线索，截图显示 ibtmp1 文件大小也是 13G ，备库则是初始值大小。

疏忽红色的箭头，查看 ibtmp1 文件大小为 13G ，仿佛有些脉络，data_free 是否和 ibtmp1 无关。

2.2 验证猜测

应用 sysbench 创立测试表 sbtest1 ，结构2w条记录，而后创立 sbtest2 ，将 sbtest1 的数据 导入到 sbtest2 。为何这么操作，前面会阐明。

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.00 sec)

查看物理ibtmp1 文件大小:

[root@tidb00 data]# du -sm ibtmp112    ibtmp1

沟通测试用例，让零碎主动生成长期表

mysql  > create table sbtest2 like sbtest1;Query OK, 0 rows affected (0.01 sec)mysql > insert into sbtest2(k,c,pad) select k,c ,pad from sbtest1;Query OK, 200000 rows affected (1.18 sec)Records: 200000  Duplicates: 0  Warnings: 0mysql > insert into sbtest2(k,c,pad) select k,c ,pad from sbtest1;Query OK, 200000 rows affected (1.06 sec)mysql > insert into sbtest2(k,c,pad) select k,c ,pad from sbtest2;Query OK, 400000 rows affected (2.49 sec)Records: 400000  Duplicates: 0  Warnings: 0mysql > insert into sbtest2(k,c,pad) select k,c ,pad from sbtest2;Query OK, 800000 rows affected (6.18 sec)Records: 800000  Duplicates: 0  Warnings: 0

再次查看 ibtmp1 文件大小 204MB

[root@tidb00 data]# du -sm ibtmp1204    ibtmp1mysql > SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE    ->        AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES    ->        WHERE TABLESPACE_NAME = 'innodb_temporary'\G*************************** 1. row ***************************      FILE_NAME: ./ibtmp1TABLESPACE_NAME: innodb_temporary         ENGINE: InnoDB   INITIAL_SIZE: 12582912 TotalSizeBytes: 213909504      DATA_FREE: 207618048  ## 和物理文件大小对应   MAXIMUM_SIZE: NULL1 row in set (0.00 sec)

查看 I_S.tables 的data_free 的值:

查看 insert select from table 在执行过程中确实应用了长期表。

mysql > explain insert into sbtest2(k,c,pad) select k,c ,pad from sbtest2\G*************************** 1. row ***************************..*************************** 2. row ***************************           id: 1  select_type: SIMPLE        table: sbtest2   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1578168     filtered: 100.00        Extra: Using temporary  ## 2 rows in set (0.00 sec)Records: 200000  Duplicates: 0  Warnings: 0

至此，能够确定客户的实例因为执行某些 SQL 过程中占用零碎长期表空间，应用完之后长期表空间并未被回收导致结尾的问题。接下来咱们具体理解 MySQL 长期表的相干常识。

三 长期表空间

3.1 介绍

ibtmp1 是非压缩的 innodb 长期表的独立表空间, 通过 innodb_temp_data_file_path参数指定文件的门路，文件名和大小，默认配置为ibtmp1:12M:autoextend，如果没有指定地位，长期表空间会被创立到innodb_data_home_dir 指定的门路。

须要留神的是: 依照默认值，这个文件大小是能够有限增长的。而且 5.7 版本并不会随着 SQL 语句完结被动回收该长期表空间，导致空间资源有余的平安危险。

3.2 什么状况下会用到长期表

当 explain 查看执行打算后果的 extra 列中，如果蕴含 Using Temporary 就示意会用到长期表,例如如下几种常见的状况通常就会用到：

  1. insert into tab1 select ... from tab2 。
  2. group by 无索引字段或 group by order by 的字段不一样。
  3. distinct 的值和 group by 的值不一样，无奈利用稠密索引。

其余的欢送补充。

3.3 长期表相干的参数和元数据

5.7 版本:

innodb_temp_data_file_pathdefault_tmp_storage_engine internal_tmp_disk_storage_engine

8.0 版本分为会话级和全局级长期表空间

innodb_temp_tablespaces_dir #指定会话级创立长期表到BASEDIR/data/#innodb_tempinnodb_temp_data_file_path # 全局变量internal_tmp_disk_storage_engine

用户本人创立的长期表能够通过查问 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO

mysql > CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;Query OK, 0 rows affected (0.00 sec)mysql  > SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G*************************** 1. row ***************************            TABLE_ID: 54                NAME: #sqlfd5_b_0              N_COLS: 4               SPACE: 36PER_TABLE_TABLESPACE: FALSE       IS_COMPRESSED: FALSE1 row in set (0.00 sec)

MySQL 在执行 sql 过程中被优化器创立的表，则无奈通过 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 间接查看。比方本文的案例。

3.4 怎么解决 ibtmp1 文件空间占用的问题

  1. 万能的重启大法, 找个适合的工夫，切换数据库，重启老的主库。

  2. 通过配置 innodb_temp_data_file_path 管制ibtmp1 文件的最大值，防止表空间大小有限减少。

    innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

    12M是文件的初始大小，10G是文件的最大值，超过最大值则零碎会提醒报错

    ERROR 1114 (HY000): The table '/data/msb_5_7_31/tmp#sql_xxxxx_0' is full

参考文章

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...