The alert said the database host was about to run out of disk, and the growth was in tmpdir, which is not where database growth is supposed to live. The files had names that meant nothing to the on-call engineer, they belonged to no tablespace anyone recognized, and they appeared every night during the reporting window. The culprit was the middle stage of MySQL's TempTable engine writing memory-mapped spill files, and the reason nobody caught it earlier is that the classic monitoring ratio for temp tables was completely blind to it. Created_tmp_disk_tables looked perfectly healthy while gigabytes of temporary I/O flowed through a directory nobody was graphing. This is how the TempTable engine's memory limits actually work in MySQL 8.0 and 8.4, and how to see spills before the disk alert does.
The engine landscape since MySQL 8.0
Internal temporary tables are the staging areas the optimizer builds when it cannot stream a result: GROUP BY that index order cannot satisfy, DISTINCT over unsorted input, derived tables, UNION with deduplication, window functions that need buffering. Since MySQL 8.0 the in-memory engine for these is TempTable, selected by internal_tmp_mem_storage_engine, replacing the MEMORY engine that served for decades. The change mattered more than it sounds. MEMORY padded every VARCHAR to its full declared width and refused to hold BLOB or TEXT in memory at all, so byte-heavy aggregations spilled to disk absurdly early. TempTable stores variable-length columns at their real length and keeps BLOB and TEXT in RAM, which means many queries that used to hit disk by policy now stay in memory by default.
One clarification that saves confusion: all of this concerns internal temporary tables the optimizer creates for query execution. A TEMPORARY table you create yourself with CREATE TEMPORARY TABLE is a different animal; it uses whatever engine you name, or default_tmp_storage_engine when you name none, so an InnoDB temporary table lands in the session temporary tablespace while a MEMORY one never touches it, and either way it does not draw from temptable_max_ram. Mixing those two up leads to confidently wrong capacity planning.
Three stages, two ceilings
TempTable allocates from a global pool capped by temptable_max_ram, shared across all sessions. On 8.0 that ceiling defaults to one gibibyte; on 8.4 an unset temptable_max_ram means three percent of the server's total memory, clamped between one and four gibibytes. When a thread cannot get what it needs from that pool, what happens next depends on version and configuration. On 8.0 the overflow goes to memory-mapped temporary files in tmpdir, capped by temptable_max_mmap at one gibibyte by default. On 8.4 the mmap stage ships switched off, with temptable_max_mmap defaulting to zero, so overflow converts straight to the final stage: an on-disk InnoDB temporary table in the session temporary tablespaces, the ibt files. Set temptable_max_mmap above zero and the middle stage comes back on either version. Since 8.0.16 the on-disk engine is always InnoDB; the old MyISAM fallback is gone.
The limit that trips experienced operators is the per-table one. Historically tmp_table_size only mattered to the MEMORY engine, jointly with max_heap_table_size. Since MySQL 8.0.28, tmp_table_size also caps the size of any individual in-memory TempTable table. So on a current server there are two genuinely different spill causes. A single monster aggregation can exceed tmp_table_size, sixteen megabytes by default, and spill while the global pool is nearly empty. Or a reporting window full of medium-sized queries can exhaust the shared temptable_max_ram pool and push everyone into mmap at once. The remedies for those two situations are different knobs, and I regularly see teams tune one while suffering from the other.
Diagnosing with performance_schema
Start by accepting that the traditional health check is now a liar by omission. The ratio of Created_tmp_disk_tables to Created_tmp_tables only counts conversions to on-disk InnoDB tables, the third stage. The entire mmap middle stage is invisible to it, which is exactly how my tmpdir incident hid in plain sight: the server was doing heavy temporary-file I/O with a spotless spill ratio. The honest view is the memory instrumentation. TempTable exposes memory/temptable/physical_ram and memory/temptable/physical_disk events, and the sys schema makes them readable:
SELECT event_name, current_alloc, high_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory/temptable%';
SELECT t.processlist_id AS conn_id,
t.processlist_user AS user,
m.current_number_of_bytes_used AS current_bytes,
m.high_number_of_bytes_used AS high_bytes
FROM performance_schema.memory_summary_by_thread_by_event_name AS m
JOIN performance_schema.threads AS t
ON t.thread_id = m.thread_id
WHERE m.event_name LIKE 'memory/temptable%'
ORDER BY m.current_number_of_bytes_used DESC
LIMIT 10;physical_ram tells you how hard the global pool is being pushed and what its high-water mark is; physical_disk is the mmap stage, and if it carries real numbers, your server is spilling in a way the old counters will never report. The per-thread view is how you find the guilty report connection instead of blaming the fleet. One caveat: memory instrumentation has to be enabled to collect, and it usually is by default, but verify on a fresh install before you trust an empty result.
Tuning decisions I actually make
First, treat tmpdir as production storage. Put it on fast local disk or tmpfs, give it real capacity, and graph its usage, because TempTable mmap files land there and grow without ceremony. The ibt files land somewhere else: the session temporary tablespaces live wherever innodb_temp_tablespaces_dir points, #innodb_temp beneath the data directory by default, so graph that path too. Second, on report-heavy replicas with RAM to spare, raising temptable_max_ram is the cheapest fix in this whole article; it is a ceiling, not a preallocation, so headroom costs nothing until used. Third, decide your spill philosophy explicitly. Keeping the mmap stage on spreads pressure across tmpdir and softens the cliff; setting temptable_max_mmap to zero sends overflow straight to InnoDB temp tables, which is simpler to reason about and avoids tmpdir surprises if your InnoDB temp tablespaces live somewhere roomier. Neither is universally right; the wrong answer is not knowing which one your server does. Fourth, for the one-monster-query case, tmp_table_size is the guardrail: raising it lets a legitimate heavy aggregation finish in memory, and lowering it protects the shared pool from a single greedy query. Set it with the worst query you actually run in mind, not the biggest one you can imagine.
The queries are still the root cause
Everything above manages the symptom. A temp table that spills exists because some query needed to materialize more rows than memory could hold, and the durable fix is usually in the query or the schema: an index that lets GROUP BY stream, a derived table rewritten so it merges, an aggregation pushed down or pre-computed. I use the memory limits to buy safety, not to make spills comfortable. When physical_disk shows sustained activity, that is a backlog of queries worth optimizing. Tune the engine so spills are survivable; tune the SQL so they are rare.
The blind spot worth taking away
If you keep one lesson from this article, keep the blind spot: on my incident night a perfectly healthy Created_tmp_disk_tables ratio proved nothing. One boundary I will not blur: MonPG, where I work, monitors PostgreSQL today and not MySQL yet; MySQL support is in active development, and TempTable is a case study in why per-engine depth matters, because the correct instrumentation here is memory events, not the temp-table counters everyone learned a decade ago. The MySQL monitoring (coming soon) page is where that work lands first. On the PostgreSQL side, the analogous knobs are work_mem and temp_files, and that monitoring already exists on the PostgreSQL monitoring side; the performance_schema setup guide covers enabling the instruments this article depends on, and the blog has the rest of the MySQL series.