Changes in MySQL Internal Temporary Table Storage from 5.6 to 8.0

MySQL Internal Temporary Tables Overview

When a SQL statement needs to store intermediate results that cannot be produced directly, MySQL creates an internal temporary table, which resides in memory until it exceeds a size limit and then spills to disk.

MySQL 5.6

In MySQL 5.6, internal temporary tables that exceed the memory limit are created as separate tablespace files in the temporary directory. If the server is configured with innodb_file_per_table disabled, these tables are placed in the shared InnoDB tablespace (ibdata1), which never shrinks, often causing the file to grow excessively.

MySQL 5.7

MySQL 5.7 introduced a shared temporary tablespace that separates internal temporary tables from the general InnoDB tablespace, eliminating the overhead of creating and dropping a tablespace for each temporary table and allowing the space to be reset on server restart.

Key parameters include tmp_table_size and max_heap_table_size (memory limits), internal_tmp_disk_storage_engine (engine for disk‑based temporary tables), and innodb_temp_data_file_path (default ibtmp1:12M:autoextend ).

MySQL 8.0

MySQL 8.0 adds several new parameters and changes the default storage engine for internal temporary tables from MEMORY to TempTable , which supports variable‑length columns and BLOB/TEXT types, and can use up to 1 GB of RAM before spilling.

internal_tmp_mem_storage_engine – default TempTable .

temptable_max_ram – maximum RAM before disk spill (default 1 GB).

temptable_use_mmap – enables memory‑mapped temporary files as overflow (ON by default, deprecated after 8.0.26).

temptable_max_mmap – maximum memory‑mapped file size (default 1 GB, 0 disables).

When temptable_use_mmap is ON and the temporary table exceeds temptable_max_ram , MySQL creates a memory‑mapped temporary file (e.g., ibtmp1 ) to avoid excessive RAM usage. If the option is OFF, the overflow goes to an InnoDB disk temporary table, which is released automatically when the session ends.

Session and Global Temporary Tablespaces

MySQL 8.0 splits temporary tablespaces into session‑level and global temporary tablespaces. Internal temporary tables are stored in the session temporary tablespace, which consists of a pool of ten files (e.g., temp_1.ibt … temp_10.ibt ) located under #innodb_temp in the data directory.

shell> ls datadir/#innodb_temp
temp_10.ibt  temp_2.ibt  temp_4.ibt  temp_6.ibt  temp_8.ibt
temp_1.ibt   temp_3.ibt  temp_5.ibt  temp_7.ibt  temp_9.ibt

Each session can acquire up to two temporary tablespaces (one for user‑created temporary tables, one for optimizer‑generated internal tables). When the session disconnects, its temporary tablespaces are reclaimed.

Practical Observations

With temptable_use_mmap=ON , exceeding temptable_max_ram creates an additional memory‑mapped temporary file in tmpdir , increasing the file count but not the number of disk temporary tables. With the option OFF, a new disk temporary table appears in the session temporary tablespace, while the file count remains unchanged.

Known bugs in MySQL 8.0 (e.g., bug IDs 98782, 98739, 99593, 99100) can cause performance degradation or “table is full” errors; a temporary workaround is to force internal_tmp_mem_storage_engine=MEMORY until the issues are resolved.