Change Buffer and Undo Logs: InnoDB's Invisible Workers Explained

1. Why InnoDB needs a change buffer in the first place

In InnoDB, a primary key index is the table itself, which is why inserts along a monotonically increasing primary key mostly land sequentially on disk. Secondary indexes, on the other hand, are separate structures whose sort order usually has nothing to do with the order rows get inserted in. An insert into a table with several secondary indexes can therefore generate multiple randomly scattered writes, one per affected index.

If the affected secondary index page is not already in the buffer pool, an immediate merge would have to load that page from disk first, just to record a single change. The change buffer avoids exactly that: it buffers the change in memory and pushes the actual merge with the index page to a later, cheaper point in time.

2. How the change buffer merges changes

Merging a buffered change happens once the affected page gets loaded into the buffer pool for another reason anyway, for example a read request, or as part of a background process that periodically works through buffered changes, and at the latest during an orderly server shutdown. As a result, several buffered changes on the same page are often bundled into a single merge operation instead of each change requiring its own separate page access.

This behavior is controlled via innodb_change_buffering, with values all, inserts, deletes, changes, purges and none, and via innodb_change_buffer_max_size, which caps the share of the buffer pool reserved for the change buffer as a percentage, 25 percent by default.

SHOW VARIABLES LIKE 'innodb_change_buffer%';

-- Watch merge activity in the status report
SHOW ENGINE INNODB STATUS\G
-- the "INSERT BUFFER AND ADAPTIVE HASH INDEX" section holds
-- the current size and merge counters of the change buffer

3. Where the change buffer hits its limits

Unique secondary indexes barely benefit from the change buffer, because an insert has to check uniqueness immediately against the actual index page, which therefore has to be loaded right away regardless. The buffering effect mostly plays out on non-unique secondary indexes, which are common in Magento tables for filter and sort columns.

Even with a working set that already mostly fits in the buffer pool, the change buffer adds little, since the affected pages are usually already loaded and an immediate merge would be just as cheap as a delayed one. The real benefit shows up under random-I/O-heavy workloads with a working set larger than the buffer pool, for example very large product catalogs with many indexed attribute columns.

4. Undo logs as the foundation for MVCC snapshots

Every change to a row in InnoDB creates an undo log entry that records the row's previous version. Other transactions that already hold a consistent read snapshot at that point can keep reading, through the chain of these undo entries, the older version of the row that is valid for them, without the writing transaction having to block for it. That is exactly the technical foundation for multi-version concurrency control in InnoDB.

Without undo logs, every reading transaction would either have to wait on writing transactions or work with potentially inconsistent intermediate states. The price for this concurrency is that old row versions must be kept around for as long as any active transaction could theoretically still need them.

5. Undo logs as the foundation for rollback

Besides MVCC, undo logs perform a second, equally important job: if a transaction aborts or gets explicitly rolled back, InnoDB reads the corresponding undo entries in reverse order and restores the state that existed before the transaction. A rollback is, technically speaking, nothing more than playing the undo chain backward in a controlled way.

That also explains why a very large transaction, for example a massive bulk update during a data import, can take noticeably long to roll back once aborted: every single change has to be individually undone through its undo entry, and that work cannot be skipped or parallelized.

6. Undo tablespaces, rollback segments and purge threads

Physically, undo logs live inside rollback segments, which are organized within dedicated undo tablespaces whose count is configurable via innodb_undo_tablespaces. Once an undo entry is no longer needed by any active transaction, because no read snapshot exists anymore that could still see the older row version, the purge threads asynchronously remove that entry in the background and free the space it occupied.

The number of these background threads is configurable via innodb_purge_threads. Under a workload with a high write frequency and a correspondingly high undo generation rate, too few purge threads can cause the cleanup work to permanently fall behind the creation of new undo entries.

SHOW VARIABLES LIKE 'innodb_undo_tablespaces';
SHOW VARIABLES LIKE 'innodb_purge_threads';

-- Current history list length as a direct indicator
-- of unpurged undo entries
SELECT NAME, COUNT FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';

7. How long-running transactions make undo logs swell

As long as any transaction holds an old read snapshot open, the corresponding undo entries cannot be purged, even if the underlying rows have long since been overwritten many times over. A single, forgotten open transaction, for example a stuck admin session or a backup process with a long snapshot duration, can therefore cause undo entries to pile up over hours or days while regular operation continues completely normally.

In a Magento context this matters most for long-running batch processes, for example a large reindex or a data import that uses a single, very long open transaction. While that process runs, the history list keeps growing continuously, and only once it finishes can the purge thread catch up on the backlog.

8. Monitoring undo growth before it becomes a problem

The key metric for unpurged undo entries is the history list length, available through the trx_rseg_history_len metric or in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS. A value that keeps growing steadily over a longer period is a reliable early warning sign of a forgotten, still-open transaction, long before disk space actually runs tight.

It is also worth regularly checking information_schema.INNODB_TRX to specifically identify transactions with an unusually long trx_started duration and, if in doubt, abort them before they block an excessive number of undo entries and thereby indirectly drive up the disk space consumption of the undo tablespaces.

SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC
LIMIT 10;

9. Practical relevance for Magento databases

For Magento stores with heavy write load on secondary indexes, for example during large price or stock imports, a sensibly configured change buffer noticeably reduces the number of expensive random I/O accesses. At the same time, it is worth checking open transactions and the history list length before any larger, long-running batch job, so a single forgotten process does not needlessly drive up undo log growth for the entire duration of the import.

Both mechanisms usually show their effect only in combination: a well-functioning change buffer relieves the write paths, while cleanly managed, short transactions ensure the undo entries they generate get purged promptly instead of piling up unnoticed over days.

10. Summary

11. FAQ: Change Buffer and Undo Logs: The Essentials at a Glance