Demystifying MySQL InnoDB Buffers: Pool, Change, Log & Doublewrite

Buffer Pool

The buffer pool is a memory area where InnoDB caches table and index pages. When a page is needed, it is loaded into the pool, allowing subsequent accesses to be served from memory instead of disk, which greatly speeds up reads and writes.

Each page is 16 KB by default. Because the pool cannot hold the entire dataset, InnoDB uses an eviction policy based on a modified LRU (Least Recently Used) algorithm that splits pages into an old sublist and a new sublist . New pages are inserted at the head of the old sublist; if a page is accessed again within one second it stays in the old sublist, otherwise it moves to the new sublist. This design reduces the impact of pre‑read and large‑scan workloads that could otherwise evict hot pages.

Why a custom LRU?

InnoDB’s pre‑read mechanism fetches sequential pages in anticipation of future reads. If every newly read page were placed at the head of a pure LRU, the pre‑read pages could pollute the hot‑data region. By inserting new pages into the old sublist, InnoDB protects frequently accessed pages from being evicted prematurely.

Change Buffer

The change buffer records modifications to secondary (non‑unique) index pages when those pages are not currently resident in the buffer pool. Instead of loading the page first, InnoDB stores the change in the change buffer and applies it later when the page is fetched.

Example: update table set name = 'yes' where id = 1; If the page containing id = 1 is absent from the pool, the update is cached in the change buffer. When the page is eventually loaded, the cached change is replayed, avoiding an immediate random I/O.

Change buffer works only for secondary index pages; it does not apply to primary key, spatial, or full‑text indexes, nor to unique indexes that require a consistency check.

Log Buffer

The log buffer temporarily holds redo‑log records before they are flushed to the redo‑log file. This reduces the number of system calls (write/fsync) by batching multiple log records into a single write.

Flushing occurs in three typical scenarios:

When the transaction commits, the buffer is written to the OS cache and fsync is called (default innodb_flush_log_at_trx_commit = 1), guaranteeing durability but incurring the highest latency.

When the buffer size exceeds half of its total capacity (default 16 MB), it is flushed to the redo‑log file.

A background thread flushes the buffer every second.

The variable innodb_flush_log_at_trx_commit controls the durability‑performance trade‑off:

0 : No flush on commit; the background thread writes and fsync s every second. Best performance, but up to one second of data can be lost on crash.

1 : Flush on every commit (default). Guarantees no loss but is the slowest.

2 : Write to OS cache on commit, fsync only every second. A middle ground; some data may be lost if the server crashes.

Doublewrite Buffer

InnoDB pages are 16 KB, which span four 4 KB OS pages. Writing a single InnoDB page directly to disk is not atomic; a power loss could corrupt only part of the page. The doublewrite buffer solves this by first copying the page to a dedicated area in memory, then writing that copy sequentially to a special doublewrite file before the page is written to its final location.

This two‑step process ensures that, after a crash, InnoDB can recover a complete page from the doublewrite file if the original write was only partially completed. Because the doublewrite writes are sequential and the data is already in memory, the performance impact is modest.

Overall Summary

Buffer Pool : Caches data and index pages, uses a split LRU (old/new sublists) to keep hot pages in memory while handling pre‑read and bulk‑scan workloads.

Change Buffer : Defers writes to secondary index pages that are not in the pool, reducing random I/O; applicable only to non‑unique secondary indexes.

Log Buffer : Batches redo‑log records, with configurable flush policies ( innodb_flush_log_at_trx_commit) to balance durability and latency.

Doublewrite Buffer : Provides atomicity for page writes by double‑writing pages, enabling safe recovery after power loss with minimal performance penalty.