InnoDB Buffer Pool Sizing on a Shared VM: Stop the Swap

The default InnoDB buffer pool size is 128MB. On a dedicated database server with 64GB of RAM, that's laughably conservative. On a shared 2GB VM running MariaDB, PHP-FPM, Nginx, and a Laravel queue worker, it might actually be the right ballpark — or it might be half the reason your site is swapping at 2am under load. Getting this number wrong is one of the most common performance mistakes I see on self-managed VPS boxes, and it's almost never talked about directly.

What the Buffer Pool Actually Does

InnoDB's buffer pool is a chunk of RAM reserved for caching data pages, index pages, and other structures. When a query touches a row, MariaDB tries to serve it from the buffer pool first. If the page isn't there, it reads from disk, loads it in, and evicts something else if the pool is full.

The practical effect: a well-sized buffer pool means most reads are in-memory. An undersized one means MariaDB is hammering your NVMe (or worse, your shared cloud storage) on every other query. An oversized one on a constrained VM means the OS starts swapping, and once Linux starts swapping on a database box, everything turns to molasses. PHP-FPM workers start timing out, opcache gets thrashed, and your monitoring alerts fire at 3am.

The Machines I'm Actually Configuring

NWOS runs managed hosting for clients — mostly Laravel apps, some WordPress, some custom PHP. A typical box is a 4GB or 8GB VPS on Vultr or Hetzner, running:

• Nginx
• PHP-FPM (usually 8.2 or 8.3, multiple pools sometimes)
• MariaDB 10.11
• Redis
• Supervisor (queue workers)
• Occasional cron jobs

These aren't microservices. Everything lives on one box. The database doesn't get 80% of RAM because PHP-FPM needs room to breathe too.

The Formula

Here's what I use. It's not from the MariaDB docs — it's from working through a few painful afternoons of swap storms and building a mental model of where memory actually goes.

innodb_buffer_pool_size = (Total RAM - OS overhead - PHP-FPM footprint - Redis footprint - misc) * 0.85

Broken down:

Let me walk through a real example. A 4GB box running a Laravel e-commerce app:

• Total RAM: 4096MB
• OS + Nginx: ~350MB
• PHP-FPM: 20 workers × 55MB = 1100MB
• Redis: 256MB (I set maxmemory 256mb in redis.conf)
• MariaDB non-buffer overhead: 192MB
• Total non-buffer: 1898MB
• Available for buffer pool: 4096 - 1898 = 2198MB
• Apply 85% safety: 2198 * 0.85 ≈ 1868MB
• Round down to: 1.8GB (1843M or just 1800M)

I set it in /etc/mysql/mariadb.conf.d/99-local.cnf:

[mysqld]
innodb_buffer_pool_size = 1800M
innodb_buffer_pool_instances = 2

The innodb_buffer_pool_instances line matters above 1GB. MariaDB recommends one instance per gigabyte, up to 8. Two instances on a 1.8GB pool reduces mutex contention on multi-core writes.

Measuring PHP-FPM's Actual Footprint

The 55MB-per-worker number I used above isn't a guess — it's something you measure. Don't assume. Here's how I check it:

ps --no-headers -o rss -C php-fpm8.2 | awk '{sum += $1} END {print sum/1024 " MB total"; print NR " workers"; print sum/NR/1024 " MB avg"}'

On a busy Laravel app with a fat composer autoload and a few packages that eagerly initialize, I've seen workers hit 90-110MB. On a lean app, 35-45MB. The difference completely changes what you can give the buffer pool.

If your workers are large, the right fix is usually fixing the app (lazy loading, reducing eager service provider bootstrapping) before tuning MariaDB. A 20-worker pool at 100MB each is 2GB just for PHP. On a 4GB box, you're already in trouble before MariaDB even loads a page.

The PHP Script I Use During Provisioning

When I'm provisioning a new managed client box, I run a quick calculation script to spit out a suggested config:

<?php

$total_mb = (int) shell_exec("awk '/MemTotal/ {print int($2/1024)}' /proc/meminfo");

// Measure PHP-FPM avg worker RSS
$fpm_output = shell_exec("ps --no-headers -o rss -C php-fpm8.2 2>/dev/null");
$fpm_rss = array_filter(array_map('trim', explode("\n", trim($fpm_output ?? ''))));
$fpm_avg_mb = count($fpm_rss) > 0
    ? array_sum($fpm_rss) / count($fpm_rss) / 1024
    : 55; // fallback estimate
$fpm_workers = count($fpm_rss) ?: 10;

// Known fixed costs
$os_mb       = 350;
$nginx_mb    = 75;
$redis_mb    = 256; // matches your maxmemory setting
$mariadb_overhead_mb = 200;

$reserved = $os_mb + $nginx_mb + $redis_mb + $mariadb_overhead_mb
          + ($fpm_avg_mb * $fpm_workers);

$available = $total_mb - $reserved;
$buffer_pool = (int) ($available * 0.85);
$buffer_pool = max(256, $buffer_pool); // never go below 256MB

$instances = min(8, max(1, (int) ($buffer_pool / 1024)));

echo "Total RAM:          {$total_mb} MB\n";
echo "Reserved (non-DB):  {$reserved} MB\n";
echo "Suggested pool:     {$buffer_pool} MB\n";
echo "Pool instances:     {$instances}\n";
echo "\n[mysqld]\n";
echo "innodb_buffer_pool_size = {$buffer_pool}M\n";
echo "innodb_buffer_pool_instances = {$instances}\n";

This isn't production-grade tooling. It's a scratchpad script I run as root during setup, read the output, sanity-check it, and paste the relevant lines into my MariaDB config. Don't automate it blindly — a wrong measurement of PHP-FPM workers (say, you ran it before FPM was warm) will produce a bad recommendation.

Gotchas That Have Bit Me

MariaDB doesn't immediately use the full buffer pool. It grows into it over time. If you restart MariaDB and immediately look at free -m, it'll look fine. Wait for production traffic to warm it up. I've been fooled by this, declared the sizing fine, and then had a swap storm 20 minutes later when the pool filled and MySQL kept going.

innodb_buffer_pool_chunk_size matters. If you set innodb_buffer_pool_size to a value that isn't a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, MariaDB will silently round up to the next multiple. On small boxes, this rounding can push you over your budget. Check the actual value after restart:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

If it's bigger than what you set, do the math:

SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size';

Default chunk size is 128MB. If you set innodb_buffer_pool_size = 900M with 2 instances, MariaDB wants a multiple of 256MB, so it rounds to 1024MB. That's 124MB you didn't plan for.

vm.swappiness is a lever you should pull. On a database box, I set it to 10 or even 1. The kernel will still use swap in extremis, but it won't aggressively move memory to swap under normal pressure:

echo 'vm.swappiness = 10' >> /etc/sysctl.d/99-mariadb.conf
sysctl -p /etc/sysctl.d/99-mariadb.conf

Don't forget transparent huge pages. On some distros, THP can cause latency spikes with MariaDB. I disable it at boot. Not directly related to buffer pool sizing, but if you're tuning for performance on a shared VM, it's the next thing to look at after you get the buffer pool right.

When to Just Give MariaDB Its Own Box

If your PHP-FPM pool needs 30+ workers to handle your traffic, you're probably past the point where a shared VM makes sense. That's 1.5–3GB of RAM just for PHP, and your database is fighting for scraps. I had a healthcare client — appointment booking app, moderate but spiky traffic — where we kept trying to squeeze more performance out of a 8GB shared box. Eventually I just moved MariaDB to a separate 4GB instance, gave the buffer pool 2.5GB, and the p99 query latency dropped by 60%. The shared-VM optimization ceiling is real.

The formula I described is for boxes where shared makes economic and operational sense: dev environments, smaller production apps, sites with predictable low-to-medium traffic. When your app outgrows it, split the services. Don't keep tuning.

When I'd Reach for This

• Any new VPS provisioning where MariaDB and PHP-FPM coexist
• Diagnosing swap usage on an existing box (check current buffer pool first — it's usually the culprit)
• Client sites on Hetzner CX21/CX31 (2-4GB) where I need to be deliberate about every MB

I would not spend time on this formula for a dedicated RDS instance or a PlanetScale-managed database. Managed services handle this for you (or at least the blast radius is theirs). This is squarely a self-hosted, shared-VM problem.

Get the buffer pool wrong and you're either leaving performance on the table or you're creating a swap-induced death spiral under load. Neither is acceptable for a production app. Measure your PHP-FPM workers, account for Redis and the OS, apply the 15% haircut, and check what MariaDB actually allocated after restart. It takes 20 minutes and it's one of the highest-leverage tuning moves on a constrained box.