MySQL innodb_buffer_pool_size: How to Size It, Resize It and Know When It Is Too Small

innodb_buffer_pool_size decides how much of your data MySQL can keep in memory, and on most servers it is the setting that matters most for read performance. The default is 134217728 bytes (128MB), which is fine for a laptop and far too small for a production database. This guide covers how to pick a size, how to change it on a running server, and which counters tell you whether the pool is actually big enough. Everything refers to MySQL 8.4 unless stated otherwise.

What the buffer pool holds

The buffer pool is the memory area where InnoDB caches table and index data. Reads that find their page there never touch the disk; reads that do not have to load the page first. Writes also land in the buffer pool as "dirty" pages and are flushed to disk in the background.

InnoDB manages the pool as a list with a midpoint. By default, 3/8 of the pool is the "old" sublist, a newly read page is inserted at the midpoint, and the least recently used page is evicted when room is needed. The algorithm keeps frequently used pages in the "new" sublist. It is not automatically immune to big scans, though: the documentation notes that by default a table scan, for example from mysqldump or a SELECT without a WHERE clause, can bring in a large amount of data and evict an equivalent amount of older data even if the new pages are never used again. The manual has a separate section on making the buffer pool scan resistant if backups or reports regularly cool down your cache.

How big should it be?

The MySQL documentation gives two slightly different numbers, and both are worth knowing:

• The buffer pool chapter says that on dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.
• The memory use chapter says a typical recommended value is 50 to 75 percent of system memory.

The gap between the two is everything else MySQL and the operating system need. InnoDB allocates the whole buffer pool at startup, but each connection allocates its own buffers on top of that: thread stack, connection and result buffers, read and sort buffers when queries need them. A server with a few thousand connections needs far more headroom than one with fifty. The same chapter warns about both directions: a pool that is too small causes churning, with pages flushed and needed again shortly after, and one that is too large "may cause swapping due to competition for memory". For a database, swapping is usually worse than a smaller cache.

A sensible way to decide:

1. Start from the physical memory of the server or the memory limit of the container, not the host.
2. Subtract what other processes on the machine need: monitoring agents, backup tools, anything else that runs there.
3. Leave room for connections. If max_connections is high, read our article on MySQL "Too many connections" first; the right fix there is rarely "more connections".
4. Give the buffer pool most of what remains, and then watch the counters described below rather than trusting the formula.

If the whole working set fits comfortably, a larger pool will not make anything faster. A database of 20GB on a machine with 128GB of memory does not need a 100GB pool.

innodb_dedicated_server: letting MySQL decide

If MySQL is the only thing running on the server or VM, you can start it with innodb_dedicated_server=ON. InnoDB then calculates the buffer pool size from the detected memory: 128MB below 1GB, half of the memory between 1GB and 4GB, and 75% above 4GB. It also sets innodb_redo_log_capacity. The values are calculated only at startup. If you set innodb_buffer_pool_size explicitly, the explicit value wins and MySQL prints a startup warning saying the option was ignored for that variable. In MySQL 8.0 this option also set innodb_flush_method; in 8.4 it no longer does. Several InnoDB defaults changed in 8.4 as well, including innodb_flush_method itself; our MySQL 8.0 to 8.4 upgrade guide lists them.

The documentation recommends this option only when MySQL can use all of the machine's resources. On a server shared with an application, set the size by hand.

Chunks and instances

Two related settings explain why the value you set is not always the value you get:

• innodb_buffer_pool_chunk_size (default 128MB) is the unit in which the pool grows and shrinks. It can only be changed at startup.
• innodb_buffer_pool_instances splits the pool into several independent parts. In 8.4 the default is 1 for a pool of 1GB or less; above that, it is the smaller of half the number of chunks and a quarter of the logical CPUs, within 1 to 64.

The resize documentation states the rule: the buffer pool size is always a multiple of chunk_size * instances, and other values are adjusted automatically. Its example: with 16 instances and the default chunk size, a requested 9G becomes 10G. The same page recommends keeping the number of chunks (pool size divided by chunk size) at 1000 or fewer, and warns that changing the chunk size can unexpectedly increase the pool size, so calculate the effect first.

Checking the current state

These queries show the configuration and the most useful status counters:

SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS pool_gb,
       @@innodb_buffer_pool_instances                AS instances,
       @@innodb_buffer_pool_chunk_size / 1024 / 1024 AS chunk_mb;

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';

• Innodb_buffer_pool_read_requests counts logical read requests; Innodb_buffer_pool_reads counts the ones that could not be served from the pool and went to disk. Look at how both change over an interval, not at their totals since startup. If disk reads keep growing during normal load, the working set does not fit.
• Innodb_buffer_pool_wait_free counts the times InnoDB needed a clean page, found none and had to wait for dirty pages to be flushed. The documentation says that with a properly sized pool this value should be small.
• Innodb_buffer_pool_pages_free close to zero is normal on a busy server: InnoDB uses the memory it has. Free pages alone do not mean the pool is too small.
• SHOW ENGINE INNODB STATUS prints a Buffer pool hit rate line in its BUFFER POOL AND MEMORY section, a quick view of the same thing.

A high hit rate does not prove every query is fine. A query that scans millions of rows from memory is still slow; the MySQL slow query log is how you find those.

You may be tempted to look at which tables occupy the pool through sys.innodb_buffer_stats_by_table. The documentation warns that views built on INNODB_BUFFER_PAGE can affect performance and should not be queried on production unless you have decided the impact is acceptable. Run them on a test instance with a copy of the workload instead.

Resizing without a restart

innodb_buffer_pool_size is dynamic, so you can change it with SET GLOBAL:

-- 24 GiB; rounded to a multiple of chunk_size * instances
SET GLOBAL innodb_buffer_pool_size = 25769803776;

-- watch the resize
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE LOWER(variable_name) LIKE 'innodb_buffer_pool_resize%';

-- once you are happy with it, keep it across restarts
SET PERSIST innodb_buffer_pool_size = 25769803776;

What to expect, according to the documentation:

• The resize does not start until active transactions have completed.
• While it runs, new transactions and operations that need the buffer pool have to wait, except during the phases that defragment the pool and withdraw pages when it shrinks. During those phases access continues, but there may be a temporary shortage of free pages.
• Innodb_buffer_pool_resize_status_code shows the current stage (0 means no resize in progress, 7 means it failed), and Innodb_buffer_pool_resize_status_progress shows a percentage per stage.

Changing innodb_buffer_pool_chunk_size or innodb_buffer_pool_instances still requires a restart.

Restarts and warm-up

After a restart the pool is empty, and the first period of traffic reads everything from disk. MySQL reduces this with buffer pool dump and load, both enabled by default: innodb_buffer_pool_dump_at_shutdown records which pages were cached, and innodb_buffer_pool_load_at_startup reads them back in on startup. By default only the most recently used 25% of pages are recorded (innodb_buffer_pool_dump_pct). Progress is visible in Innodb_buffer_pool_load_status, and if the load adds too much overhead you can stop it with innodb_buffer_pool_load_abort. If a server is noticeably slow for a while after every restart, check that these settings have not been switched off.