Failure Analysis | Starting from the data_free exception

Author: Yang Qilong

Net name "North is in the South", senior DBA, mainly responsible for database architecture design and operation and maintenance platform development, good at database performance tuning and fault diagnosis.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

I. Introduction

A customer reported that the query database found that the value of information_schema.tables data_free was unexpectedly abnormal, reaching about 13G. As shown in the figure:

What is the cause that needs to be investigated, this article sorts out the process of investigation and the method to solve the problem.

Second investigation

2.1 Analysis

First of all data_free means that after the tablespace ibd file is written and deleted, the size of the fragmented space left that is not reclaimed.

Ask the on-site students to check the main and standby databases at the same time, and compare whether there are differences in file size and configuration. It is found that the value of the main library data_free is about 13G, and the standby library is normal.

Looking at the results, it is guessed that it is related to some request actions on the main database. The hole is the result that the allocated space requested by MySQL because of sql writing is not automatically reclaimed. Based on the information given by the front line, there is no other way of thinking, and then look at the screenshots sent by the front line:

Accidentally 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 size.

Ignore the red arrow and check that the size of the ibtmp1 file is 13G. It seems to have some clues, whether data_free is related to ibtmp1.

2.2 Verifying the conjecture

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

 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)

Check the physical ibtmp1 file size:

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

Communicate 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 again ibtmp1 file size 204MB

 [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  ## 和物理文件大小对应
   MAXIMUM_SIZE: NULL
1 row in set (0.00 sec)

View the value of I_S.tables of data_free :

See that insert select from table does use a temporary table during execution.

 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 client's instance occupies the system temporary table space during the execution of some SQL processes, and the temporary table space is not reclaimed after use, causing the initial problem. Next, let's learn more about MySQL temporary tables.

Three temporary tablespaces

3.1 Introduction

ibtmp1 is an independent tablespace of the uncompressed innodb temporary table. The path, file name and size of the file are specified by the innodb_temp_data_file_path parameter. The default configuration is ibtmp1:12M:autoextend . is created to the path specified by innodb_data_home_dir .

Note: By default, this file size can grow indefinitely. Moreover, version 5.7 will not automatically reclaim the temporary table space with the end of the SQL statement, resulting in the security risk of insufficient space resources.

3.2 When will temporary tables be used

When explain looks at the extra column of the execution plan result, if it contains Using Temporary, it means that a temporary table will be used. For example, the following common situations are usually used:

1. insert into tab1 select ... from tab2 .
2. The field of group by without index or the field of group by order by is not the same.
3. The value of distinct is different from the value of group by, and sparse index cannot be used.

Other additions are welcome.

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 #指定会话级创建临时表到BASEDIR/data/#innodb_temp
innodb_temp_data_file_path # 全局变量
internal_tmp_disk_storage_engine

Temporary tables created by users themselves 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 table created by the optimizer during the execution of sql cannot be directly viewed through INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO . such as the case in this article.

3.4 How to solve the problem of ibtmp1 file space occupation

1. The omnipotent restart Dafa, find a suitable time, switch the database, and restart the old main library.

2. By configuring innodb_temp_data_file_path control the maximum value of the ibtmp1 file to avoid the infinite increase of the 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, 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