Inside InnoDB: How Buffer Pool, Change Buffer, and Adaptive Hash Index Boost MySQL Performance

InnoDB Architecture Origin

On December 24, 2003, a DBA shouted that MyISAM table locks were crippling a system, prompting the development of a new storage engine to break the lock bottleneck.

"MyISAM's table lock is a time bomb! We need a revolutionary storage engine," said the CTO.

The story continues with a fictional engineer envisioning InnoDB's memory and disk structures, leading to a proposal that showcased dramatic performance gains.

Performance Comparison

Scenario

MyISAM

InnoDB Prototype

1,000,000 concurrent updates

Crash

TPS 18,492

Range query

12.8 s

0.3 s

InnoDB Memory Structure

The core memory components are Buffer Pool , Change Buffer , Adaptive Hash Index , and Log Buffer .

Buffer Pool

The Buffer Pool is a pre‑allocated contiguous memory area that caches table and index pages, allowing frequently accessed data to be read directly from memory.

Its size is set via innodb_buffer_pool_size (recommended 60‑80 % of physical RAM).

Buffer Pool LRU Algorithm

InnoDB uses a variant of the LRU algorithm that splits the list into a New Sublist (young pages) at the head and an Old Sublist (less‑used pages) at the tail.

About 3/8 of the pool is reserved for the Old Sublist.

New pages are inserted at the midpoint; accesses to Old Sublist pages promote them to the New Sublist.

Pages that remain unused gradually age toward the tail and are eventually evicted.

Optimization tip: set the pool as large as possible while leaving enough RAM for other processes to avoid excessive paging.

Change Buffer

"10⁵ non‑primary‑key updates per second—disk IOPS exploded!" the operations director exclaimed.

Change Buffer caches write operations for secondary‑index pages that are not currently in the Buffer Pool, merging them later when the pages are loaded.

<code>Before: IOPS 15,000 → After: 2,300 (↓85%)</code>

Key points:

If the target page is absent, the change is written to the Change Buffer and persisted via Redo Log.

When the page is later read, the buffered changes are merged, reducing random I/O.

Question: Why add a Change Buffer when a Buffer Pool already exists?

Because secondary indexes are often non‑unique and updated in random order; buffering these changes avoids costly disk reads.

Drawbacks include long merge times under heavy write loads and increased memory usage.

Adaptive Hash Index (AHI)

"Can your solution handle hotspot data?" Oracle challenged; the engineer enabled AHI with a single command.

<code>SET GLOBAL innodb_adaptive_hash_index=ON;</code>

AHI automatically creates hash entries for frequently accessed index keys, bypassing B‑tree traversal for equality queries.

Creation triggers:

Frequency threshold: the same index page accessed > 100 times consecutively.

Query pattern match: accesses exceed page_records/16 for a given page.

Lifecycle Management

Automatic creation when thresholds are met.

Automatic eviction via LRU when entries become cold.

Entries are removed when the table is dropped or rebuilt.

Pros: reduces B‑tree levels, lowers CPU/I/O, fully automatic.

Cons: only works for equality queries, possible hash collisions, extra memory consumption, may become a bottleneck under high concurrency.

Log Buffer

"Millions of transactions per second—how to guarantee zero loss?" the DBA asked.

The Log Buffer temporarily stores Redo Log records in memory before they are flushed to disk.

Its size is controlled by innodb_log_buffer_size (default 64 MB).

Flush Strategies

Parameter Value

Behavior

Data Safety

Performance

Use Case

0

Log flushed once per second; no fsync on commit

Low

High

Non‑critical batch processing

1

fsync on every commit

High

Low

Financial transactions

2

Writes go to OS cache; OS flushes once per second

Medium

Medium‑High

Typical business workloads

During a transaction commit, the Log Buffer may be flushed based on innodb_flush_log_at_trx_commit , buffer fullness, or a timed task ( innodb_flush_log_at_timeout ).

Group Commit

Multiple transactions' log records are batched together, reducing the number of fsync calls and improving throughput.

Transactions append their logs to the Log Buffer.

A background thread writes the combined logs to disk.

Fewer fsync operations boost concurrency.

Proper tuning of innodb_flush_log_at_trx_commit , Log Buffer size, and enabling Group Commit can dramatically improve performance while maintaining durability.

Practical Recommendations

Enable AHI for OLTP workloads dominated by equality queries (e.g., user lookups, order queries).

Disable AHI for write‑intensive workloads, low‑memory environments, or when SSDs and a large Buffer Pool already provide sufficient performance.

Set Buffer Pool to the maximum feasible size (60‑80 % of RAM) to approach in‑memory database speeds.

Adjust innodb_log_buffer_size for large transactions to reduce disk I/O.

Choose the appropriate innodb_flush_log_at_trx_commit value based on your tolerance for potential data loss versus latency.