Purge, History, Long Transactions, Undo Growth, and Bloat-Like Operational Symptoms

Learning outcomes

ServiceHub has a puzzling incident: a batch UPDATE finished quickly, yet InnoDB storage remains large and background work continues for minutes. Another engineer suggests running PostgreSQL-style VACUUM. That advice crosses database-engine mental models. InnoDB implements multi-version concurrency control (MVCC) using undo history and asynchronous purge; it does not use PostgreSQL’s dead-tuple/VACUUM mechanism. Old row versions remain necessary as long as an active read view could still need them.

The history list is a practical indicator of committed undo history waiting to become purgeable. A long transaction—sometimes even a session that is “just reading”—can keep an old snapshot open and prevent purge from discarding versions created after that snapshot. Updates and deletes can therefore create sustained undo/history pressure, additional I/O and storage growth. “The UPDATE already committed” does not mean every obsolete version has already disappeared.

1. Reset a lab with enough rows to create visible version history

DROP DATABASE IF EXISTS servicehub_innodb_lab;CREATE DATABASE servicehub_innodb_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_innodb_lab;CREATE TABLE digits (n TINYINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE TABLE work_orders (  work_order_id BIGINT NOT NULL AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status VARCHAR(20) NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  summary VARCHAR(180) NOT NULL,  notes LONGTEXT NULL,  PRIMARY KEY (work_order_id),  KEY ix_work_orders_status (status, priority, work_order_id),  KEY ix_work_orders_customer (customer_id, opened_at)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;INSERT INTO work_orders(customer_id,status,priority,opened_at,summary,notes)SELECT 1 + (x.n % 250),       ELT(1 + (x.n % 4),'queued','open','closed','cancelled'),       1 + (x.n % 5),       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL x.n SECOND,       CONCAT('ServiceHub order ', x.n),       RPAD(CONCAT('diagnostic-note-',x.n,' '), 1200, 'x')FROM (  SELECT a.n + 10*b.n + 100*c.n + 1000*d.n AS n  FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits d) AS xWHERE x.n BETWEEN 1 AND 8000;

SHOW VARIABLES WHERE Variable_name IN ('innodb_undo_tablespaces','innodb_undo_log_truncate',  'innodb_max_undo_log_size','innodb_purge_threads');SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_history_list_length','Innodb_available_undo_logs');SHOW ENGINE INNODB STATUS\G

The history-list length can be near zero or already nonzero because background work and other sessions share the server. Capture a baseline; the lab is about direction and blocker correlation, not producing one exact numeric target.

2. Why purge must respect old read views

When a transaction updates a row, InnoDB preserves enough previous state in undo to reconstruct older versions. A consistent read at REPEATABLE READ can keep referring to its earlier snapshot even after other transactions commit new versions. Purge must therefore wait until no active read view can see those old versions before permanently removing obsolete history. This is correctness, not housekeeping inefficiency.

A common application bug is an idle transaction created by a connection pool: the code begins a transaction, runs a SELECT, then waits on an HTTP call or returns the connection without committing. The SQL thread looks idle, but the transaction age and read view remain operationally significant.

3. Two-session lab: create a purge blocker safely

USE servicehub_innodb_lab;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION WITH CONSISTENT SNAPSHOT;SELECT COUNT(*), SUM(priority) FROM work_orders;-- Keep this transaction open while Session B runs.

USE servicehub_innodb_lab;UPDATE work_orders SET priority = 1 + MOD(priority,5)WHERE work_order_id BETWEEN 1 AND 3000;UPDATE work_orders SET summary = CONCAT(summary,' v2')WHERE work_order_id BETWEEN 1 AND 3000;UPDATE work_orders SET summary = REPLACE(summary,' v2',' v3')WHERE work_order_id BETWEEN 1 AND 3000;COMMIT;SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length';SELECT trx_id,trx_state,trx_started,trx_mysql_thread_id,trx_queryFROM information_schema.INNODB_TRXORDER BY trx_started;

History-list growth depends on exactly what the server purges between measurements, but Session A should remain visible as an old transaction/read view. The important diagnostic relationship is transaction age + active snapshot + growing history under write workload.

4. End the blocker and watch asynchronous recovery

COMMIT;

SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length';SHOW ENGINE INNODB STATUS\G-- Repeat after a short interval rather than forcing purge with unsafe changes.SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length';

Purge is asynchronous, so the value may not drop immediately to the original baseline. Give the normal purge threads time to work while watching I/O and workload latency. The safe repair for an accidental stale read view is usually to fix/terminate the owning transaction through the application incident process—not to crank purge variables first.

5. Updates/deletes can create “bloat-like” symptoms without PostgreSQL VACUUM

InnoDB can retain page space and reuse it for future records even when DELETE removes rows logically. File-per-table tablespaces do not necessarily shrink to the filesystem immediately. DATA_FREE, table/index length and file size are therefore capacity clues, not direct evidence that “dead tuples need VACUUM.” Rebuilding a table can reclaim/reorganize space but is a DDL operation with lock, I/O, disk-headroom and replication implications.

Keep two questions separate during an incident. History pressure asks whether obsolete versions are still required by old snapshots and therefore cannot yet be purged. Space reclamation asks what happens after versions become purgeable: freed space may remain inside InnoDB pages/tablespaces for reuse, while returning bytes to the operating system can require undo-tablespace truncation or a planned table rebuild depending on which structure grew. Mixing these questions leads teams to perform expensive DDL while the real blocker is still an open transaction.

6. Diagnose the oldest transaction before changing purge knobs

SELECT trx_id,       TIMESTAMPDIFF(SECOND,trx_started,NOW()) AS age_seconds,       trx_state,trx_mysql_thread_id,trx_rows_modified,trx_queryFROM information_schema.INNODB_TRXORDER BY trx_started;SHOW FULL PROCESSLIST;SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length';

Correlate the MariaDB thread ID with application connection metadata, logs and request traces. A long-running transaction may be legitimate—for example, a consistent export—but it still needs an explicit duration budget. If it is required, schedule write-heavy work around it or use an architecture that avoids holding an old snapshot on the primary workload path.

7. Deliberately wrong approach: tune purge before fixing the blocker

Increasing innodb_purge_threads or changing purge batch/lag settings cannot make it correct to delete versions still visible to an active read view. More purge capacity only helps once history is eligible for purge. The repaired incident sequence is: prove history growth, identify the oldest transaction/read view, decide whether it is valid, end/fix the accidental blocker, observe purge recovery, and only then evaluate whether sustained eligible history still exceeds purge capacity.

MariaDB also provides innodb_max_purge_lag and related variables that can throttle DML when history grows, plus undo tablespace truncation controls. Those are policy/tuning mechanisms, not substitutes for transaction hygiene. Chapter 18 will revisit them with workload measurements.

8. Lab checklist, knowledge check, and bridge

1. Capture baseline history-list and undo configuration.
2. Open Session A with a consistent snapshot.
3. Run the bounded update sequence in Session B and commit.
4. Observe history-list direction and identify Session A in INNODB_TRX.
5. Commit Session A and observe asynchronous purge catch-up.
6. Explain why the table file may not shrink even though rows/old versions become reclaimable.
7. Record the application fix that prevents idle transactions from living indefinitely.

The final lesson turns all of these mechanisms into an evidence-based tuning workflow. Instead of prescribing “80% RAM” or “bigger redo,” you will establish a baseline, change one low-risk property, compare counters/latency, and keep only changes supported by the workload.