MySQL on a Constrained VPS: Budgeting Buffers by Subtraction, Not Guesswork

MySQL and MariaDB are the most common cause of an OOM kill on a 1–2 GB VPS, and the usual fix — “lower innodb_buffer_pool_size” — often makes things worse because it ignores where the rest of the memory went. This article works through the actual budget on constrained servers: what each buffer costs, which ones scale with connections, and how to size them so the database stays in RAM and the kernel does not reach for the OOM killer.

Read the real memory map first

Start from what the running server reports, not from a config template. Connect and run:

SELECT
  VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_pages_total',
  'Innodb_buffer_pool_pages_free',
  'Threads_connected'
);

Then check the OS view with ps_mem or smem -tk, which accounts for shared pages properly. RSS from ps overstates MySQL’s footprint because the buffer pool is shared memory; PSS from smem is the number to compare against your RAM budget.

The four buffers that actually matter

• innodb_buffer_pool_size — the big one. Targets 60–70% of RAM on a dedicated database host; on a web VPS that also runs PHP, keep it lower (see below).
• Per-connection buffers — sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size. These are allocated per connection when needed, so the worst case is workers × size.
• tmp_table_size / max_heap_table_size — in-memory temp tables, also per connection.
• InnoDB log and dictionary — innodb_log_buffer_size plus a few hundred MB of fixed overhead you cannot configure away.

The trap is the per-connection group. Setting sort_buffer_size = 16M with 50 max connections means a theoretical 800 MB of sort memory on a server that only has 1 GB. Most connections never allocate it, but a bad query plan on a busy day will.

A measured budget for a 2 GB VPS

Assume the VPS runs the web stack too. Reserve roughly: 350 MB for the OS and page cache floor, 300 MB for PHP-FPM and nginx, then give the rest to MySQL. That lands here:

[mysqld]
innodb_buffer_pool_size      = 512M
innodb_buffer_pool_instances = 2
innodb_log_file_size         = 128M
innodb_flush_method          = O_DIRECT

# per-connection, deliberately small
sort_buffer_size             = 1M
join_buffer_size             = 1M
read_buffer_size             = 512K
read_rnd_buffer_size         = 512K
tmp_table_size               = 16M
max_heap_table_size          = 16M

max_connections              = 40
table_open_cache             = 400
innodb_open_files            = 400

performance_schema           = OFF

Two deliberate choices stand out. First, innodb_buffer_pool_instances = 2 with a 512M pool reduces mutex contention on multi-core instances without splitting the pool so finely that it wastes memory. Second, disabling performance_schema frees 100–200 MB on MariaDB builds where it is enabled by default — re-enable it temporarily when you need to diagnose, then turn it off again.

Watch it rather than guess it

After applying changes, restart (buffer pool size is not fully online-settable in older versions) and watch the working set over a few days:

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- read_requests vs reads: the miss ratio is reads / read_requests

If the miss ratio stays under ~1% after a day of normal traffic, the pool is large enough and you should not grow it. If it sits at 5%+, you are either under-provisioned or running queries that scan more than they need — check EXPLAIN before adding RAM.

Match the budget to the server, not the template

On a 1 GB VPS, drop the pool to 256M and cut max_connections to 25. On a 4 GB server that only runs the database, push the pool to 2–3 GB and raise per-connection buffers only when you see sort or join spills in SHOW STATUS LIKE 'Created_tmp_disk_tables'. Sizing is a subtraction problem: total RAM minus everything that is not MySQL, and the remainder is your pool. Run the same arithmetic against any managed or self-managed hosting plan you deploy and the OOM kills stop being mysterious.