Understanding InnoDB Buffer Pool Internals for Real-World MySQL Performance

InnoDB’s buffer pool is the core of MySQL performance. Almost every read and write goes through it, so understanding how it works is essential for any engineer running MySQL in production.

This article walks through how the buffer pool is structured, how pages move through it, and how to size and monitor it on a typical Linux server (RHEL/Rocky). The focus is on safe, repeatable practices.

1. What the InnoDB buffer pool actually does

InnoDB stores table and index data in 16 KB pages on disk. The buffer pool is a long-lived cache of these pages in memory.

Very simplified view:

┌─────────────────────────────┐
│        InnoDB Buffer Pool   │
│ ┌────────┐  ┌────────┐      │
│ │ Page 1 │  │ Page 2 │ ...  │  <─ 16 KB each
│ └────────┘  └────────┘      │
└─────────────────────────────┘

Disk tablespace (ibd files):
┌────────┬────────┬────────┬────────┐
│ Page 1 │ Page 2 │ Page 3 │  ...   │
└────────┴────────┴────────┴────────┘

When a query needs a page:

If the page is in the buffer pool, it is served from memory (a buffer pool hit).
If not, InnoDB reads it from disk into the buffer pool, possibly evicting another page.

Writes are also done in memory first; dirty pages are later flushed to disk by background threads.

2. Core structures inside the buffer pool

Internally, the buffer pool is more than just an array of pages. The main concepts you need to know are:

Buffer pool instances: logical partitions of the buffer pool for concurrency.
LRU list: tracks which pages are “hot” vs “cold”.
Free list: pages available for reuse.
Flush list: dirty pages waiting to be written to disk.

2.1 Instances and concurrency

The buffer pool can be split into instances:

┌─────────────────────────────────────────────┐
│           Total buffer pool                │
│ ┌─────────────┬─────────────┬────────────┐ │
│ │ Instance 1  │ Instance 2  │ Instance 3 │ │
│ └─────────────┴─────────────┴────────────┘ │
└─────────────────────────────────────────────┘

Each instance has its own LRU, free and flush lists, reducing contention on hot workloads.

Key parameters:

innodb_buffer_pool_size: total buffer pool size.
innodb_buffer_pool_instances: number of instances.

Instances are automatic above certain sizes; you usually only tune the total size.

2.2 The LRU list and the “midpoint”

InnoDB does not use a simple LRU. It uses a midpoint insertion strategy to avoid large scans evicting your hot pages.

LRU list (conceptual):
┌───────────────┬────────────────────────────┐
│  New / Hot    │      Old / Cold            │
│ (frequently   │   (rarely used, evicted    │
│  accessed)    │    from this end)          │
└───────────────┴────────────────────────────┘
                 ▲
                 │
           Midpoint insertion

Freshly read pages are inserted near the middle of the list. If they are accessed again, they move towards the hot end. Pages not re-used drift towards the cold end and are evicted first.

2.3 Free and flush lists

Free list: pages not currently holding useful data. When InnoDB needs a new page, it first tries the free list, then evicts from the LRU if needed.
Flush list: dirty pages that must be written to disk. This is ordered by modification (LSN), not by recency of access.

These lists matter when you see stalls from page flushing or when the free list runs low.

3. Sizing the buffer pool safely

On dedicated MySQL servers, the buffer pool is typically the largest memory consumer. Oversizing it can cause swapping and severe performance issues.

3.1 Basic sizing guidelines

On a dedicated DB server, a common starting point is 60–70% of RAM for the buffer pool.
On shared hosts or when running other heavy services, start lower (e.g. 30–40%) and adjust based on monitoring.
Always leave headroom for:
- OS page cache.
- MySQL connections, sorts, joins, temporary tables.
- Backup tools (e.g. mysqldump, xtrabackup).

Example: 64 GB RAM, dedicated MySQL on Rocky Linux.

Start with 40–45 GB buffer pool (around 65–70%).
Leave ~20 GB for OS, connections and overhead.

3.2 Configuring in my.cnf

On RHEL/Rocky, edit /etc/my.cnf or a file under /etc/my.cnf.d/:

[mysqld]
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 8

Warning: Changing innodb_buffer_pool_size can be done dynamically on most modern MySQL versions, but large changes can cause temporary stalls. Test first and avoid big jumps during peak traffic.

4. Monitoring buffer pool health

You should continuously monitor buffer pool usage and hit ratios. Some key metrics come from SHOW ENGINE INNODB STATUS and information_schema.

4.1 Quick status snapshot

mysql> SHOW ENGINE INNODB STATUS\G

Look for the BUFFER POOL AND MEMORY section. Useful fields include:

Buffer pool size: number of pages.
Database pages: pages with data (not free).
Free buffers: how many pages are free.
Modified db pages: dirty pages.

4.2 Buffer pool hit ratio

From SHOW GLOBAL STATUS:

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Key counters:

Innodb_buffer_pool_read_requests: logical reads (from buffer pool).
Innodb_buffer_pool_reads: physical reads (from disk).

Approximate hit ratio:

Hit ratio ≈ 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

You ideally want this very close to 1.0 (e.g. > 0.99) for OLTP workloads. But do not chase a perfect ratio at the expense of OS stability; avoid causing swapping just to increase this number.

4.3 Memory usage from information_schema

On newer MySQL versions, you can query buffer pool stats:

SELECT
  POOL_ID,
  POOL_SIZE,
  FREE_BUFFERS,
  DATABASE_PAGES,
  MODIFIED_DATABASE_PAGES
FROM information_schema.INNODB_BUFFER_POOL_STATS;

This helps you see per-instance distribution and how many dirty pages are waiting to be flushed.

5. Understanding and managing buffer pool flushing

Dirty pages must be flushed to disk regularly to:

Keep the redo log from filling up.
Ensure crash recovery is fast.
Prevent sudden bursts of I/O under load.

5.1 Types of flushing

Background flushing: runs continuously to keep dirty pages under control.
Adaptive flushing: increases flushing when the redo log is filling too quickly.
Checkpoint flushing: ensures a consistent point on disk.

Relevant parameters include:

innodb_flush_method (e.g. O_DIRECT to avoid double buffering with the OS cache).
innodb_flush_neighbors (controls flushing of adjacent pages on spinning disks; often 0 on SSDs).
innodb_lru_scan_depth (how aggressively InnoDB scans the LRU to find free pages).

Changes to these settings should be done cautiously and tested under realistic load. Mis-tuning can lead to high latency spikes.

6. Step-by-step tuning workflow

Below is a safe, incremental approach to tuning the buffer pool on a RHEL/Rocky MySQL server.

Step 1: Collect baseline metrics

Record hit ratio, dirty pages, and free buffers:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW ENGINE INNODB STATUS\G
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;

Capture OS-level memory stats:

$ free -m
$ vmstat 5 5
$ iostat -x 5 5

Ensure there is no swapping (si/so in vmstat should be 0 or near 0).

Step 2: Decide on a target size

Estimate working set: roughly, how much of your data is “hot” (frequently accessed)?
Check current buffer pool utilisation: if free buffers are consistently very low and hit ratio is poor, you may benefit from more memory.
Confirm OS has enough free RAM to grow the buffer pool without swapping.

Step 3: Apply a conservative change

Warning: Avoid large jumps in production (e.g. from 8 GB to 48 GB). Increase in small steps (25–50%) and observe behaviour.

Dynamic change example (if supported by your version):

mysql> SET GLOBAL innodb_buffer_pool_size = 48 * 1024 * 1024 * 1024;

Or via config and restart:

[mysqld]
innodb_buffer_pool_size = 48G

On RHEL/Rocky:

$ sudo systemctl restart mysqld

Step 4: Re-measure under load

Repeat the baseline queries.
Compare hit ratio, disk I/O, and query latency.
Ensure swap usage is still zero or negligible.

If you see improved performance and the OS remains healthy, you can consider another incremental increase until you reach your target or hit system limits.

7. Best practices and common pitfalls

Avoid swapping at all costs: A slightly smaller buffer pool is far better than a swapped-out MySQL process.
Use O_DIRECT on dedicated DB servers (if supported and tested):

[mysqld]
innodb_flush_method = O_DIRECT

Keep buffer pool instances reasonable: Too many instances with a small total size can waste memory. For < 8 GB buffer pools, fewer instances (1–2) are usually enough.
Monitor over time, not just once: Collect metrics at regular intervals to see trends and detect regressions.
Consider workload type:
- OLTP (many small queries) benefits most from a large, well-tuned buffer pool.
- Large analytical scans can still cause I/O even with a big buffer pool; you may need query/index tuning as well.
Don’t ignore dirty pages: A very high ratio of dirty pages and long flush times can cause stalls. Check the Modified db pages metric and tune flushing if needed.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

With a solid grasp of buffer pool internals and a disciplined tuning process, you can often unlock significant performance gains without changing a single line of application code. Focus on safe, incremental changes, backed by metrics, and your MySQL instances will reward you with more predictable, stable behaviour.