Analyzing Buffer Pool Instance Distribution for a Table in MySQL

As the memory usage of MySQL grows, it is advisable to enable multiple InnoDB buffer pool instances. The article investigates how many buffer pool instances a single table occupies and whether a table can reside in only one instance.

The experiment creates a test database, builds a populated table (referencing a previous experiment for the CREATE statement), repeatedly inserts rows to increase data volume, and then queries the buffer pool distribution. The query output (196 rows) is manually simplified for analysis.

Observations from the simplified results show a clear pattern:

The table’s data pages alternate between two buffer pool instances (POOL_ID 0 and 1).

Pages 3‑35 appear in POOL 1, while pages 36‑63 are empty.

Subsequently, every block of 64 pages switches to the other POOL, creating an alternating sequence.

The article explains why multiple buffer pools are needed: a single pool would cause heavy lock contention when accessing the buffer pool, whereas multiple pools distribute the lock load.

It also clarifies why pages are not assigned one‑by‑one to different pools: scanning contiguous pages is common, and assigning each page to a different pool would increase lock contention rather than alleviate it.

A practical tip is provided: querying information_schema.INNODB_BUFFER_PAGE can be expensive, so it should be avoided in production environments without proper testing.

Finally, the article shows how to estimate the amount of a specific table’s data residing in the buffer pool, noting that the number of rows does not map exactly to the number of pages because InnoDB pages contain more than just row data.