The 2 a.m. page said the data volume was at 94 percent and climbing. The largest file on it was not a table. It was ibtmp1, sitting at 180 GB, wearing the word temporary in its name. A restart fixed it, because ibtmp1 is recreated at startup, and the file came back at 12 MB while everyone went back to bed. But nobody could say why it had grown, so nobody could say whether it would come back. It came back, eleven days later, during the same month-end report run.
Temporary tablespace bloat is one of those MySQL problems that is simple once you see the machinery and baffling before. So, the machinery: the two kinds of temporary tablespace MySQL 8.0 keeps, what actually lands in each, why ibtmp1 only ever grows during runtime, how to put a ceiling on it, and how to name the session responsible instead of just restarting and hoping.
Two kinds of temporary tablespace
MySQL 8.0 splits temporary storage in two, and conflating them is the root of most confusion. Session temporary tablespaces are a pool of about ten files, temp_1.ibt through temp_10.ibt, living in the #innodb_temp directory. Each session that needs on-disk temporary storage gets assigned from the pool, and these files hold the actual data: on-disk internal temporary tables the optimizer materializes, and the contents of user-created CREATE TEMPORARY TABLE tables. When a session drops its temp tables or disconnects, its space is released back to the pool, and everything under #innodb_temp is recreated fresh at startup.
The global temporary tablespace, ibtmp1, serves a different purpose: it holds the rollback segments for changes made to temporary tables, the undo that lets InnoDB back out temp table modifications when a transaction rolls back. Different purpose, different file, different growth pattern. When someone says the temp tablespace ate the disk, the first question is which one they mean, because the fix and the culprit differ.
What actually lands on disk
Internal temporary tables follow a staged path. Aggregation and materialization work, GROUP BY, DISTINCT, derived tables, starts in memory under the TempTable engine, in a shared pool capped by temptable_max_ram. What happens at the limit depends on version. On 8.0, allocation overflows to memory-mapped files capped by temptable_max_mmap, which defaults to 1 GB there, and only when that too is exhausted does the table convert to an on-disk InnoDB table in the session temporary tablespaces. On 8.4, temptable_max_mmap defaults to 0, the mmap stage is effectively off, and exhausting the RAM pool converts the table straight to on-disk InnoDB. Since 8.0.28, tmp_table_size also caps any single in-memory temp table, so one ambitious query can spill on its own while the shared pool still has headroom.
Big sorts and hash join spills are a different path that gets blamed on the same files. Filesort and hash join overflow write temporary files in tmpdir, not into either tablespace. Same symptom, disk growing during a query, but a different directory and a different fix, and half the forum threads about ibtmp1 are actually tmpdir files. Check which directory is growing before you tune anything.
ibtmp1 itself grows when sessions make large or long-lived changes to temporary tables and the rollback segments balloon to match. The classic driver is a report or ETL job that builds big user temp tables and then updates them inside one long transaction: every update generates undo in those rollback segments, and the segments grow with the job. That was our month-end report, to the gigabyte.
Why ibtmp1 only ever grows
The default configuration tells the whole story: innodb_temp_data_file_path defaults to ibtmp1:12M:autoextend. The file starts at 12 MB, extends on demand, and never shrinks while the server runs. InnoDB tablespaces are high-water-mark files: freeing space inside the file returns it to InnoDB's free lists, not to the filesystem, and there is no online shrink for ibtmp1. The file is recreated at its initial size during startup, so a restart is the only way back down.
Early 8.0 releases also had genuine runaway-growth bugs in this area, fixed long ago, so on a current 8.0 or 8.4 what remains is architectural high-water-mark behavior plus workloads that legitimately needed the space at least once. That reframes the operational question. It is never how to shrink ibtmp1 online, because you cannot. It is how big you are willing to let it get, and whether you know why it got there.
Cap it before it eats the volume
The ceiling lives in the same variable: innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:8G gives autoextend a maximum. What happens at the ceiling matters: statements that need more temporary rollback space fail with an error, while the server stays up and everything else continues. A failed report is a much better 2 a.m. than a full data volume, because a full data volume can stop writes for every database on the instance, not just the one with the greedy query.
Size the cap from observed usage, not from vibes: watch the file for a few weeks through your real peak, including month-end, and set the ceiling with headroom above the observed high-water mark but well inside the volume. Reclaiming space is still a restart, so plan it like one: roll through replicas first, or use a quiet window, and the file returns at 12 MB with the cap keeping it honest. If sorts and hash joins are heavy on the same host, give tmpdir its own volume too, so runaway spill files cannot fill the data volume through the side door.
Name the session, not just the file
Session temporary tablespaces are directly observable, and the id column is the processlist id, which makes naming the culprit a join away:
SELECT s.id AS processlist_id,
s.path,
round(s.size / 1024 / 1024, 1) AS size_mb,
s.state, s.purpose,
p.user, p.host, p.time AS seconds,
left(p.info, 80) AS current_query
FROM information_schema.innodb_session_temp_tablespaces s
LEFT JOIN information_schema.processlist p ON p.id = s.id
ORDER BY s.size DESC;The purpose column distinguishes user temporary tables from internal optimizer materialization, which tells you whether to go lecture a report job or go fix a query plan. For ibtmp1's own size, information_schema.files knows it as the innodb_temporary tablespace. And for history rather than the live snapshot, the statement digests carry per-query disk-table counts, with one blind spot explained below:
SELECT schema_name,
left(digest_text, 100) AS query_family,
count_star AS calls,
sum_created_tmp_disk_tables AS disk_temp_tables
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_created_tmp_disk_tables > 0
ORDER BY sum_created_tmp_disk_tables DESC
LIMIT 10;The blind spot first: sum_created_tmp_disk_tables increments only when a table actually converts to on-disk InnoDB. On 8.0's default mmap path a query can churn gigabytes of temporary space while this counter stays at zero, so a clean row here means no conversions, not no temp I/O. With that understood: a query family that spills on every call is structural, so fix the query or the indexing. A family that only spills during peak hours usually means concurrency exhausting the shared pool, which is a capacity answer, but check before buying RAM, because peak hours also bring bigger inputs and plan changes; confirm the per-call cost looks the same at peak before blaming the pool. If you do not have the digest infrastructure humming yet, the setup is covered in Performance Schema with low overhead, and the triage habit in the slow query digest workflow.
Prevention is query work, mostly
The durable fixes are almost always in the SQL. An index that lets GROUP BY stream avoids materialization entirely. Narrow SELECT lists through aggregations keep temp rows slim. Report jobs that build a huge user temp table and then update it in one endless transaction are the ibtmp1 killers: chunk the updates, commit between stages, and the rollback segments stay modest. Raise temptable_max_ram or tmp_table_size only after the measurements above say memory is the binding constraint, and remember that every gigabyte of temp headroom comes out of the same physical RAM budget as the buffer pool.
For monitoring, trend two numbers daily: ibtmp1's size, and the largest session temp tablespace. Alert on deviation from baseline rather than absolutes, because a workload that legitimately needed 40 GB last month will need it again, and the interesting event is the need changing, not the number existing.
MonPG, and where MySQL stands
Since this series mentions tooling, here is mine: I work on MonPG, which monitors PostgreSQL today and not MySQL; MySQL support is being built. This incident is on its list precisely because stock MySQL makes the failure so quiet: ibtmp1 and session temp tablespace sizes trended over time, per-session attribution one click away from the growth, digest spill history beside it, so the 2 a.m. version of this story ends with a named query rather than a restart. Watch the MySQL monitoring (coming soon) page for it. On the PostgreSQL side, where the cousin of this problem is work_mem spills and temp file bytes, that visibility already exists on the PostgreSQL platform; the comparisons show the rest, and the blog has the remaining MySQL field notes.