1, Foreword
A customer feedback query database found information_ schema. Data of tables_ The free value is suddenly abnormal, reaching about 13G. As shown in the figure:

picture
What causes need to be investigated? This paper combs the process of investigation and the methods to solve the problems.

2, Check
2.1 analysis
First, data_free means the amount of fragmented space left after the table space ibd file is written and deleted without recycling.
Ask the students on site to check the primary and standby databases at the same time, and compare whether there are differences in file size and configuration. Discover the data of the main library_ The free value is about 13G, and the standby database is normal.

Looking at the result, I guess it is related to some request actions on the main database. The hole is the result that MySQL requests to allocate space because of sql writing, which is not automatically reclaimed. Based on the information given by the front line, there is no other idea. Look at the screenshot sent by the front line:

Unexpectedly, I found some clues from the ibtmp1 file size in the screenshot. The screenshot shows that the ibtmp1 file size is also 13G, and the standby database is the initial value size.

Ignore the red arrow and check that the ibtmp1 file size is 13G. It seems that there are some clues. Data_ Whether free is related to ibtmp1.

2.2 verification conjecture
Use sysbench to create the test table sbtest1, construct 2w records, and then create sbtest2 to import the data of sbtest1 into sbtest2. The reason for this operation will be explained later.

1 row in set (0.00 sec)

To view the physical ibtmp1 file size:

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

Build test cases and let the system automatically generate temporary tables

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: 0
mysql > 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: 0
mysql > 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

Check ibtmp1 file size 204MB again

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

mysql > 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: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 213909504
      DATA_FREE: 207618048  ## Corresponding to physical file size
   MAXIMUM_SIZE: NULL
1 row in set (0.00 sec)

View I_ S. Data of tables_ Value of free:

Check that the temporary table is indeed used during the execution of 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: ALL
possible_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

So far, it can be determined that the customer's instance occupies the system temporary table space during the execution of some SQL processes, and the temporary table space is not recycled after use, resulting in the initial problem. Next, let's learn more about MySQL temporary tables.

3, Temporary tablespace

3.1 introduction
Ibtmp1 is an independent table space of the uncompressed innodb temporary table, which is accessed through innodb_ temp_ data_ file_ The path parameter specifies the path, file name and size of the file. The default configuration is ibtmp1:12M:autoextend. If no location is specified, the temporary table space will be created to innodb_data_home_dir specifies the path.
It should be noted that according to the default value, the file size can grow infinitely. Moreover, version 5.7 will not actively reclaim the temporary table space as the SQL statement ends, resulting in the security risk of insufficient space resources.
3.2 under what circumstances will temporary tables be used
When explain is used to view the extra column of the execution plan results, if Using Temporary is included, it means that temporary tables will be used. For example, the following common situations are usually used:

insert into tab1 select ... from tab2 .

Group by has no index field or group by order by has different fields.

The value of distinct is different from that of group by, and sparse indexes cannot be used.

Others are welcome to add.
3.3 parameters and metadata related to temporary tables
Version 5.7:

innodb_temp_data_file_path
default_tmp_storage_engine
internal_tmp_disk_storage_engine
Version 8.0 is divided into session level and global level temporary tablespaces

innodb_temp_tablespaces_dir # specifies that a temporary table is created at the session level to basedir/data/\innodb_ temp
innodb_temp_data_file_path # global variable
internal_tmp_disk_storage_engine
The temporary table created by the user can be queried by 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: 36

PER_TABLE_TABLESPACE: FALSE

   IS_COMPRESSED: FALSE

1 row in set (0.00 sec)
The tables created by the optimizer during the execution of MySQL cannot pass the INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO view directly. For example, the case of this article.

3.4 how to solve the problem of ibtmp1 file space occupation
The universal method of restarting is to find a suitable time to switch databases and restart the old main database.
By configuring innodb_temp_data_file_path controls the maximum value of ibtmp1 file to avoid unlimited increase of table space size.

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

12M is the initial size of the file, 10G is the maximum value of the file, and if the maximum value is exceeded, the system will prompt an error

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

Reference article
https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html