Temporary Tables, Spill to Disk, tmpdir, Internal Temp Engines, and Query Rewrite

Learning outcomes

A reporting query suddenly starts consuming disk and latency increases. An operator raises tmp_table_size globally, assuming every “temporary table” is the same and that more memory must be faster. MySQL has both explicit temporary tables created by SQL and internal temporary tables created by the optimizer/executor. MySQL 8.4 also changed TempTable defaults, so older tuning recipes can be actively misleading.

Internal temporary work is an execution mechanism

MySQL may create an internal temporary table for GROUP BY/ORDER BY combinations, DISTINCT, UNION, materialized derived tables/CTEs, semijoin materialization, some window processing, and other plan shapes. You do not issue CREATE TEMPORARY TABLE for these objects; the server creates and removes them as part of statement execution.

An explicit CREATE TEMPORARY TABLE, by contrast, is a session-visible SQL object that persists until you drop it or disconnect. Its storage-engine and size rules are not identical to internal TempTable rules.

Inspect effective TempTable and tmpdir policy

SELECT @@internal_tmp_mem_storage_engine AS internal_mem_engine,       @@tmp_table_size AS tmp_table_size,       @@GLOBAL.temptable_max_ram AS temptable_max_ram,       @@GLOBAL.temptable_max_mmap AS temptable_max_mmap,       @@GLOBAL.tmpdir AS tmpdir;SELECT VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCEFROM performance_schema.variables_infoWHERE VARIABLE_NAME IN ( 'internal_tmp_mem_storage_engine','tmp_table_size', 'temptable_max_ram','temptable_max_mmap','tmpdir')ORDER BY VARIABLE_NAME;

tmp_table_size limits an individual in-memory internal temporary table. temptable_max_ram governs global RAM used by TempTable, excluding its per-thread local block. If memory-mapped overflow is disabled, exceeding the relevant limits can move internal work to InnoDB on-disk internal temporary storage rather than proving that tmpdir is the only capacity to watch.

Prove the table and variable context before tuning

Before blaming temporary work, confirm the table engine/schema size and the scope of the variables you plan to touch. This prevents two common mistakes: assuming an explicit table is using a particular engine without evidence, and trying to apply a global TempTable budget as if it were a per-session knob.

SHOW CREATE TABLE servicehub_perf_lab.work_orders\GSELECT ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTHFROM information_schema.tablesWHERE table_schema='servicehub_perf_lab'  AND table_name='work_orders';SHOW VARIABLES WHERE Variable_name IN ( 'internal_tmp_mem_storage_engine','tmp_table_size', 'temptable_max_ram','temptable_max_mmap','tmpdir');

SHOW CREATE TABLE proves the declared ServiceHub table definition; INFORMATION_SCHEMA.TABLES adds storage metadata whose row/byte figures can be estimates for InnoDB. Neither output proves how much internal temporary work a specific statement creates—that requires execution evidence.

-- Do this only to observe the safe rejection, then leave the global value unchanged.SET SESSION temptable_max_ram = 67108864;-- Expected on MySQL 8.4:-- ERROR 1229 (HY000): Variable 'temptable_max_ram' is a GLOBAL variable-- and should be set with SET GLOBAL

The rejection is useful evidence: temptable_max_ram is a global shared budget. The repair is not to issue SET GLOBAL reflexively. For this lab, keep the global setting unchanged and use the session-scoped tmp_table_size experiment below.

Explicit temporary table: prove exact stored values

An explicit temporary table is useful for contrast because it is a real session-scoped SQL object, not an optimizer-created internal structure. Create a tiny deterministic object, verify its definition and rows, then remove it. This gives you exact stored-state evidence before moving back to internal execution work.

CREATE TEMPORARY TABLE tmp_servicehub_cost_check (  work_order_id BIGINT PRIMARY KEY,  region VARCHAR(20) NOT NULL,  parts_cost DECIMAL(10,2) NOT NULL) ENGINE=InnoDB;INSERT INTO tmp_servicehub_cost_check  (work_order_id, region, parts_cost)VALUES  (9001,'north',12.50),  (9002,'south',27.75);SHOW CREATE TABLE tmp_servicehub_cost_check\GSELECT work_order_id, region, parts_costFROM tmp_servicehub_cost_checkORDER BY work_order_id;-- Expected rows:-- 9001 | north | 12.50-- 9002 | south | 27.75DROP TEMPORARY TABLE tmp_servicehub_cost_check;

The result proves what this explicit temporary object stored in this session. It does not describe the hidden internal TempTable object that MySQL may build for the aggregation below; that mechanism is observed through its plan, counters, memory instruments, and I/O effects instead.

Reproduce temporary work safely

SHOW SESSION STATUS WHERE Variable_name IN ( 'Created_tmp_tables','Created_tmp_disk_tables');EXPLAIN ANALYZESELECT region, technician_id,       COUNT(*) AS jobs,       SUM(parts_cost) AS total_partsFROM servicehub_perf_lab.work_ordersWHERE scheduled_at >= '2026-03-01'GROUP BY region, technician_idORDER BY total_parts DESC;SELECT region, technician_id,       COUNT(*) AS jobs,       SUM(parts_cost) AS total_partsFROM servicehub_perf_lab.work_ordersWHERE scheduled_at >= '2026-03-01'GROUP BY region, technician_idORDER BY total_parts DESC;SHOW SESSION STATUS WHERE Variable_name IN ( 'Created_tmp_tables','Created_tmp_disk_tables');

The exact plan can differ with statistics and indexes, but the delta tells you whether this session created internal temporary tables and how many counted as disk tables. Created_tmp_disk_tables has documented limitations for memory-mapped TempTable files, so it is not a universal accounting ledger; on the 8.4 default path mmap is disabled, but verify configuration rather than assuming.

Force a spill in one session—not across the server

SET @original_tmp_table_size := @@SESSION.tmp_table_size;SET SESSION tmp_table_size = 32768;SHOW SESSION STATUS WHERE Variable_name IN ( 'Created_tmp_tables','Created_tmp_disk_tables');SELECT region, technician_id,       COUNT(*) AS jobs,       SUM(parts_cost) AS total_parts,       MAX(payload) AS sample_payloadFROM servicehub_perf_lab.work_ordersWHERE scheduled_at >= '2026-03-01'GROUP BY region, technician_idORDER BY total_parts DESC;SHOW SESSION STATUS WHERE Variable_name IN ( 'Created_tmp_tables','Created_tmp_disk_tables');SET SESSION tmp_table_size = @original_tmp_table_size;

Adding MAX(payload) increases temporary row width; the small per-session threshold makes spill more likely while keeping the experiment isolated. If your server still does not spill, record that result rather than fabricating one. Dataset size, execution algorithm, current TempTable global use, and optimizer choices matter.

SELECT EVENT_NAME,       CURRENT_NUMBER_OF_BYTES_USED,       HIGH_NUMBER_OF_BYTES_USEDFROM performance_schema.memory_summary_global_by_event_nameWHERE EVENT_NAME IN ('memory/temptable/physical_ram',                     'memory/temptable/physical_disk');

The physical_disk TempTable memory instrument is relevant to memory-mapped overflow; when 8.4 default temptable_max_mmap=0 is in effect, an InnoDB internal temp table on disk is not the same mechanism. Correlate status/plan/I/O evidence rather than interpreting one instrument in isolation.

Wrong approach: increase temp limits until the counter disappears

-- Do not copy this to production as a generic fix:-- SET GLOBAL tmp_table_size = 1024*1024*1024;-- SET GLOBAL temptable_max_ram = 8*1024*1024*1024;

Repair the workload before tuning the ceiling

The report scans all regions from March onward, then groups and sorts. Suppose the real ServiceHub dashboard requests one region at a time. Rewrite to express that requirement and give the predicate a covering access path. Temporary aggregation may still be needed, but the amount of base data read and copied can fall substantially.

CREATE INDEX idx_region_schedule_coverON servicehub_perf_lab.work_orders(region, scheduled_at, technician_id, parts_cost);ANALYZE TABLE servicehub_perf_lab.work_orders;EXPLAIN ANALYZESELECT technician_id,       COUNT(*) AS jobs,       SUM(parts_cost) AS total_partsFROM servicehub_perf_lab.work_ordersWHERE region='north'  AND scheduled_at >= '2026-03-01'GROUP BY technician_idORDER BY total_parts DESC;

Compare rows examined/actual rows, elapsed time, temporary-table deltas, and storage I/O on the same dataset. A correct rewrite is preferable to allocating gigabytes merely to make a broader query spill less.

Production judgment

Tune temporary-work limits only after identifying the responsible query shapes and concurrency. Protect both memory and temporary-storage capacity. Keep tmpdir and InnoDB temporary-storage paths on storage with adequate free space and latency, but do not assume every on-disk internal temporary table is literally a file you can watch under tmpdir. Test query/index changes first; then adjust limits with the whole memory budget and concurrent workload in view.

Lesson 4 moves from transient query work to the write path: redo capacity, checkpoint age, dirty-page flushing, and storage I/O.

Authoritative references

• MySQL 8.4 Reference Manual
• MySQL Community Server 8.4 downloads
• Internal Temporary Table Use in MySQL
• CREATE TEMPORARY TABLE
• Server System Variables
• Performance Schema Memory Summary Tables