Tuning innodb_buffer_pool_size for Your Workload

Every time I inherit a MySQL server, the first thing I check is innodb_buffer_pool_size, and more often than I'd expect it's still the 128 MB default on a box with 32 GB of RAM. That default exists so the server can start on anything. It's not a recommendation. On a dedicated database server the pool should hold as much of the working set as memory allows, otherwise the OS page cache ends up doing the work, and does it less efficiently.

I'll cover picking a size, changing it online on MySQL 5.7, and checking afterwards whether it helped.

What are you running now?

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

Then compare that with the size of your data:

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS innodb_gb
FROM information_schema.tables
WHERE engine = 'InnoDB';

If the total of your InnoDB tables is smaller than the memory you can spare, the answer is easy: make the pool a little larger than the data and the whole database lives in RAM.

Picking a target

The usual rule of thumb is 70 to 80 percent of RAM on a server that only runs MySQL. It's a starting point, not a law. I prefer working backwards from what else needs memory:

• The OS itself and its file cache for binary logs, redo logs and the error log: leave 1 to 2 GB minimum.
• Per-connection buffers: sort_buffer_size, join_buffer_size, read_buffer_size and tmp_table_size are allocated per session as needed. With max_connections = 500 and default sizes, worst case is several gigabytes.
• Other InnoDB memory: innodb_log_buffer_size, the adaptive hash index and the change buffer live inside the pool, but the dictionary cache and per-thread stacks do not.
• Anything else on the box, such as a web server, memcached or a backup running mysqldump.

On a 32 GB dedicated server I usually land at 22 to 24 GB. On an 8 GB box shared with an application, 3 to 4 GB is more realistic.

Chunks and instances

Since MySQL 5.7.5 the buffer pool is allocated in chunks of innodb_buffer_pool_chunk_size (128 MB default), and the total size is always rounded up to a multiple of chunk_size * instances. With the default 8 instances, that means the pool grows in 1 GB steps. If you request 22 GB you get 22 GB; if you request 22.5 GB you get 23 GB. Set the size as a clean multiple to avoid surprises.

innodb_buffer_pool_instances splits the pool into independent regions to reduce mutex contention. Keep each instance at least 1 GB. For a 24 GB pool, 8 instances is fine; for a 2 GB pool, use 1 or 2.

Resizing online (MySQL 5.7)

Resizing without a restart is one of my favorite things about 5.7. Set it dynamically first, then persist it in the config.

SET GLOBAL innodb_buffer_pool_size = 24 * 1024 * 1024 * 1024;

Watch progress:

SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

The resize happens in the background and may take a minute or two on a busy server, as it needs to withdraw pages when shrinking or allocate memory when growing. Then make it permanent in /etc/my.cnf on CentOS or /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu:

[mysqld]
innodb_buffer_pool_size      = 24G
innodb_buffer_pool_instances = 8

On MySQL 5.6 and MariaDB 10.1 the variable is not dynamic; edit the config file and restart during a maintenance window.

Warm restarts

While you're in the config file, make sure the pool warms itself after a restart. Both are on by default in 5.7, but I write them out anyway so nobody has to remember the defaults:

innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup  = 1

This writes the list of cached page IDs to ib_buffer_pool in the data directory at shutdown and reloads those pages on start, so the server does not spend its first hour after a restart reading everything from disk.

Did it help?

What matters is how often InnoDB has to go to disk because a page wasn't in the pool. Take two snapshots a few minutes apart during normal load:

SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests',
   'Innodb_buffer_pool_pages_free', 'Innodb_buffer_pool_pages_total',
   'Innodb_buffer_pool_wait_free');

Innodb_buffer_pool_reads counts reads that missed the pool; Innodb_buffer_pool_read_requests counts all logical reads. On a warmed-up server with an adequate pool, misses should be a tiny fraction of requests. If Innodb_buffer_pool_pages_free stays high for days after a restart, the pool is bigger than your working set and you could give memory back. If Innodb_buffer_pool_wait_free is climbing, InnoDB is waiting for the flusher to free pages, and you are short of memory or of I/O capacity.

Also check free -m and the Swap line. Any swap on a database server means the pool is too big for the machine, whatever the percentages said. I'd rather run a pool that's a bit too small than one that swaps.

Gotchas

[ERROR] InnoDB: Cannot allocate memory for the buffer pool at startup means the requested size does not fit. The server refuses to start. Lower the value; on a VM, check that the memory you think you have is actually assigned.

[Warning] InnoDB: Option innodb_buffer_pool_size was requested to be 22.5G but is set to 23G is the chunk rounding described earlier, not an error.

OOM killer kills mysqld shortly after a resize, with Out of memory: Kill process ... (mysqld) in /var/log/messages or journalctl -k. The pool plus per-connection memory exceeded RAM under load. Reduce the pool by a couple of gigabytes, or lower max_connections and the per-session buffers.

Resize appears stuck. Innodb_buffer_pool_resize_status shows Withdrawing blocks to be shrunken for a long time when shrinking a hot pool. It will finish; avoid issuing a second SET GLOBAL while the first is running.

It's a ten-minute change, and it often does more than every other tuning knob combined.