Tune MySQL InnoDB Buffer Pool Size for a VPS

The MySQL InnoDB buffer pool size controls how much of your database MySQL can hold in RAM instead of reading from disk. On a small VPS the default is usually far too small, so every query waits on disk I/O. This guide shows you how to measure the current pool, pick a value that fits your RAM, and apply it in the config file.

Before you start

You need:

• A Linux VPS running MySQL 8.0/8.4 or MariaDB 10.x/11.x, with root or sudo access.
• The ability to restart MySQL. That means a short outage — plan it.
• A rough idea of how much RAM the server has and what else runs on it (PHP-FPM, Nginx, Redis).

If you are not sure where your config file lives, see where my.cnf lives on MySQL and MariaDB. On Ubuntu and Debian it is normally /etc/mysql/mysql.conf.d/mysqld.cnf; on AlmaLinux and Rocky it is usually /etc/my.cnf.d/mysql-server.cnf.

How the InnoDB buffer pool works

InnoDB keeps data pages and index pages in memory in the buffer pool. When a query needs a page that is already there, that is a logical read and it is fast. When the page is not there, InnoDB reads it from disk — a physical read — and that is where your latency comes from.

The ratio between the two is your hit rate. A healthy OLTP workload sits above 99%. If yours is much lower, the working set is bigger than the pool and you are paying for disk on every query.

The pool is the single largest memory consumer in MySQL, so it is also the first thing to tune when a VPS feels slow.

Check your current MySQL InnoDB buffer pool size

Start with the memory you actually have free.

free -m

Then look at the pool itself.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

The size is reported in bytes. Divide by 1073741824 for gigabytes.

Now check the hit rate. Run these two, then compare the numbers.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Innodb_buffer_pool_read_requests counts logical reads, Innodb_buffer_pool_reads counts reads that had to go to disk. The hit rate is:

hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

On a busy server, sample it twice a few minutes apart rather than trusting a single reading. Finally, size your data so you know what you are trying to fit:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

If the total is smaller than the RAM you can spare, the whole database can live in memory. That is the goal.

Pick a buffer pool size for your VPS

There is no single correct number, but these rules keep you out of trouble:

• Dedicated database VPS: set the pool to roughly 60–70% of total RAM.
• Shared VPS (web server plus database): set it to 40–50% and leave room for PHP, Nginx and the OS page cache.
• Always leave headroom. The OS, MySQL's per-connection buffers, the redo log and temporary tables all need memory too. On a 2 GB VPS, a 1 GB pool is a sane starting point.
• Do not exceed your data size. A pool larger than the working set wastes RAM that other processes could use.
• Keep innodb_buffer_pool_instances at 1 on small VPS plans. Multiple instances only help large pools under high concurrency, and they add overhead.

Set innodb_buffer_pool_size in the config file

Back up the config first, so you can roll back in seconds.

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

Open the file and add the settings under the [mysqld] section.

[mysqld]
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

The last two lines save the warm pages on shutdown and reload them at startup, so the server is not cold after a restart.

Restart the service. Use mariadb instead of mysql on MariaDB systems.

sudo systemctl restart mysql
sudo systemctl status mysql --no-pager

MySQL 5.7 and later, and recent MariaDB releases, can also resize the pool at runtime without a restart:

SET GLOBAL innodb_buffer_pool_size = 1073741824;

That change is not persistent — always write it to the config file as well. Check your version's documentation, since runtime resizing behaviour differs between releases.

Check it works

Confirm the running value matches what you set.

SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS pool_gb;

Then let the server run under normal traffic for a while and re-check the hit rate.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

You want Innodb_buffer_pool_reads to grow slowly relative to Innodb_buffer_pool_read_requests. Also confirm nothing else got squeezed:

free -m
sudo dmesg -T | grep -i "killed process"

No OOM messages and a rising hit rate means the change worked.

Troubleshooting

• MySQL will not start after the change — the value is larger than available RAM, or the line is malformed. Check sudo journalctl -u mysql -n 50, then restore the .bak file and restart.
• The reported pool size is smaller than what you set — InnoDB rounds the value to a multiple of innodb_buffer_pool_chunk_size multiplied by innodb_buffer_pool_instances. Set a value that divides evenly, or accept the rounding.
• Hit rate stays low after doubling the pool — your working set is larger than RAM. Add indexes, archive old rows, or move to a bigger plan; more memory alone will not fix a full table scan.
• mysqld gets killed under load — the OOM killer is reclaiming memory. Lower innodb_buffer_pool_size and review other consumers with ps aux --sort=-rss | head.
• Performance got worse, not better — you may have starved the OS page cache or PHP workers. Reduce the pool by 25% and measure again.

Next steps

• Work through the full memory picture with tuning MySQL and MariaDB memory for a small VPS.
• Find the queries that are still hitting disk in finding slow queries with the slow query log.
• Check which tables are actually large in checking database and table sizes in MySQL.
• Confirm your config file location in where my.cnf lives.