Diagnosing Excessive data_free Values in MySQL and Managing ibtmp1 Temporary Tablespace Bloat

1. Introduction

A client reported that the information_schema.tables column data_free suddenly jumped to about 13 GB, indicating a large amount of unreclaimed space. The article outlines the investigation process and the solution.

2. Investigation

2.1 Analysis

The data_free field represents the size of fragmented space left in a tablespace after write and delete operations. By comparing the primary and replica instances, the primary showed a data_free of roughly 13 GB while the replica was normal.

Screenshot of the primary’s ibtmp1 file also showed a size of 13 GB, suggesting a link between data_free and the temporary tablespace file.

2.2 Verification

Using sysbench, a test table sbtest1 with 20 000 rows was created, then duplicated into sbtest2. The following commands were executed to observe the temporary tablespace behavior:

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)

# du -sm ibtmp1
12 ibtmp1

After repeated INSERT ... SELECT operations, the ibtmp1 file grew to 204 MB:

# 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  ## matches physical file size
MAXIMUM_SIZE: NULL
1 row in set (0.00 sec)

Running EXPLAIN INSERT INTO sbtest2 ... SELECT ... confirmed that the operation used a temporary table:

mysql> explain insert into sbtest2(k,c,pad) select k,c,pad from sbtest2\G
*************************** 1. row ***************************
... 
Extra: Using temporary  ## indicates temporary table usage

Thus, the client’s instance accumulated space in the temporary tablespace because the temporary tables were not reclaimed after the SQL execution.

3. Temporary Tablespace Details

3.1 Overview

The file ibtmp1 is the independent tablespace for non‑compressed InnoDB temporary tables. Its location and size are defined by the innodb_temp_data_file_path parameter, defaulting to ibtmp1:12M:autoextend. If no path is specified, the file is created under innodb_data_home_dir.

By default the file can grow without limit, and in MySQL 5.7 it is not automatically reclaimed after a statement finishes, posing a risk of exhausting storage.

3.2 When Temporary Tables Are Used

Temporary tables are created when the execution plan’s Extra column contains Using temporary. Common scenarios include:

INSERT … SELECT

GROUP BY on a non‑indexed column or when GROUP BY and ORDER BY columns differ

DISTINCT values that cannot use a sparse index

3.3 Relevant Parameters and Metadata

MySQL 5.7:

innodb_temp_data_file_path
default_tmp_storage_engine
internal_tmp_disk_storage_engine

MySQL 8.0 (session‑level and global temporary tablespaces):

innodb_temp_tablespaces_dir   # session‑level directory
innodb_temp_data_file_path    # global variable
internal_tmp_disk_storage_engine

User‑created temporary tables can be inspected via 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

Temporary tables generated by the optimizer (as in this case) are not listed in that view.

3.4 Solving ibtmp1 Space Consumption

Restart the database during a maintenance window to release the temporary tablespace.

Configure innodb_temp_data_file_path with a maximum size to prevent unlimited growth, e.g.:

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

When the limit is reached MySQL returns

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

.

4. References

MySQL 5.7 Temporary Tablespace: https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

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