Understanding MySQL Temporary Tables and Temporary Files: Creation, Monitoring, and Management

Introduction

The following tests were performed on MySQL 8.0.21; results may differ on other versions.

What Are Temporary Tables and Temporary Files?

Temporary tables and temporary files are used to store intermediate result sets temporarily. Typical characteristics of data stored there are small size, short‑lived (lifetime equals the session or statement), session‑isolated, and they do not generate GTIDs.

From a creation perspective they fall into two categories:

User‑created temporary tables

Temporary tables/files generated by the optimizer

User‑Created Temporary Tables

Only the session that creates a temporary table can see its contents.

create database if not exists db_test ;
use db_test ;
CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
select * from db_test.t1 ;

Note: A temporary table can have the same name as a regular table; other sessions see the regular table, while the creating session sees the temporary one first.

To view user‑created temporary tables you can run:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G

These tables appear with names starting with #sql (e.g., #sql45aa_7c69_2 ) and are not listed in information_schema.tables .

Cleanup occurs automatically when the session ends, or manually with DROP TABLE t1; .

Parameters Related to User‑Created Temporary Tables

The session temporary tablespace stores both user‑created and optimizer‑created internal temporary tables when InnoDB is used. The directory is defined by innodb_temp_tablespaces_dir (default #innodb_temp under the data directory) and files are named temp_[1-20].ibt .

select * from information_schema.innodb_session_temp_tablespaces ;

When a user‑created temporary table is dropped, its space is released and the corresponding temp_*.ibt file shrinks.

Prior to MySQL 8.0.16 the variable internal_tmp_disk_storage_engine chose the engine for internal temporary tables (INNODB or MYISAM). From 8.0.16 onward this variable was removed and InnoDB is used by default.

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

When Does the Optimizer Create Temporary Tables or Files?

The optimizer creates internal temporary tables when the statement requires them, such as:

Uses UNION or UNION DISTINCT

Contains derived tables

Uses WITH (CTE)

ORDER BY and GROUP BY on different columns

Multi‑table UPDATE

Contains both DISTINCT and ORDER BY

You can detect temporary‑table usage by checking the Extra column of EXPLAIN for Using temporary (memory) or Using filesort (temporary file).

explain xxx ;

Another method is to query information_schema.innodb_session_temp_tablespaces after execution; if a new entry appears, a temporary table was created.

select * from information_schema.innodb_session_temp_tablespaces ;

Storage of Internal Temporary Tables

By default the optimizer stores internal temporary tables in memory using the TempTable engine (controlled by internal_tmp_mem_storage_engine ). When the memory usage exceeds temptable_max_ram , the engine switches to either memory‑mapped files or InnoDB on‑disk tables, depending on temptable_use_mmap (deprecated after 8.0.23 in favor of temptable_max_mmap ).

Disk‑based temporary tables are placed in the directory defined by tmpdir and are reclaimed when the server or TempTable engine shuts down.

Monitoring Temporary Table Usage

Memory usage of TempTables can be observed via Performance Schema:

select * from performance_schema.memory_summary_global_by_event_name \
where event_name in('memory/temptable/physical_ram','memory/temptable/physical_disk') \G

Creation counters are available:

Created_tmp_tables – total internal temporary tables

Created_tmp_disk_tables – those created on disk

If many disk temporary tables are created, consider increasing tmp_table_size and max_heap_table_size .

Exceptional Cases Requiring Large Temporary Space

LOAD DATA LOCAL statements store incoming data in tmpdir .

During replica replay of LOAD DATA, data is stored in slave_load_tmpdir (defaults to tmpdir ).

Online ALTER TABLE that rebuilds a table uses innodb_tmpdir for sorting files; if not set, it falls back to tmpdir .

These cases often need dedicated mount points.

Other Tips

Hidden temporary tables created by failed ALTER statements start with #sql and can be dropped.

Use lsof +L1 to find files marked deleted but still open.

Force release of such files by echoing an empty string to the corresponding file descriptor (use with caution).

cd /proc/${pid}/fd   # ${pid} is the process holding the file
ls -al | grep '${file_name}'
# find the fd number, e.g., 6
echo "" > ${fd_number}

Summary

Typical temporary tables/files are small, stored in memory first and spill to disk when they exceed size limits; disk temporary tables use InnoDB in the temporary tablespace defined by innodb_temp_tablespaces_dir . Space is reclaimed automatically after use, and the global temporary tablespace file ibtmp1 can have its maximum size configured via innodb_temp_data_file_path .

Exceptional operations such as LOAD DATA , replica replay, and online ALTER may require large temporary storage and should be directed to separate directories or mount points.

Setting set session big_tables=1 forces large temporary tables/files to be created directly on disk.

Design Speculation

MySQL likely stores small temporary objects in memory or a unified disk temporary tablespace for quick release, while large temporary objects are placed in user‑specified locations via parameters like tmpdir or innodb_tmpdir . Currently only innodb_temp_data_file_path can limit the size of user‑created temporary tables; no other parameter caps overall temporary‑table disk usage.