The disk alert made no sense: the undo directory was growing two gigabytes an hour on a database whose actual data grew maybe four gigabytes a month. By the time I got a shell, undo_002 was 88 gigabytes and the volume had eleven hours of headroom left. The cause, when I found it, was a BI tool that had opened a REPEATABLE READ snapshot on the primary fourteen hours earlier for a report nobody was watching anymore. Nothing was wrong with the data. The database was simply keeping every row version that snapshot might still ask for, and undo is where those versions live.
Why does undo grow at all?
Because MVCC never destroys a row version someone can still see. Every UPDATE and DELETE writes the before-image of the row into an undo log, and InnoDB's purge threads may only remove versions older than every open read view in the system. One long-lived transaction — one snapshot — pins purge for everything written after it started, and undo then accumulates at your write rate for as long as that snapshot stays open. The number that tells the truth is the history list length — the History list length line in SHOW ENGINE INNODB STATUS, or trx_rseg_history_len in INNODB_METRICS when you want to scrape it; the mechanics are covered in depth in history list length and purge lag, and the hunting side in long transaction detection. The lesson to internalize: undo growth is never the bug. The lesson to internalize: undo growth is never the bug. It is the symptom of an unbounded snapshot.
Not all undo is equal, and the distinction explains some growth surprises. Insert undo — the before-image of an INSERT — can be discarded the moment the transaction commits, because no other transaction can ever need a version of a row that did not exist before that commit. Update undo, produced by UPDATEs and DELETEs, must survive until no open read view can still see the old version, and only purge may remove it. A delete-heavy batch job therefore generates undo that lingers exactly as long as your oldest snapshot, which is why the nightly cleanup job and the abandoned report make such a toxic pair.
How does innodb_undo_log_truncate reclaim the space?
Automatic truncation is on by default in 8.0 and works like this: when an undo tablespace file grows past innodb_max_undo_log_size — one gigabyte by default — the server marks that tablespace inactive, steering new transactions away from it while existing ones finish. Once purge has drained everything the tablespace holds, the file is truncated back to its small initial size and reactivated. Two consequences matter. First, truncation needs at least two undo tablespaces, because one must stay active while the other drains; the 8.0 default of two is exactly that floor. Second, truncation is gated by purge, and purge is gated by your oldest snapshot — the same stuck transaction that inflates undo also delays its reclamation. Killing the snapshot is always step one; Killing the snapshot is always step one; resizing knobs while it lives just gives the leak a bigger bucket.
You can watch the machinery work. On a healthy write-heavy system the undo files cycle: a tablespace grows past the limit, flips to inactive, drains, and comes back small, over and over. The pathological pattern is a tablespace sitting inactive for hours, which means purge cannot drain it, which almost always means an ancient read view upstream. Purge capacity itself comes from innodb_purge_threads — four by default — chewing through innodb_purge_batch_size chunks of undo log per pass. If the history list keeps growing with no long transaction in sight, that pairing is where I look next.
How do you manage undo tablespaces by hand?
Since 8.0.14, undo tablespaces are first-class objects you create and drop with SQL, which is how you get above the two-tablespace floor for headroom, or spread undo I/O across volumes. Files live in innodb_undo_directory, and the dance for retiring one is mark-inactive, wait for empty, drop:
CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
-- later, to retire it:
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
-- wait until STATE shows empty in INNODB_TABLESPACES, then:
DROP UNDO TABLESPACE undo_003;The server enforces a minimum of two active undo tablespaces at all times, so if you add before you drop, you cannot paint yourself into a corner. In practice I rarely manage these by hand on small systems; the feature earns its keep on write-heavy boxes, where a stuck truncate on one tablespace no longer stalls everything behind it.
How do you monitor undo growth before the disk fills?
Two queries cover it. The first reads the tablespace metadata directly — name, state, on-disk size:
SELECT name,
state,
ROUND(file_size / 1024 / 1024 / 1024, 2) AS file_gb,
ROUND(allocated_size / 1024 / 1024 / 1024, 2) AS allocated_gb
FROM information_schema.innodb_tablespaces
WHERE space_type = 'Undo'
ORDER BY file_size DESC;The second finds the pinner before the file grows: the oldest open transactions, ordered by age, with what they are running:
SELECT trx_id,
trx_mysql_thread_id,
TIMESTAMPDIFF(MINUTE, trx_started, NOW()) AS age_minutes,
LEFT(trx_query, 80) AS current_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;Alert on the pair — undo gigabytes climbing together with an oldest-transaction age over your threshold — and you catch the incident shape hours before the volume does. The full disk-side triage for when you are already in the red is in disk full emergencies.
FILE_SIZE versus ALLOCATED_SIZE deserves a sentence of its own, because it fools people. FILE_SIZE is the apparent size of the .ibu file — roughly what ls reports. ALLOCATED_SIZE tracks pages actually allocated inside it, and the two diverge after truncate cycles and on filesystems with sparse-file behavior. When I alert, I alert on ALLOCATED_SIZE, because the volume fills on allocated blocks, not apparent length; FILE_SIZE is the conservative ceiling, useful mainly for knowing the worst a naive copy can stream.
What does the classic stuck-report incident look like?
Always the same silhouette. A reporting or ETL tool opens a REPEATABLE READ transaction, runs for hours or gets abandoned mid-run, and undo grows at the write rate of the whole primary. mysqldump --single-transaction on a big database is the other repeat offender — one snapshot held for the entire dump. The fix order never changes: kill the pinning transaction and watch growth stop flat; let purge drain, watching the history list collapse while purge CPU ticks up; let truncation reclaim the files; and only then have the conversation about bounding report queries, moving them to a replica, or enforcing a max execution time. Every postmortem I have written on this incident ends with the same two monitors: oldest transaction age and history list length. The undo file size is just where the bill arrives.
Where MonPG stands on MySQL
I build MonPG, so the plain disclaimer: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. Purge lag, undo tablespace growth, and pinned snapshots are exactly the signals the MySQL work is meant to graph over time, so a stuck report reads as a rising curve with a named culprit instead of a 2 a.m. disk alert. The MySQL monitoring (coming soon) page tracks that work as it ships. Until it lands, the same philosophy runs on the PostgreSQL side, and the rest of these MySQL field notes are on the blog.