Temporary Table Disk Spill & Sort Buffer Sizing

Query execution memory limits where complex ORDER BY, GROUP BY, or UNION operations exceed sort_buffer_size or tmp_table_size, spilling temporary tables to disk.

Internal Architecture Breakdown

When query aggregation or sorting intermediate sets exceed tmp_table_size, max_heap_table_size, or sort_buffer_size, MySQL converts in-memory temporary tables into on-disk storage structures.

Rising Created_tmp_disk_tables metrics confirm severe disk I/O bottlenecks and indicate a need for composite indexing or query plan tuning.

Production Inspection Queries

MySQL

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

Back to Glossary