How InnoDB Works: Pages, Buffer Pool, Redo and Undo Logs

You do not need to know InnoDB internals to use MySQL. But a simple mental model explains why indexes, transactions, memory settings and crashes behave as they do.

Pages: the unit of storage

InnoDB stores data in fixed-size pages, 16 KB by default. A page holds many rows (or index entries). Reading a row means reading its page. Everything is organised around pages.

1

SELECT @@innodb_page_size / 1024 AS page_size_kb, @@innodb_file_per_table AS one_file_per_table;

1
2
3
4
5
6

+--------------+--------------------+
| page_size_kb | one_file_per_table |
+--------------+--------------------+
|      16.0000 |                  1 |
+--------------+--------------------+
1 row in set

The clustered index: the table is the primary key tree

The rows of an InnoDB table are stored inside the primary key B-tree: the leaf pages of the tree contain the full rows, sorted by primary key. This is the clustered index.

1
2
3
4
5

          [ root page ]
         /      |      \
   [ branch ] [ branch ] [ branch ]
   /   |    \
[leaf: rows 1..90][leaf: rows 91..180] ...   <- leaf pages hold whole rows, in primary key order

• A lookup by primary key walks the tree and reads the row directly.
• A secondary index has its own B-tree. Its leaf entries contain the indexed columns and the primary key of the row. To read other columns, InnoDB takes the primary key and looks it up in the clustered index (a “bookmark lookup”) (What Is an Index?).

That is why a short primary key keeps all your indexes small, and why an AUTO_INCREMENT key makes inserts append to the end of the tree, while a random UUID inserts everywhere and splits pages (UUIDs as Keys).

The buffer pool: memory cache

The buffer pool is a big area of memory that caches pages (data and index pages) so that most reads do not touch the disk. When a page is needed, InnoDB looks in the buffer pool first. If it is missing, InnoDB reads it from disk into the pool, evicting an old page by a least-recently-used rule.

A changed page is called a dirty page. It is written back to disk later, in the background, in batches. That is why a write returns quickly: the change is made in memory, not directly on the data file.

1

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;

1
2
3
4
5

+----------------+
| buffer_pool_mb |
+----------------+
|    64.00000000 |
+----------------+

(The value on the test server. A real server uses a much larger pool, see Server Settings.)

The redo log: durability without writing data pages at once

If a change lives only in a dirty page in memory, a crash would lose it. So InnoDB first writes a short description of every change to the redo log, a sequential file on disk. At COMMIT, the redo log is flushed (by default). The data pages themselves can be written later.

1

change a row -> write redo record -> COMMIT (flush redo log to disk)  -> (later) write the dirty page to the data file

After a crash, InnoDB replays the redo log from the last checkpoint, which re-applies the committed changes that never reached the data files. Writing the redo log is sequential (fast), while writing data pages is random (slow). That is how InnoDB is both durable and fast.

1

SELECT @@innodb_redo_log_capacity / 1024 / 1024 AS redo_log_capacity_mb, @@innodb_flush_log_at_trx_commit AS flush_setting;

1
2
3
4
5
6

+----------------------+---------------+
| redo_log_capacity_mb | flush_setting |
+----------------------+---------------+
|         100.00000000 |             1 |
+----------------------+---------------+
1 row in set

The undo log: rollback and old versions

Before a row is changed, its old version is saved in the undo log. The undo log does two jobs:

1. Rollback. If a transaction rolls back, or the server crashes before it committed, InnoDB uses the undo records to restore the old values.
2. MVCC. A reader that needs an older version of a row (its snapshot) reads it from the undo log (MVCC).

Old undo records are removed by a background purge process when no transaction needs them any more.

The doublewrite buffer: protection from half-written pages

A page is 16 KB, while the operating system and disks write in smaller units. A power failure in the middle of writing a page can leave it half old, half new (“torn page”), which the redo log cannot repair. InnoDB therefore first writes dirty pages to a doublewrite area, then to their real place. After a crash, a torn page is restored from the doublewrite copy.

1

SELECT @@innodb_doublewrite AS doublewrite;

1
2
3
4
5

+-------------+
| doublewrite |
+-------------+
| ON          |
+-------------+

Keep it on, unless your storage guarantees atomic 16 KB writes.

The change buffer

When a secondary index page is not in memory and you insert or update a row, InnoDB does not read the page from disk just to change it. It records the change in the change buffer, and merges it when the page is read later. This saves a lot of random reads for write-heavy tables with many secondary indexes.

The adaptive hash index

InnoDB watches index lookups, and for pages that are accessed very often it builds an in-memory hash index on top of the B-tree, so that repeated lookups become one hash probe. It is automatic. (On some workloads, with a lot of contention, people switch it off with innodb_adaptive_hash_index = OFF.)

Putting it together: what happens on an UPDATE

1. InnoDB finds the row’s page (in the buffer pool, or reads it from disk).
2. It locks the row.
3. It writes the old version to the undo log.
4. It changes the row in the page in memory (the page becomes dirty).
5. It writes a redo log record.
6. On COMMIT, the redo log is flushed to disk and the locks are released.
7. Later, a background thread writes the dirty page to the data file (via the doublewrite buffer), and the old undo record is purged when nobody needs it.

Why this matters to you