MySQL InnoDB Buffer Pool Instances: The Setting That Reduces Mutex Contention

The Mutex Problem in a Single Buffer Pool

InnoDB's buffer pool holds data pages in memory to avoid disk reads. Every time a thread needs a page — whether for a read, a write, or a page eviction — it acquires a mutex protecting the buffer pool's internal data structures: the LRU list, the free list, and the flush list. With a single buffer pool, this is a single mutex shared across all threads.

Under low concurrency, this is not a problem. A single thread acquires the mutex, does its work, releases it, and the next thread proceeds. Under high concurrency — 200+ simultaneous connections all issuing queries that touch different pages — threads queue up waiting for the mutex. The CPU shows available capacity, query latency increases, and throughput plateaus. The bottleneck is lock contention, not I/O or CPU.

The symptom shows up in InnoDB status output:

-- Check buffer pool mutex wait state
SHOW ENGINE INNODB STATUS\G

-- Look for these lines in the SEMAPHORES section:
-- OS WAIT ARRAY INFO: reservation count 142831, signal count 142789
-- --Thread 140234567890 has waited at buf0buf.cc line 2387 for 0.0001 seconds the semaphore:
-- Mutex at 0x... created file buf0buf.cc line 1234, lock var 0

-- Also check:
SELECT * FROM performance_schema.mutex_instances
WHERE NAME LIKE '%buf_pool%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

High wait times on buffer pool mutexes with available CPU capacity is the diagnostic signal that innodb_buffer_pool_instances will help.

How Buffer Pool Instances Work

Setting innodb_buffer_pool_instances = N divides the total buffer pool into N independent partitions, each with its own mutex, LRU list, free list, and flush list. A page is assigned to a specific instance based on a hash of its tablespace ID and page number. Threads accessing different pages almost always hash to different instances, so they acquire different mutexes and proceed in parallel.

The practical effect: N instances can service up to N concurrent buffer pool operations simultaneously rather than serializing all of them. This is a significant throughput increase on multi-core servers with high connection counts.

Sizing Rules

MySQL's own documentation gives one rule: buffer pool instances are only used when innodb_buffer_pool_size is at least 1 GB. Below that threshold, MySQL ignores the instances setting and uses one pool regardless. Above 1 GB, the general guidance is one instance per GB of buffer pool, up to a maximum of 64 instances.

# my.cnf — example for a server with 64GB RAM
# Buffer pool = 75% of RAM = ~48GB
innodb_buffer_pool_size = 48G

# One instance per GB, capped at 64
# 48 instances is reasonable; 8-16 is a good practical starting point
innodb_buffer_pool_instances = 16

The "one per GB" rule is conservative. In practice, more instances mean smaller per-instance pools, which affects the LRU eviction algorithm's efficiency — very small individual pools (under 1 GB each) can cause more frequent page evictions for workloads with large working sets. The practical sweet spot for most production OLTP servers is 8–16 instances regardless of total pool size, because beyond 16 the marginal mutex contention reduction becomes negligible compared to the LRU granularity cost.

# Practical starting configuration for an OLTP server
# 128GB RAM instance (r6i.4xlarge or similar)
innodb_buffer_pool_size = 96G          # 75% of RAM
innodb_buffer_pool_instances = 16      # 6GB per instance, good balance
innodb_buffer_pool_chunk_size = 128M   # chunk size must divide evenly into each instance

The innodb_buffer_pool_chunk_size constraint matters: the total buffer pool size must be an integer multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If it's not, MySQL silently adjusts the actual buffer pool size upward to satisfy this constraint, and the effective size may differ from what you set.

-- Verify actual buffer pool size after startup
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size';

-- The actual size = chunk_size * instances * (some multiplier)
-- If innodb_buffer_pool_size / (chunk_size * instances) is not an integer,
-- MySQL rounds up to the next multiple

Per-Instance Statistics

With multiple instances, per-instance statistics are available in INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS. Each row represents one instance:

SELECT
  POOL_ID,
  POOL_SIZE,
  FREE_BUFFERS,
  DATABASE_PAGES,
  OLD_DATABASE_PAGES,
  MODIFIED_DATABASE_PAGES,
  PENDING_DECOMPRESS,
  PENDING_READS,
  PENDING_FLUSH_LRU,
  PENDING_FLUSH_LIST,
  PAGES_MADE_YOUNG,
  PAGES_NOT_MADE_YOUNG,
  PAGES_MADE_YOUNG_RATE,
  PAGES_MADE_NOT_YOUNG_RATE,
  NUMBER_PAGES_READ,
  NUMBER_PAGES_CREATED,
  NUMBER_PAGES_WRITTEN,
  PAGE_READ_RATE,
  PAGE_CREATE_RATE,
  PAGE_WRITE_RATE,
  HIT_RATE,
  YOUNG_MAKE_PER_THOUSAND_GETS,
  NOT_YOUNG_MAKE_PER_THOUSAND_GETS
FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS
ORDER BY POOL_ID;

Look at HIT_RATE per instance — it should be consistent across instances for uniform workloads. A single instance with a significantly lower hit rate indicates that a hot subset of pages is concentrated in that instance, which can happen with poorly distributed tablespace IDs.

Buffer Pool Warmup After Restart

A cold buffer pool after a restart causes a burst of I/O as pages are read from disk to fill memory. With multiple instances, each instance warms independently. InnoDB's buffer pool dump and restore feature (`innodb_buffer_pool_dump_at_shutdown` and `innodb_buffer_pool_load_at_startup`) works correctly with multiple instances — it dumps and restores the page list for each instance separately:

# Enable automatic buffer pool state preservation across restarts
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_pct         = 25   # dump top 25% of pages per instance

-- Monitor warmup progress after restart
SELECT
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_load_status',
  'Innodb_buffer_pool_dump_status'
);

-- Also check:
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_data';
-- pages_data / pages_total = fill ratio — should approach 1.0 as warmup completes

Aurora MySQL and RDS: Instance Setting Behavior

On RDS MySQL and Aurora MySQL, innodb_buffer_pool_instances is a static parameter — it requires a DB instance restart to take effect. In Aurora, the buffer pool size is automatically tuned by Aurora's memory management layer (innodb_buffer_pool_size is not directly configurable), but innodb_buffer_pool_instances can still be set via the parameter group.

-- Check current setting on RDS/Aurora
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

-- On Aurora, also check:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Aurora manages this dynamically; the value shown reflects current allocation

For Aurora MySQL r6g or r7g instances (Graviton), the default parameter group typically sets innodb_buffer_pool_instances to 8. For heavy OLTP workloads on large instance types (r6g.4xlarge and above), increasing to 16 is worth benchmarking.

When More Instances Hurt

More instances are not always better. Two scenarios where additional instances degrade performance:

Small total buffer pool size. If the total pool is 4 GB and you set 16 instances, each instance is 256 MB. The LRU algorithm makes eviction decisions within each instance independently. A 256 MB instance evicts pages more aggressively than a 4 GB instance, which can cause a working set that would fit comfortably in a single 4 GB pool to suffer repeated evictions when spread across 16 small pools. For total pool sizes under 8 GB, use 4 or fewer instances.

Analytical workloads with large sequential scans. Sequential full table scans tend to hash pages across all instances somewhat randomly. The LRU young/old subdivision that prevents scans from evicting hot OLTP pages works better with larger individual instances. If your workload includes significant full-scan reporting queries, very high instance counts can reduce scan performance compared to fewer, larger instances.

The diagnostic: if increasing buffer pool instances does not reduce mutex wait times in SHOW ENGINE INNODB STATUS, or if hit rates decrease after the change, roll back to the previous setting.