Deep Dive into MySQL InnoDB Architecture: BufferPool, LRU, and Storage Engine Explained

1. Introduction

To follow this article you should install MySQL and be familiar with its basic syntax and usage.

2. MySQL Architecture Design

2.1 How Programs Interact with MySQL

MySQL follows a classic client/server model. Applications load a driver and client, configure the server address and credentials, and then connect to the MySQL server.

2.2 Interaction Diagram

2.3 Server‑Side Process

The server receives the client request and processes it via inter‑process communication. MySQL supports three connection methods:

TCP/IP (port 3306)

Named pipes and shared memory (Windows)

Unix domain socket files (Linux)

After establishing a connection, MySQL parses and optimizes the query in three steps:

Query cache

Syntax parsing

Query optimization

The optimized query is then handed to the storage engine for persistence.

2.4 Server Process Diagram

3. InnoDB Architecture Design

3.1 Design Goals

Data persistence

High concurrency and speed

Minimal data loss and fast recovery after crashes

Ability to roll back problematic operations quickly

Implementation ideas:

Write data to disk files for persistence.

Use a BufferPool (memory) to cache pages before writing to disk.

Record operations in a redo log (similar to Redis AOF).

Store pre‑update values in an undo log for rollback.

3.2 Update Statement Illustration

When updating, MySQL reads the row from disk, writes the old version to the undo log, writes the new version to the redo log, and then persists the changes.

3.3 Diagram

4. MySQL Physical Data Model

4.1 Storage Format of VARCHAR

Each row’s variable‑length fields are stored with a length prefix stored in reverse order.

4.2 Null Bitmap and Row Header

4.3 Row Overflow

If a row does not fit into a single data page, the excess is stored in overflow pages linked together.

5. BufferPool

5.1 Free List

5.1.1 Concept

When MySQL starts, it allocates a BufferPool and initializes descriptor data and pages, which are initially empty.

During DML operations, pages are loaded into the BufferPool. The free list is a doubly linked list that tracks empty descriptor slots.

5.1.2 Page Hash Table

The hash table maps a key (tablespace ID + page number) to a cached page address, indicating that the page is already in memory.

(tablespace ID + page number, cached page address)

5.2 Flush List

5.2.1 Concept

If a page is modified but not yet cached, MySQL uses the free list to allocate a descriptor, loads the page, and then places the descriptor into the flush list, which tracks pages that must be written back to disk.

5.2.2 Diagram

5.3 LRU List

5.3.1 Concept

BufferPool pages are evicted using an LRU algorithm based on an LRU linked list; recently accessed pages move to the front, and the least recently used pages reside at the tail for eviction.

5.3.2 Diagram

5.3.3 LRU Issues

1) Impact of Read‑Ahead

Read‑ahead loads adjacent pages into the cache, which may push rarely used pages forward in the LRU list, preventing timely eviction.

2) Common Triggers for Read‑Ahead

innodb_read_ahead_threshold (default 56): when sequentially accessing more than this many pages in a region, all pages in the region are prefetched.

innodb_random_read_ahead (default off): when 13 consecutive pages in a region are frequently accessed, the whole region is prefetched.

3) Full Table Scan Impact

A full table scan loads all pages into the BufferPool, causing even frequently accessed pages to appear at the LRU tail.

4) Diagram of Issues

5.3.4 LRU Optimizations

MySQL introduces two optimizations:

1) Hot/Cold Separation

Cold data (prefetched, full‑scan, rarely used) is placed in a cold segment, while hot data stays in a hot segment; eviction only targets the cold segment.

Key parameters (generally left at defaults):

innodb_old_blocks_time – cold‑data exemption time (default 1000 ms).

innodb_old_blocks_pct – size of the cold segment (default 37%).

2) Background Thread

A background thread periodically flushes the tail of the cold segment to disk.

5.4 Dynamic Relationship of Free, Flush, and LRU Lists