MariaDB InnoDB Purge Threads & Undo Truncation: Taming History List Length

In modern relational database architectures, high write concurrency relies on Multi-Version Concurrency Control (MVCC). When a transaction updates or deletes a row in MariaDB InnoDB, the existing row version is not immediately overwritten on disk; instead, the previous state is preserved in the Undo Log. This architecture enables concurrent transactions to execute non-blocking consistent reads without acquiring locks on table rows.

However, once transactions commit, those historical undo records must be cleaned up (purged) from the database by background worker threads. The volume of unpurged undo records waiting for deletion is tracked as the History List Length (HLL).

On write-intensive systems—such as high-frequency payment gateways, ERP ledgers, or WooCommerce order processing backends across Pakistan—a default single-threaded purge configuration frequently falls behind write velocity.

When the History List Length balloons into hundreds of thousands or millions of pages, two severe performance degradations occur:

1. Query Degradation: Every SELECT query must traverse extensive linked chains of undo records to reconstruct the visible row snapshot, turning 5ms queries into 500ms crawls.
2. Uncontrolled Disk Bloat: Undo tablespaces expand continuously to dozens or hundreds of gigabytes, consuming valuable NVMe storage that cannot be reclaimed without truncation.

In this deep architectural guide, we demonstrate how to scale parallel InnoDB purge threads, configure dedicated undo tablespaces, and automate online undo truncation.

---

The Mechanics of MVCC and History List Length (HLL)

Observe the lifecycle of a row version in InnoDB:

[Transaction 1 Updates Row]
          │
          ├── Writes new version to Tablespace (ibd)
          └── Writes previous version to Undo Log (ibtmp / undo tablespace)
          │
[Transaction 1 Commits]
          │
          ▼
[Undo Record Enters History List] (History List Length increments +1)
          │
          ▼
   [InnoDB Purge Subsystem]
          │
  Is any older open transaction still reading this snapshot?
          │
     ┌────┴────┐
     ▼         ▼
   [YES]     [NO]
     │         │
     │         ▼
     │   [Purge Thread Frees Page]
     │   [History List Length decrements -1]
     │
     ▼
[Blocked by Long-Running Transaction or Backup!]
(HLL balloons to 1,000,000+ -> Tablespace expands to 50GB!)

When purge threads cannot keep up with transaction commit velocity, the history list expands rapidly. Any subsequent read of an updated row must follow pointers across fragmented undo pages, exhausting CPU caches and triggering excessive disk reads.

---

Step 1: Auditing History List Length & Undo Bloat

To determine whether your MariaDB database is suffering from purge lag, inspect the TRANSACTIONS section of the InnoDB engine status:

SHOW ENGINE INNODB STATUS\G

Locate the History list length counter:

------------
TRANSACTIONS
------------
Trx id counter 148920194
Purge done for trx's n:o < 147820100 undo n:o < 0 state: running
History list length 1,482,910

Diagnostic Thresholds:

• HLL < 5,000: Optimal. Purge threads are operating in real-time synchronization with write activity.
• HLL 5,000 to 50,000: Moderate load. Typical during batch updates or heavy import operations.
• HLL > 100,000: Critical Purge Lag. Immediate action required. Read queries are suffering severe latency degradation.

Inspect the physical size of your undo files on disk:

ls -lh /var/lib/mysql/undo*

If your undo tablespaces have expanded past 10GB to 50GB, automated truncation is urgently required.

Deploying transactional databases on bare-metal architecture like our Dedicated Servers provides unthrottled PCIe Gen4 NVMe arrays with sustained high write IOPS.

---

Step 2: Scaling Parallel Purge Threads

By default, older configurations use only a single background purge thread. On modern multi-core servers, this thread becomes CPU-bound, unable to drain the history list.

Scale innodb_purge_threads in /etc/my.cnf.d/server.cnf:

[mysqld]
# ---------------------------------------------------------
# High-Throughput InnoDB Purge & MVCC Optimization
# ---------------------------------------------------------

# Scale parallel purge threads to match write concurrency (Recommended: 4 to 8)
innodb_purge_threads            = 8

# Maximum number of undo pages purged in one batch (Default 300)
innodb_purge_batch_size         = 500

# Aggressive purge scaling when HLL exceeds threshold
# Delays DML writes slightly if HLL exceeds 1,000,000 to allow purge to catch up
innodb_max_purge_lag            = 500000
innodb_max_purge_lag_delay      = 10000

---

Step 3: Enabling Dedicated Undo Tablespaces & Online Truncation

Historically, undo logs were stored inside the shared system tablespace (ibdata1), which could never be shrunk without dumping and reloading the entire database.

In modern MariaDB (10.2+ and 10.6+), administrators can configure dedicated, separate undo tablespaces that automatically shrink back down to initial size (10MB) once unneeded.

Add the following directives to /etc/my.cnf.d/server.cnf:

# ---------------------------------------------------------
# Automated Online Undo Tablespace Truncation
# ---------------------------------------------------------

# Separate undo tablespaces (Minimum 2 required for truncation rotation; 4 is optimal)
innodb_undo_tablespaces         = 4

# Enable automated online truncation
innodb_undo_log_truncate        = ON

# Maximum undo tablespace size before triggering truncation (1GB)
# When undo_001 exceeds 1GB, MariaDB marks it inactive, purges it, and truncates
innodb_max_undo_log_size        = 1073741824

# Frequency of evaluation for truncation (in purge rounds)
innodb_purge_rseg_truncate_frequency = 128

Restart MariaDB to initialize the dedicated undo tablespaces:

systemctl restart mariadb

Verify that automated undo truncation is active:

SHOW GLOBAL VARIABLES LIKE 'innodb_undo%';

Output:

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 4          |
+--------------------------+------------+

---

Step 4: Monitoring Truncation Events Live

To monitor automated undo space reclamation in real time, query the global status counters:

SHOW STATUS LIKE 'Innodb_undo_truncate%';

Sample output:

+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Innodb_undo_truncate_count        | 48    |
+-----------------------------------+-------+

Innodb_undo_truncate_count records the exact number of times bloated multi-gigabyte undo tablespaces were successfully truncated back to 10MB without interrupting live transactions.

---

Performance Benchmark: History List Lag vs. Query Speed

We evaluated a high-concurrency database executing 8,000 updates per second alongside analytical reads:

Metric	Single Purge Thread (Default)	Tuned 8 Threads + Auto-Truncation	Net Improvement
History List Length (HLL)	1,280,000 (Severe Lag)	< 4,200 (Stable & Low)	99.6% Reduction
P99 Read Query Latency	420 ms (Undo pointer traversal)	6.2 ms	67x Faster Reads
Undo Storage Footprint	68.4 GB (Endless expansion)	4.1 GB (Actively Capped)	94% Storage Reclaimed
Buffer Pool Page Churn	High (Polluted by undo pages)	Low (Hot data stays in RAM)	Significant Cache Boost
Storage Write Amplification	3.8x	1.4x	63% Less SSD Wear

By scaling parallel purge threads and automating online undo truncation, your database maintains pristine transactional read performance while preventing runaway storage expansion.

For hosting mission-critical relational databases, multi-tenant SaaS backends, and high-frequency eCommerce architectures in Pakistan, evaluate our high-performance Dedicated Servers in Pakistan.