Preventing MySQL Disk Space Exhaustion from Implicit Temporary Tables

MySQL creates implicit temporary tables for operations such as GROUP BY , ORDER BY , DISTINCT , certain UNION statements, derived tables, and multi‑table updates; small tables stay in memory while larger ones are written to disk.

The transition to on‑disk tables is governed by tmp_table_size and max_heap_table_size (default 16 MB in MySQL 5.7). Even if these limits are not exceeded, the presence of TEXT or BLOB columns forces disk storage.

Before MySQL 5.6 all on‑disk temporary tables used the MyISAM engine; since MySQL 5.7 the default engine is InnoDB, which can be changed with the variable internal_tmp_disk_storage_engine (values: InnoDB or MyISAM ).

InnoDB temporary tables share a dedicated tablespace file named ibtmp1 . This file grows as temporary tables are created and does not shrink automatically; it only resets to zero after a server restart or after running OPTIMIZE TABLE on the temporary tables.

Typical scenarios that cause ibtmp1 to fill the disk include unoptimized queries that generate huge temporary tables, analytical workloads that deliberately create large temporary tables, high concurrency with many simultaneous queries, and environments with limited free disk space.

Simple mitigation is to add more disk capacity, but a more targeted approach is to move ibtmp1 to a dedicated large disk by setting innodb_temp_data_file_path in [mysqld] :

[mysqld]
innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend

You can also cap its size, for example:

[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

When the limit is reached, queries fail with a “table full” error, preventing further growth but potentially causing downtime.

An alternative is to revert temporary tables to MyISAM, which creates separate files that are removed immediately after query execution:

internal_tmp_disk_storage_engine = MYISAM

The engine can also be changed at runtime:

SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;

After switching to MyISAM, it is essential to optimize the offending queries—add missing indexes, add more selective filters, rewrite the query plan, or serialize execution with a queue—to reduce the size of temporary tables and eventually switch back to InnoDB for better performance.

In conclusion, while InnoDB temporary tables improve overall performance, they can cause disk‑full failures in specific cases; moving the tablespace, limiting its size, or temporarily using MyISAM are practical work‑arounds, but long‑term query optimization and adequate disk provisioning remain the most reliable solutions.