Why MySQL’s ibtmp1 Swells to 192 GB and How to Stop It

1. The Emergency Alert

On a typical Wednesday afternoon a severe alert fires: disk usage exceeds 98 %. Investigation shows a 192 GB file named ibtmp1 consuming the space.

2. What is ibtmp1?

In MySQL 5.7+ the ibtmp1 file is the InnoDB temporary tablespace used for on‑disk temporary tables. Its default configuration is defined by the variable innodb_temp_data_file_path = ibtmp1:12M:autoextend, meaning it starts at 12 MB and grows without a hard limit as long as disk space remains.

3. Three‑step Remedy

Step 1 – Graceful shutdown to clear the ghost : Restarting MySQL automatically resets the temporary tablespace. For safety, run:

SET GLOBAL innodb_fast_shutdown = 0;</code><code>SHUTDOWN;

This forces a full checkpoint before the instance stops.

Step 2 – Set a size cap to prevent recurrence : Add a limit in my.cnf:

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

Now the temporary tablespace cannot exceed 20 GB; any further growth triggers an error, exposing the problematic queries.

Step 3 – Identify the “real culprits” – SQL that creates large temporary tables : Use slow‑query logs and EXPLAIN to find statements that produce Using temporary. Typical patterns include: GROUP BY on non‑indexed columns

Mismatched GROUP BY and ORDER BY fields DISTINCT with an ORDER BY on a different column UNION (except UNION ALL) INSERT INTO … SELECT … on large tables

Example checks:

EXPLAIN SELECT * FROM test_tmp1 GROUP BY col2;  -- Extra: Using temporary; Using filesort

EXPLAIN SELECT name FROM test_tmp1 GROUP BY name ORDER BY id DESC;  -- Extra: Using temporary; Using filesort

EXPLAIN SELECT DISTINCT col2 FROM test_tmp1 ORDER BY name;  -- Extra: Using temporary; Using filesort

EXPLAIN SELECT name FROM t1 UNION SELECT name FROM t1 WHERE id < 10;  -- Extra: Using temporary

EXPLAIN INSERT INTO test_tmp3 SELECT * FROM test_tmp3;  -- Extra: Using temporary

4. Reproducing the Issue

A small experiment demonstrates the growth:

Create a 15 million‑row table without indexes.

Set tmp_table_size=16M and innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2G to force disk‑based temporary tables.

Run INSERT INTO test_tmp3 SELECT * FROM test_tmp3; Result: ibtmp1 expands from 12 MB to about 2.8 GB, and a second run hits the 2 GB limit, producing error ERROR 1114 (HY000): The table '/tmp/#sql_xxx' is full.

5. Related Parameters

Besides innodb_temp_data_file_path, other settings influence temporary table behavior: tmp_table_size – maximum size of in‑memory temporary tables. max_heap_table_size – limit for user‑defined MEMORY tables. default_tmp_storage_engine – default engine for temporary tables (InnoDB from 5.7). internal_tmp_disk_storage_engine – engine for on‑disk temporary tables (introduced in 8.0).

These variables interact to decide whether a temporary table stays in memory or spills to disk.

6. Conclusion

Clearing the 192 GB ibtmp1 file and capping its size eliminated the alert, and fixing the identified inefficient queries prevented future explosions. The episode illustrates that sudden MySQL slowdowns and disk exhaustion are signals of poorly written SQL that should be optimized.