The InnoDB buffer pool is MySQL’s and MariaDB’s main memory cache. It holds table data and indexes so queries can be answered from RAM instead of disk. On most database servers, getting its size right is the single most important configuration change.

How much?

The goal: hold your working set — the data and indexes that are actually used — in memory.

Server Buffer pool
Dedicated database server 60–75% of RAM
Database + web server + PHP on one VPS 25–40% of RAM
Small VPS (1–2 GB) with a full web stack 128–512 MB

Never set it so high that the OS, other services and per-connection buffers don’t fit — swapping or the OOM killer will hurt far more than a smaller cache.

Upper bound: there’s no benefit in making it much larger than your data + indexes:

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

If your database is 600 MB, a 1 GB buffer pool holds all of it plus room to grow.

Configure it

[mysqld]
innodb_buffer_pool_size = 2G

Check the current value:

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS mb;

MySQL 8.0 also offers innodb_dedicated_server = ON, which sizes the buffer pool (and redo log) automatically from available memory — only for servers running nothing but MySQL.

Resize without a restart

Since MySQL 5.7 (and MariaDB 10.2), the buffer pool can be resized online:

SET GLOBAL innodb_buffer_pool_size = 3221225472;   -- 3 GB in bytes
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

The size is rounded to a multiple of innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances. Persist the value in the config file (or with SET PERSIST on MySQL 8) so it survives a restart.

Is it big enough?

Hit ratio

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
  • Innodb_buffer_pool_read_requests — logical reads (from memory).
  • Innodb_buffer_pool_reads — reads that had to go to disk.

Hit ratio = 1 − reads / read_requests. After a warm-up period, OLTP workloads typically show 99%+. A lower ratio together with high disk reads suggests the pool is too small (or queries scan too much data).

How full is it?

SELECT FORMAT(pages_data * 100.0 / pages_total, 1) AS pct_used
FROM (SELECT
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_data') AS pages_data,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') AS pages_total) t;

If it stays well below 100% after days of normal traffic, you can shrink it and give the memory to something else.

Free pages and waits

Innodb_buffer_pool_wait_free > 0 means queries had to wait for clean pages — the pool is under pressure or flushing can’t keep up.

Buffer pool instances

innodb_buffer_pool_instances splits the pool to reduce contention on many-core servers with large pools (default 8 when the pool is ≥ 1 GB on MySQL). On small VPS servers, the default is fine. (MariaDB 10.5+ removed multiple instances.)

Warm-up after restarts

After a restart the pool is empty, and the first minutes are slower. InnoDB saves the list of cached pages at shutdown and reloads them at startup — on by default:

innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

Bigger isn’t a substitute for indexes

A query scanning millions of rows is slow even from memory, and it evicts useful pages for everyone else. Fix the heavy queries first — see the slow query log and indexing basics.

Example configurations

4 GB VPS, WordPress + MariaDB + PHP-FPM + Nginx:

innodb_buffer_pool_size = 1G

8 GB dedicated database server:

innodb_buffer_pool_size = 5G
innodb_redo_log_capacity = 1G      # MySQL 8.0.30+; innodb_log_file_size on MariaDB

Related: MySQL performance tuning and how much RAM your VPS needs. When the working set outgrows memory, more RAM is the cure — our VPS plans scale up to 40 GB.

  • MySQL
  • MariaDB
  • Performance