The Hidden MySQL File That Grows on Its Own—Why 90% of DBAs Miss It

On a typical Wednesday afternoon a severe alert reported disk usage over 98 %. While the team inspected log files, a quick SSH check of MySQL's data directory uncovered a 192 GB file named ibtmp1.

What Is the "ghost" file?

In MySQL 5.7 and later, ibtmp1 is the InnoDB temporary tablespace used for storing 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 can grow without bound as long as disk space remains.

Why did it explode?

In the test environment, performance was ignored and inefficient SQL statements repeatedly created temporary tables. Each such operation caused ibtmp1 to expand like a balloon until the disk filled.

Three steps to "exorcise" the file

1. Graceful shutdown to clear the file

Temporary tablespaces exist only while MySQL runs. Restarting the instance automatically resets ibtmp1. To ensure a clean shutdown, run:

SET GLOBAL innodb_fast_shutdown = 0;
SHUTDOWN;

MySQL 5.7+ allows the SHUTDOWN command directly in SQL. After the shutdown, the 192 GB file disappears.

2. Set an upper limit to prevent recurrence

Add a size cap in my.cnf:

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

Now even aggressive SQL can consume at most 20 GB; exceeding this triggers an error, exposing the problematic queries.

Verification after restart:

SHOW VARIABLES LIKE 'innodb_temp_data_file_path';
-- Output: ibtmp1:12M:autoextend:max:20G

3. Identify the offending SQL statements

Queries that cause Using temporary in the execution plan are the main culprits. Typical patterns include:

GROUP BY on non‑indexed columns

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

GROUP BY and ORDER BY on different columns

EXPLAIN SELECT name FROM test_tmp1 GROUP BY name ORDER BY id DESC;
-- Extra: Using index for group‑by; Using temporary; Using filesort

DISTINCT with mismatched ORDER BY

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

UNION (note: UNION ALL avoids temporary tables)

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

INSERT … SELECT on large tables

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

These patterns are strong indicators of slow‑query risk and are often the root cause of ibtmp1 growth.

Reproducing the issue

To demonstrate the expansion, the author performed a small experiment:

Create a 15 million‑row table test_tmp3 without primary key or 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; Results:

Initial ibtmp1: 12 MB

After execution: 2.8 GB

Second execution (with 2 GB cap) fails with error:

ERROR 1114 (HY000): The table '/tmp/#sql_xxx' is full

The error log also records the same message, confirming the intended "circuit‑breaker" behavior—allowing the SQL to fail rather than crashing the server.

Related configuration parameters

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

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

Conclusion

By restarting MySQL, capping ibtmp1, and locating inefficient queries, the author cleared a 192 GB ghost file and prevented future incidents. The episode illustrates that sudden disk‑space exhaustion is rarely random; it signals neglected SQL patterns that need optimization.