A query that ran in 80 ms in staging takes 4 seconds in production, and EXPLAIN looks identical in both places. The plan did not change. The data volume did, and somewhere inside execution, an internal temporary table that fit in memory on the small dataset overflowed onto disk on the big one. Nothing in the plan output distinguishes those two runs; the optimizer decided "Using temporary" either way, and the memory-or-disk decision happened at runtime, per query, based on limits most teams have never tuned and cannot name.
Internal temp table spills are among the most common silent regressions in MySQL 8.x. This guide covers what triggers materialization, how the TempTable engine's three-stage overflow actually works, which limits apply when, and how to hunt down offenders with performance_schema.
What creates an internal temporary table
The optimizer materializes an internal temp table whenever it needs a staging area it cannot stream. The usual suspects: GROUP BY that cannot be satisfied by index order, DISTINCT over unsorted input, ORDER BY on a different set of columns than the GROUP BY, UNION (the deduplicating kind; UNION ALL usually streams), derived tables and materialized common table expressions, window functions that need buffering, multi-table UPDATE, and INSERT ... SELECT reading and writing the same table. EXPLAIN advertises the intent with "Using temporary" in Extra, and EXPLAIN FORMAT=TREE shows materialization steps explicitly.
The key mental model: "Using temporary" is not itself the problem. Small in-memory temp tables are a perfectly good execution strategy, and busy servers create thousands per second without drama. The problem is the size cliff, when a temp table outgrows its memory allowance and gets rebuilt or extended on disk mid-query, turning a CPU-bound aggregation into an I/O-bound one.
The TempTable engine and its three-stage overflow
Since MySQL 8.0, in-memory internal temp tables default to the TempTable engine (internal_tmp_mem_storage_engine), which replaced the old MEMORY engine for this job. It was a genuine upgrade: TempTable handles VARCHAR efficiently instead of padding to full width, and it supports BLOB and TEXT columns in memory, which under MEMORY went straight to disk.
TempTable allocates from a global pool capped by temptable_max_ram, 1 GiB by default, shared across all sessions. When the pool is exhausted, allocation overflows to a second stage: memory-mapped temporary files, capped by temptable_max_mmap (also 1 GiB by default; set it to 0 to skip this stage). Only when mmap space is also exhausted does the engine fall to the third stage, converting the temp table to an on-disk InnoDB table in the session temporary tablespaces, the ibt files under #innodb_temp. Since 8.0.16 the on-disk engine is always InnoDB; the old choice of MyISAM is gone.
There is a second, per-table limit that trips people up. Historically tmp_table_size only governed the MEMORY engine (together with max_heap_table_size, whichever is smaller). As of MySQL 8.0.28, tmp_table_size also caps the size of any individual in-memory TempTable table. So on a current 8.0 or 8.4 server, a single query's temp table can spill because it hit tmp_table_size on its own, even while the global temptable_max_ram pool has plenty of headroom, and the whole server can start spilling because the shared pool is exhausted by concurrency even though no single table is large. Diagnosing which of those happened is half the game.
The counter that lies by omission
The traditional health check is the ratio of two status counters:
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN ('Created_tmp_tables', 'Created_tmp_disk_tables');Useful, but read the fine print: Created_tmp_disk_tables counts conversions to on-disk InnoDB temp tables. The mmap overflow stage does not increment it. A server can be shoveling gigabytes through memory-mapped temp files, doing real disk I/O, while Created_tmp_disk_tables sits flat and the dashboard says everything is in memory. I consider this one of the sneakiest observability gaps in stock MySQL, and the fix is to watch TempTable's own allocation accounting:
SELECT event_name,
current_number_of_bytes_used / 1024 / 1024 AS current_mb,
high_number_of_bytes_used / 1024 / 1024 AS high_water_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name IN ('memory/temptable/physical_ram',
'memory/temptable/physical_disk');physical_ram is the in-memory pool; physical_disk is the mmap stage. A nonzero high-water mark on physical_disk means you have been spilling past RAM whether or not any counter said so. Trend both, alert on the disk one.
Finding the offending queries
Server-wide counters tell you spilling is happening; performance_schema's statement digests tell you who. This is the same move I recommend for slow-query triage generally, aggregated digest evidence over log spelunking, as argued in MySQL slow query log vs pg_stat_statements:
SELECT schema_name,
left(digest_text, 100) AS query_family,
count_star AS calls,
sum_created_tmp_tables AS tmp_tables,
sum_created_tmp_disk_tables AS tmp_disk_tables,
round(sum_created_tmp_disk_tables / count_star, 2) AS disk_per_call,
round(sum_timer_wait / 1e12, 1) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_created_tmp_disk_tables > 0
ORDER BY sum_created_tmp_disk_tables DESC
LIMIT 15;A disk_per_call near 1.0 means the query spills every single execution: that is structural, fix the query or the schema. A low ratio that climbs during peak hours means concurrency is exhausting the shared pool: that is capacity. For a live view of who is currently occupying session temp space, information_schema.innodb_session_temp_tablespaces joins back to processlist IDs and will name the connection sitting on 30 GB of ibt files.
Verifying a spill on a live query
Digest tables give you history; sometimes you need to catch a spill in the act. EXPLAIN ANALYZE (8.0.18+) executes the query and reports actual row counts and timings per plan node, so a materialization step whose cost dwarfs its estimate is visible directly, and a temp-table aggregation node that takes 3 seconds on production data but milliseconds in staging is your spill, even though plain EXPLAIN printed the same plan in both places. For a query already running, performance_schema.events_statements_current exposes CREATED_TMP_DISK_TABLES for the in-flight statement, and watching the #innodb_temp directory grow while it runs is as unambiguous as evidence gets.
One habit worth building: when a report or dashboard query regresses after a data-volume milestone rather than after a deploy, suspect a spill before you suspect a plan change. Plan regressions correlate with releases, statistics refreshes, and upgrades; spill regressions correlate with growth. The two get conflated constantly, and they have different fixes: a plan regression wants an index or an optimizer hint, while a spill regression wants narrower temp rows, more temp memory, or aggregation pushed into an index. Checking the digest table's tmp_disk_tables delta for that one query family settles the question in thirty seconds.
Fix the query first, the knob second
The durable fixes are almost always in the SQL. An index that matches the GROUP BY lets aggregation stream without materializing. Trimming SELECT lists keeps temp rows narrow; dragging a TEXT column through a GROUP BY bloats every stage. Derived tables that get re-materialized per outer row can often become joins. LIMIT with ORDER BY on an indexed prefix avoids sorting the world.
When the knob is the right answer, size deliberately. Raising temptable_max_ram trades global memory for spill avoidance, and it comes out of the same physical RAM budget as the buffer pool, the topic of InnoDB buffer pool vs PostgreSQL shared_buffers, so a 4 GiB temp pool on a 16 GiB box means a smaller cache for everything else. Raising tmp_table_size helps the single-big-table case. I set both based on the measured high-water marks above, not on forum numbers, and I leave headroom: temp memory is demand-driven and spiky, and the OOM killer does not read tuning guides.
MonPG and MySQL, stated plainly
MonPG is a PostgreSQL monitoring platform. It does not monitor MySQL today, and MySQL support is currently in development, tracked on the MySQL monitoring (coming soon) page. Temp table spill detection is a design commitment for that work precisely because stock MySQL makes it so easy to miss: the plan is TempTable RAM and mmap high-water marks trended by default, digest-level spill attribution one click from the server-level graph, and the mmap blind spot handled honestly instead of pretending Created_tmp_disk_tables tells the whole story. PostgreSQL has the same disease under a different name, work_mem spills and temp_blks_written, and if you run Postgres too you can start there now while the MySQL side lands.