innodb_buffer_pool_size sets how much memory InnoDB gets for caching data and index pages — the single most influential MySQL setting for performance on a VPS, and at the same time one of the most often underestimated, or, the other way around, set to all available RAM with no margin left.
What the buffer pool actually caches
The buffer pool keeps in memory InnoDB table and index pages that have been read or changed, avoiding a repeat read from disk when the same data is accessed again. This applies only to InnoDB tables: for MyISAM memory is cached by a separate setting, key_buffer_size, and the difference between the engines is covered in the article on InnoDB and MyISAM.
If the entire working data set fits in the buffer pool, disk is barely used for reads — only writes and rare operations such as a full table scan touch it. If the data set is larger than the buffer pool, some pages get evicted and reread, and the bigger that mismatch is, the higher the disk load.
Why not just give the whole RAM to the buffer
Besides InnoDB, the operating system runs on the same server, MySQL itself reserves memory per connection for sorting and joins (sort_buffer_size, join_buffer_size), and if MySQL shares the VPS with PHP-FPM, Nginx or Redis, those services need memory too. A value of 100% of RAM is guaranteed to start swapping on the first spike in connections.
A practical starting point for a server where MySQL is the main workload is 50-70% of total RAM. If the same VPS also runs PHP-FPM and a site, the share should drop to 30-40%, leaving the rest for PHP processes, whose budget is covered in the article on sizing pm.max_children.
The formula and a worked example
In general form the formula is: buffer_pool equals total RAM minus memory for the OS and other services minus a margin for connections, multiplied by the chosen share. On a 4 GB VPS where MySQL shares the server with PHP-FPM and Nginx, after subtracting 1 GB for the system and services about 3 GB is left, of which 50-60% is a reasonable share for the buffer, that is, 1.5-1.8 GB as a starting point.
- the size of the entire working data set — if the database is smaller than the buffer, growing it further is pointless
- the number of concurrent connections and their sort_buffer_size, join_buffer_size
- other services on the same VPS — PHP-FPM, Redis, Nginx
- the kernel's swappiness setting — a low value reduces the risk of the buffer being pushed into swap
- warm-up time after a restart — an empty buffer reads from disk until it fills up again
Table: starting values by RAM size
The ranges in the table are a starting point for further measurement under your own load, not a ready answer for any server.
| Server RAM | MySQL is the main service | MySQL shares the VPS with PHP/Nginx |
|---|---|---|
| 2 GB | 1-1.2 GB | 0.5-0.7 GB |
| 4 GB | 2.5-3 GB | 1.5-1.8 GB |
| 8 GB | 5-6 GB | 3-4 GB |
| 16 GB | 10-12 GB | 6-8 GB |
How to check the size was chosen correctly
The exact answer comes from status variables, not the table above: the ratio of Innodb_buffer_pool_reads (pages read from disk) to Innodb_buffer_pool_read_requests (all page requests, including cache hits) shows the cache miss rate. If after a day of ordinary load the miss rate is noticeable and keeps growing, the buffer should be increased; if it barely changes when the buffer grows, the working set is already cached in full.
A separate and common source of confusion is the growth of the ibdata1 file on disk: it is not directly tied to innodb_buffer_pool_size and does not shrink when that setting changes, and where that growth comes from is covered in the article on ibdata1 and disk space.
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
SET GLOBAL innodb_buffer_pool_size = 1610612736;Frequently asked questions
Can innodb_buffer_pool_size be changed without restarting MySQL?
In current MySQL versions the size can be changed dynamically with SET GLOBAL, but the change applies gradually and the value itself is not kept after a restart unless my.cnf is edited too.
Will a bigger buffer fix slow queries?
No: the buffer speeds up reading pages that were already read, but a query without the right index still reads extra pages on every execution; working with indexes is covered in the article on MySQL indexes.
The server got slower after a restart even though buffer_pool_size did not change?
That is expected: right after startup the buffer is empty, and the first queries read pages from disk until the cache warms up again; speed comes back as the buffer fills with the working data set.