Sizing the InnoDB Buffer Pool for a 1 GB VPS: The Arithmetic Nobody Shows You

The default MySQL configuration on most VPS images allocates `128M` to the InnoDB buffer pool. On a 1 GB instance running WordPress, that leaves roughly 700 MB of RAM idle while every `SELECT` that misses the pool goes to disk. Raising it is not guesswork — it is arithmetic with one hard ceiling and two soft ones. Here is the calculation, the configuration, and the counter readings that tell you when to stop.

The One Number You Must Not Exceed

The buffer pool is not the only thing that uses RAM in a MySQL process. `innodb_buffer_pool_size` is the largest single allocation, and everything else in the process — per-connection buffers, the redo log, temporary tables, InnoDB internal structures — sits on top of it. Total RSS for a `mysqld` sized this way lands around `buffer_pool + 150–250 MB` under light concurrency.

VPS RAM	OS + web stack	Recommended pool	Expected mysqld RSS
1 GB	~300 MB (nginx + PHP-FPM 4 workers)	384M – 512M	560–760 MB
2 GB	~500 MB	768M – 1G	950 MB – 1.25 GB
4 GB	~800 MB	2G – 2.5G	2.2–2.9 GB
8 GB	~1.5 GB	4G – 5G	4.3–5.5 GB

The formula a DBA would write down: take `MemTotal`, subtract 350 MB for the kernel, SSH, systemd, and your monitoring agent, subtract the resident set of nginx plus PHP-FPM, subtract 200 MB of safety margin, and give the rest to the pool — capped at 75% of total RAM so page cache stays warm. On a 1 GB box that lands at 384–512 MB.

Confirm Your PHP-FPM Footprint First

Do not guess the web stack’s share. Measure it before you decide:

# Real RSS of the components that compete with MySQL
sudo systemctl restart php8.3-fpm nginx mysqld
sleep 5
ps -eo rss,comm --sort=-rss | awk 'NR==1 || $1>5000{printf "%-8.1f MB  %s\n", $1/1024, $2}'
echo "--- MemAvailable ---"; awk '/MemAvailable/{printf "%.0f MB\n", $2/1024}' /proc/meminfo

PHP-FPM with 4 `pm.max_children` runs about 30–45 MB per worker for a typical WordPress stack, so 4 workers is 120–180 MB. If you see 8 workers, your pool budget is gone and the correct fix is `pm.max_children = 4`, not a smaller buffer pool.

Configuration That Actually Applies

# /etc/mysql/mysql.conf.d/zz-vps-tuning.cnf
[mysqld]
innodb_buffer_pool_size         = 448M
innodb_buffer_pool_instances    = 1      # one instance per ~1 GB of pool
innodb_log_file_size            = 256M   # bigger = fewer checkpoints, faster writes
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2      # 1 = fully durable, 2 = ~1s loss window
innodb_io_capacity              = 2000   # NVMe; SSD ~800, spinning ~200
innodb_io_capacity_max          = 4000
innodb_read_io_threads          = 4
innodb_write_io_threads         = 4
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON
tmp_table_size                  = 32M
max_heap_table_size             = 32M
performance_schema              = OFF

`innodb_buffer_pool_instances = 1` matters more than people expect on small instances. Multiple instances each carry their own overhead and reduce the effective pool; the gain from splitting is only worth it above roughly 1 GB of pool. `performance_schema = OFF` recovers on the order of 150 MB of RSS on MySQL 8 — a meaningful amount when your pool is 448 MB.

Changing `innodb_log_file_size` requires a clean shutdown of MySQL (with a clean `innodb_fast_shutdown=0`) or the server refuses to start with the new size. Do this during a maintenance window, not on a live box.

The Three Readings That Tell You to Stop Tuning

• Buffer pool hit rate — `SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_read%’`. Target 99.9%+. The ratio is `reads/read_requests`; below 99% means genuine working-set misses and a larger pool or an index fix.
• Pages free — `SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages_free’`. If this is consistently above ~5% of `pages_total`, the pool is oversized. Give the RAM back to page cache.
• No swap growth — if `pswpin` in `/proc/vmstat` moves while MySQL is under load, the pool is too large and the kernel is paging out parts of it. Shrink by 64 MB and retest.

Run one clean measurement cycle. Restart MySQL with the new config, warm the pool by replaying representative traffic for a few minutes, then take the readings. If the hit rate is above 99.9%, pages free are near zero, and swap is quiet, stop — you are done, and further increments to the pool will only push `mysqld` RSS past the point where the OS starts reclaiming the page cache that your file reads depend on.

Where the Working Set Actually Comes From

Buffer pool sizing only helps if the data fits. On a WordPress VPS the hot set is typically `wp_posts`, `wp_postmeta`, and `wp_options`, which for a 5,000-post site is often under 200 MB. Run `SELECT table_name, ROUND((data_length+index_length)/1024/1024,1) AS mb FROM information_schema.tables WHERE table_schema=’wordpress’ ORDER BY mb DESC LIMIT 15;` — if the sum of that list is under your pool size, a 99.9% hit rate is achievable and the remaining latency you see is query-shaped, not memory-shaped.

Two Settings That Undo Your Sizing Work

A correctly sized buffer pool can still be defeated by two defaults that ship on most MySQL 8 images. First, `innodb_page_size` combined with a mismatched `innodb_buffer_pool_chunk_size`: the pool is allocated in chunks, defaulting to 128 MB, and MySQL silently rounds your setting up to the nearest multiple. Asking for 448 MB on a default install gets you 512 MB — and on a 1 GB VPS that missing 64 MB is the difference between page cache and swapping. Second, `innodb_numa_interleave` on multi-socket hosts, which is irrelevant on a VPS but occasionally set by over-eager hardening guides and causes uneven page placement.

-- Confirm what MySQL actually allocated, not what you asked for
SELECT @@innodb_buffer_pool_size/1024/1024 AS configured_mb,
       @@innodb_buffer_pool_chunk_size/1024/1024 AS chunk_mb,
       @@innodb_buffer_pool_instances AS instances;
-- The allocated total is rounded UP to a multiple of chunk_mb * instances.
-- With chunk 128M and instances 1, 448M becomes 512M. Set chunk_size explicitly:
--   innodb_buffer_pool_chunk_size = 64M   -> 448M is then honoured exactly.

Verify with `SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages_total’` and multiply by `innodb_page_size` (16384 by default). If the product does not match your intended size, you are running with more pool than you budgeted and less kernel page cache than you planned for.

Reading the Hit Rate Correctly

The common mistake is computing the hit rate from `Innodb_buffer_pool_reads` against `Innodb_buffer_pool_read_requests` over a lifetime — which includes the cold start and therefore always looks worse than reality. Compute it over an interval instead, so you are measuring steady state:

-- Sample 1
SHOW GLOBAL STATUS WHERE Variable_name IN
 ('Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests');
-- wait 300 seconds under normal traffic
-- Sample 2, then: hit% = (1 - (reads2-reads1)/(requests2-requests1)) * 100
-- Target: > 99.9%. Between 98% and 99.9% the pool is slightly small;
-- below 95% you are losing to disk on a meaningful share of reads.

If your host is genuinely too small for the working set, sizing is the wrong lever. Our VPS performance guides cover the migration and resize path, and the provider comparison lists plans with the RAM headroom where a 2 GB pool is not a compromise. Sizing a 448 MB pool is an optimisation; needing a 2 GB pool is a purchasing decision.

If you are tuning a small VPS and want hardware that does not fight you, our VPS provider performance tables break down CPU steal, NVMe IOPS, and RAM overcommit behaviour across the hosts we test on. Newer KVM nodes with dedicated vCPU pinning make the numbers in this article reproducible rather than aspirational. Two hosts we keep coming back to: InterServer VPS for flat-rate pricing with no RAM upcharge, and Cloudways managed cloud if you would rather not manage the kernel yourself. Compare the two against the benchmark methodology we publish before you commit.