The disk alert fired at 07:40 on the first of the month, on a MySQL 5.7 primary whose data volume had been growing at a predictable 1.5GB a day for a year. Overnight it had grown by 38GB, and none of it was in any application table. The ibdata1 file had ballooned from 6GB to 44GB while the finance team's month-end reconciliation report ran — one REPEATABLE READ transaction, opened at 21:15, finished at 08:03 the next morning. When the report finally committed, I watched the disk, expecting the space to come back. It never did. Not after the commit, not after a restart, not after three weeks of waiting. The file had grown to hold the undo of eleven hours of everyone else's writes, and InnoDB files only ever grow in one direction.
Years later, on MySQL 8.0, the same report pattern grew one of the undo tablespaces to 14GB in an afternoon — but this time I got the space back in twenty minutes without downtime, because 8.0 keeps undo logs in separate undo tablespaces and knows how to truncate them. The difference between those two incidents is the whole topic: what undo logs actually store, why one idle transaction inflates them for the entire server, what innodb_max_undo_log_size and innodb_undo_log_truncate really do, the manual truncate surgery for when you want the disk back now, and the monitoring that catches this before the volume fills.
What do undo logs actually store?
Before-images: the previous version of every row your transaction modifies, written so InnoDB can roll the change back and so other transactions can read a consistent snapshot. When an UPDATE changes a row, InnoDB copies the old row version into an undo log record first, then modifies the row in place and points it at its undo chain. Rollback is the obvious consumer — a ROLLBACK or a crash recovery walks the undo records in reverse and puts every row back. The less obvious one is MVCC: a transaction running under REPEATABLE READ with a snapshot from an hour ago reads not the current row but the version reconstructed by walking undo records back to its point in time. Undo records live in rollback segments, which live in undo tablespaces — innodb_undo_001 and innodb_undo_002 on a default 8.0 install — and they come in two flavors with different lifetimes. Insert undo exists only for rollback, so it is discarded the moment the transaction commits. Update undo is the expensive kind: it must survive until no open read view anywhere on the server could still need that old row version, and only the purge thread gets to decide when that moment has arrived. Temporary-table undo is a third, separate stream that skips redo logging entirely, since temp tables never need crash recovery.
Why does one long transaction inflate undo for everyone?
Because purge can only discard undo records older than the oldest active read view on the server, so a single idle transaction with an old snapshot pins purge for the entire instance. The month-end report took its read view at 21:15 and held it for eleven hours. During those hours the application kept writing — roughly nine million row modifications across the night shift, order updates, inventory moves, session touches — and every one of those updates produced an update-undo record that purge was not allowed to touch, because the report's snapshot might still need to reconstruct any of those rows. The backlog of undo waiting for purge is the history list, and I watched it climb past 2.4 million entries that night, which is the same number the history list length field notes treat as a five-alarm fire. The mechanics matter for diagnosis: the transaction that caused the growth was doing almost nothing itself. It issued one big SELECT every few minutes and slept the rest of the time. The undo volume was produced by healthy, fast application transactions whose only crime was running while an old read view existed. And the pinning is not per-table — the report touched three tables, but its read view covers the whole instance, so undo for every table piled up together. If you have ever hunted a disk-growth mystery and found nothing in the slow query log, this coupling is why: the guilty session shows up as Sleep in the processlist while everyone else's writes do the growing, which is exactly the detection pattern in long transaction detection.
How do innodb_max_undo_log_size and innodb_undo_log_truncate actually work?
innodb_max_undo_log_size is a threshold that makes an undo tablespace eligible for truncation — not a hard cap — and innodb_undo_log_truncate enables the background process that actually reclaims it. The default maximum is 1GB, and a tablespace that exceeds it does not stop growing or raise an error; it simply becomes a candidate. With truncation enabled, the server picks a candidate, stops assigning new rollback segments in it, waits for purge to finish with the undo logs already there, then truncates the file back to its small initial size and returns it to service. There is a hard requirement that explains a lot of dead ends: truncation needs at least two undo tablespaces, because one must stay active to absorb new work while the other drains. That requirement is where 5.7 and 8.0 diverge. In 8.0, two undo tablespaces exist by default and truncation works out of the box. In 5.7, innodb_undo_tablespaces defaults to zero, meaning all undo lives in the system tablespace — and undo in ibdata1 can never be truncated by any setting. My 44GB ibdata1 was that case. The 5.7 fix required setting innodb_undo_tablespaces to 2 or more, which is not dynamic in that version, so it took a planned restart; the space already inside ibdata1 stayed there forever, because InnoDB reuses freed pages internally but never returns them to the filesystem. Reclaiming it meant a logical dump and rebuild of the instance — a full weekend, for a number on a graph. If you are still on 5.7 with undo in the system tablespace, moving undo out is the single highest-value change you can schedule.
What does the manual truncate surgery look like in MySQL 8.0?
Mark the oversized undo tablespace inactive, let purge drain it, and the server truncates and reactivates it automatically — no restart, no downtime, and safe to run during production traffic. Automatic truncation fires on its own schedule, which may be hours after the disk alert that got you paged, so doing it by hand is a standard piece of DBA plumbing. The sequence I ran on the 14GB tablespace:
-- what undo tablespaces exist, and how big are they on disk?
SELECT FILE_NAME, FILE_SIZE
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';
-- take the oversized one out of rotation
-- (the name is the basename of the .ibu file, without extension)
ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
-- watch it drain: new transactions no longer use it, purge empties it,
-- and once empty the server truncates the file and flips it back to 'active'
SELECT NAME, STATE
FROM information_schema.INNODB_TABLESPACES
WHERE NAME LIKE 'innodb_undo%';Three honest caveats. First, draining is not instant: existing undo logs in the inactive tablespace must be purged before truncation, and if your history list is deep — say, the report that caused this is still running — the tablespace sits in an empty-pending state until purge catches up. Truncating while the pinning transaction is still open accomplishes nothing, so kill or wait out the long transaction first. Second, during the drain you are running on fewer undo tablespaces, which concentrates rollback-segment contention on what remains; on a normal workload this is a non-event, but I would not start the surgery in the middle of a peak-hour write storm on an instance with exactly two tablespaces. Third, the reclaimed space returns to the filesystem only when the truncate happens — an inactive-but-not-yet-empty tablespace still holds its bytes. After my run, df showed the 14GB back within the same minute the state flipped to active.
Why does undo growth look like disk pressure with no obvious query?
Because the growth is written by transactions that are not the problem, on behalf of a transaction that is doing nothing — so every query-centric tool shows you a healthy server while the disk fills. The slow query log is clean because the application writes are fast and the report's SELECTs may even be indexed well. Per-table growth checks show nothing because undo is not attributed to any application table. The processlist shows the culprit as a sleeping connection that last ran a SELECT forty minutes ago, which looks like the least suspicious row on the screen. What the DBA actually experiences is a volume-filling alert on the datadir mount, a frantic du that fingers ibdata1 or undo files, and a game of whodunit with no obvious suspect. Two discipline notes from having lived this. The file-level view is the fastest triage: du -h over the datadir sorted by size, or the information_schema.FILES query above, tells you in seconds whether you are chasing undo, redo, binlog, temp tables, or real data — undo has a signature because ibdata1 and the .ibu files are the only InnoDB files that grow without any corresponding table growth. And the permanent fix is never the truncate; it is the transaction boundary. The month-end report now runs against a replica instead of the primary, and where it must run on the primary it commits and re-snapshots between chunks, accepting slightly inconsistent totals across chunk boundaries in exchange for never holding an eleven-hour read view. That trade — perfect consistency versus an instance-wide purge stall — is the real decision, and the right answer is almost never the perfect snapshot.
How do you monitor undo growth before the disk alert fires?
Track the history list length, the undo tablespace file sizes, and the age of the oldest open transaction — those three numbers tell the whole story before any filesystem gets involved. The history list length is exposed through the innodb_metrics table; the counter ships disabled on some builds, so enable it explicitly and then read it like any gauge. The FILES table gives you on-disk sizes of the undo tablespaces, the INNODB_TRX view gives you the oldest transaction, and a handful of status variables count the tablespaces themselves:
-- history list length: enable once, then it updates continuously
SET GLOBAL innodb_monitor_enable = 'trx_rseg_history_len';
SELECT NAME, COUNT, MAX_COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';
-- on-disk undo tablespace sizes
SELECT FILE_NAME, FILE_SIZE
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';
-- who is holding purge back right now?
SELECT trx_id, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
trx_state, trx_mysql_thread_id
FROM information_schema.INNODB_TRX
ORDER BY trx_started
LIMIT 5;
-- undo tablespace inventory
SHOW GLOBAL STATUS LIKE 'Innodb_undo_tablespaces%';Alerting thresholds, from experience rather than the manual. History list length is workload-relative — a busy OLTP primary can idle in the tens of thousands — but a sustained climb past a few hundred thousand means purge is pinned, and the growth rate matters more than the level. Any transaction older than your longest legitimate query by an order of magnitude deserves a page: my rule is oldest-transaction age over thirty minutes on a system whose longest job is two minutes. Undo tablespace file size is the lagging indicator — by the time it triples, the other two alerts should already have fired — but trend it anyway, because the rate at which undo grows tells you how much runway the volume has. The sys schema wraps several of these views if you prefer it, and the daily-driver queries in the sys schema field notes complement what is above. One gap to be aware of: INNODB_METRICS counters are dynamic and reset on restart, so if you graph them, graph the rate or the current value, never a naive difference.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — history list length as a timeline, undo tablespace file sizes against their thresholds, oldest-transaction age as a first-class alert — are exactly what the MySQL work is designed to surface, so a pinned read view shows up as a graph hours before it shows up as a disk alert. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side today, and the rest of these MySQL field notes live on the blog.