MariaDB InnoDB Multi-Thread Purge & Undo Log Auto-Truncation in Pakistan

High-volume transactional database deployments in Pakistan—such as core banking microservices, retail ERP transaction ledgers, and fast-growing WooCommerce platforms—process thousands of write operations (INSERT, UPDATE, DELETE) every minute. However, system administrators frequently discover a terrifying storage issue: the MariaDB data directory (/var/lib/mysql/) balloons uncontrollably by tens or hundreds of gigabytes, even when the actual application data tables only contain a few gigabytes of rows!

When investigating disk usage, the culprit is almost invariably InnoDB undo tablespaces (undo_001, undo_002, or the legacy shared system tablespace ibdata1). Under InnoDB’s Multi-Version Concurrency Control (MVCC) architecture, modifying or deleting a row does not immediately delete the older version from disk. Instead, InnoDB writes an undo log record containing the prior version so that concurrent reading transactions can maintain a consistent snapshot.

If background purge operations cannot keep pace with write workloads—or if long-running read queries block the purge progress—the History List Length (HLL) explodes into hundreds of thousands of unpurged undo pages. In legacy or default configurations, undo tablespaces expand infinitely and never shrink, permanently consuming disk space and causing catastrophic storage exhaustion.

By deploying on high-performance bare-metal Dedicated Servers and properly configuring multi-threaded purge workers (innodb_purge_threads) and online undo tablespace auto-truncation (innodb_undo_log_truncate), database administrators can keep the History List Length under control and automatically reclaim gigabytes of storage space.

---

The Mechanics of MVCC, Undo Logs, and History List Length (HLL)

To effectively resolve undo log runaway, understand how InnoDB manages old row versions:

1. Transaction Modification: When a query updates a row, the old row version is appended to an undo log segment.
2. History List: The transaction commits, but its undo records remain linked in the History List until all older active transactions that might need them have completed.
3. The Purge Thread: A background worker thread sweeps through the History List, physically removes dead row versions that are no longer visible to any active transaction snapshot, and frees the undo log pages.

Incoming UPDATE Queries (Thousands per second)
                       │
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ InnoDB Write Path: Append old row versions to Undo Log     │
└─────────────────────────────────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ History List Length (HLL) Backlog Accumulation              │
└─────────────────────────────────────────────────────────────┘
                       │
       ┌───────────────┴───────────────┐
       ▼                               ▼
[ Default Single Purge Thread ]   [ Optimized Multi-Thread Purge ]
- Only 1 worker thread             - 8 Dedicated Purge Workers
- Overwhelmed by write volume      - Keeps HLL < 500 at all times
- HLL spikes to > 800,000!         - Auto-truncates Undo Logs to 1GB
- Disk fills: Storage Outage!      - Continuous disk space reclamation!

---

Step 1: Diagnosing History List Length & Undo Log Disk Consumption

Check the active History List Length and undo log status in MariaDB:

SHOW ENGINE INNODB STATUS\G

Locate the TRANSACTIONS section and inspect the first line:

------------
TRANSACTIONS
------------
Trx id counter 294810294
Purge done for trx's n:o < 294001202 undo n:o < 0 state: running
History list length 842104

An HLL exceeding 10,000 indicates severe purge lag; an HLL exceeding 100,000 means the purge system is completely paralyzed.

To inspect the physical disk footprint of undo tablespaces on the host filesystem:

ls -lh /var/lib/mysql/undo*
# Output showing bloated undo files:
# -rw-r----- 1 mysql mysql 48G Sep 30 14:10 /var/lib/mysql/undo_001
# -rw-r----- 1 mysql mysql 52G Sep 30 14:10 /var/lib/mysql/undo_002

---

Step 2: Enabling Multi-Threaded Purge & Online Undo Truncation in /etc/my.cnf.d/server.cnf

To allow MariaDB to aggressively purge dead row versions using parallel CPU cores and automatically truncate undo files back to their baseline size, apply the following production parameters in /etc/my.cnf.d/server.cnf:

[mariadb]
# /etc/my.cnf.d/server.cnf
# NextGen Pakistan - High-Throughput InnoDB Undo Purge & Storage Reclamation Profile

# 1. Multi-Threaded Background Purge Workers
# By default, MariaDB uses 4 threads. On servers with 8+ CPU cores,
# allocate 8 dedicated threads to prevent purge bottlenecks during write bursts.
innodb_purge_threads = 8

# 2. Online Undo Tablespace Auto-Truncation
# Enables automated background deallocation and truncation of oversized undo tablespaces
innodb_undo_log_truncate = ON

# 3. Maximum Undo Tablespace Size Ceiling (Bytes)
# When an undo tablespace exceeds this limit (e.g. 1GB), MariaDB marks it inactive,
# creates a new active space, and asynchronously truncates the old one on disk!
innodb_max_undo_log_size = 1073741824   # 1 GB

# 4. Number of Dedicated Separate Undo Tablespaces
# Separate undo logs from ibdata1 so they can be truncated independently (minimum 2 or 3)
innodb_undo_tablespaces = 3

# 5. Purge Batch Size and Frequency Tuning
# Maximum number of undo log records parsed and purged in a single batch
innodb_purge_batch_size = 512

# 6. Purge Delay (Microseconds)
# Keep at 0 to ensure purge threads run at maximum unthrottled speed
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 0

Apply non-static variables dynamically in MariaDB:

SET GLOBAL innodb_undo_log_truncate = ON;
SET GLOBAL innodb_max_undo_log_size = 1073741824;
SET GLOBAL innodb_purge_batch_size = 512;

---

Step 3: Identifying and Killing Rogue Long-Running Transactions

Even with 8 purge threads enabled, InnoDB cannot purge undo records if an abandoned application connection has left a transaction open in REPEATABLE READ isolation. The purge engine must preserve all historical snapshots back to the oldest running transaction!

Identify the blocking transaction:

SELECT 
    trx_id, 
    trx_state, 
    trx_started, 
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
    trx_mysql_thread_id, 
    trx_query 
FROM information_schema.innodb_trx 
ORDER BY trx_started ASC 
LIMIT 5;

If a rogue query or abandoned worker has been running for thousands of seconds:

+-----------+-----------+---------------------+------------------+---------------------+-----------+
| trx_id    | trx_state | trx_started         | duration_seconds | trx_mysql_thread_id | trx_query |
+-----------+-----------+---------------------+------------------+---------------------+-----------+
| 28910401  | RUNNING   | 2026-09-30 08:14:02 | 21600            | 4912                | NULL      |
+-----------+-----------+---------------------+------------------+---------------------+-----------+

Notice trx_query: NULL and duration_seconds: 21600 (6 hours). An application connection opened a transaction, ran a query, and failed to issue COMMIT or ROLLBACK!

Kill the rogue thread to instantly unblock the purge engine:

KILL 4912;

Within seconds, the 8 purge threads will churn through the backlog, and MariaDB will truncate the multi-gigabyte undo tablespaces back to 10MB!

---

Step 4: Monitoring Real-Time History List Length Depletion

Observe the History List Length drop rapidly after unblocking:

SHOW GLOBAL STATUS LIKE 'Innodb_undo_tablespaces_total';
SHOW GLOBAL STATUS LIKE 'Innodb_undo_tablespaces_active';
SHOW GLOBAL STATUS LIKE 'Innodb_undo_tablespaces_truncated';

Sample output:

+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Innodb_undo_tablespaces_total     | 3     |
| Innodb_undo_tablespaces_active    | 2     |
| Innodb_undo_tablespaces_truncated | 14    |
+-----------------------------------+-------+

Innodb_undo_tablespaces_truncated: 14 confirms that MariaDB has successfully truncated and reclaimed disk space 14 times automatically in the background without requiring a database restart or offline maintenance!

---

High-IOPS Dedicated Database Hosting for Pakistani Enterprises

Heavy transactional database workloads with continuous undo logging and background page purging require high write endurance and unthrottled random IOPS. Virtualized shared clouds frequently throttle IOPS during purge truncation spikes, leading to lock wait timeouts and query freezing.

Hosting your database infrastructure on bare-metal Dedicated Servers in Pakistan equips your environment with enterprise-grade NVMe drives in RAID 10, ECC DDR5 RAM, and dedicated CPU cores, ensuring that transaction commit latencies remain under 100 microseconds and storage maintenance executes seamlessly in the background.