MariaDB Memory and Buffer Pool Tuning on a Small VPS

MariaDB’s default configuration is written for a server with several gigabytes of RAM and no idea what your workload looks like. On a 2 GB or 4 GB VPS, those defaults either reserve more memory than exists or leave the buffer pool so small that every other query hits the disk. The result is a database that looks healthy in top while spending most of its time in random I/O.

This walkthrough derives buffer pool and allocator settings from your actual instance size, applies them in a testable way, and verifies the outcome with InnoDB’s own counters. It assumes InnoDB only — if you still run MyISAM, convert first, since MyISAM’s key cache behaves completely differently and cannot use the buffer pool at all. The sizing notes below assume a general-purpose instance from a Cloud VPS plan with local SSD storage.

Establishing the Memory Budget

Start from physical RAM and subtract what the OS and your other services need, not what MariaDB would like. A practical allocation for a single-purpose database VPS is roughly 60–70% of total RAM to innodb_buffer_pool_size, leaving room for connections, sort buffers, the binary log cache, and the page cache the OS uses for redo and binlogs. On a 4 GB instance that means a 2.5 GB buffer pool, not 3.8 GB.

Confirm the ceiling the OS actually enforces with systemctl show mariadb | grep -i memorylimit and cat /sys/fs/cgroup/memory.max. If a cgroup limit is lower than physical RAM, budget from the limit. Also check swap behavior: run cat /proc/sys/vm/swappiness, and on a database host set it to 1 in /etc/sysctl.d/99-db.conf. Buffer pool pages swapped to disk are worse than a smaller pool that stays resident.

Setting innodb_buffer_pool_size Correctly

In /etc/mysql/mariadb.conf.d/99-tuning.cnf, set innodb_buffer_pool_size = 2560M for a 4 GB host with only the database running. Below 1 GB of pool, consider dropping to a smaller instance or reducing dataset size, because InnoDB’s internal overhead becomes a meaningful fraction of the pool. Set the instance count to one (innodb_buffer_pool_instances = 1) below 1 GB and to 2–4 above that; too many instances fragment the pool’s effectiveness on small datasets.

Note that MariaDB 10.5 and later support online resizing, so you can test without a restart: SET GLOBAL innodb_buffer_pool_size = 3221225472;. The change persists only until restart, which makes it a safe experiment. When you find the value that fits, write it into the config file and apply it with systemctl restart mariadb. Verify the effective value with SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; — a common failure is a later-loading config file overriding yours, visible via SHOW VARIABLES LIKE 'innodb_buffer_pool_size'\G after checking mysqld --verbose --help | grep -A1 "Default options".

Reading the Buffer Pool Hit Ratio Honestly

The widely quoted formula 1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) is misleading because it includes logical reads that never touch storage. After a fresh restart it reads as 99.9% no matter how badly sized the pool is. Measure the trend instead: SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; twice, ten minutes apart, and compute the delta. Sustained physical reads above 50 per second on an OLTP workload means the working set does not fit.

Use Innodb_buffer_pool_pages_free and Innodb_buffer_pool_pages_dirty together to judge headroom. Steady-state free pages near zero with a stable working set means the pool is well sized. Free pages stuck above 30% means you over-allocated and are starving the OS page cache. For per-table detail, query information_schema.INNODB_SYS_TABLESPACES to find the largest tables and confirm they dominate the pool.

Tuning the Per-Connection Buffers

Per-connection buffers are the most common cause of OOM on small VPS instances because they multiply by connection count. The dangerous ones are sort_buffer_size, join_buffer_size, read_buffer_size, and read_rnd_buffer_size. MariaDB allocates these per connection as needed, so 200 idle connections with 4 MB sort buffers can consume gigabytes in a burst. On a 4 GB host, keep sort_buffer_size at 2M and join_buffer_size at 1M.

Then bound the connection count itself. Set max_connections = 150 and put a pooler in front so the application does not open 500 idle sessions. Watch Max_used_connections and Threads_connected; if the peak approaches the limit, fix the application’s connection handling before raising the ceiling. Check for runaway queries using SHOW FULL PROCESSLIST and enable slow_query_log = 1 with long_query_time = 1 to catch them systematically.

Redo Log, Flush Method, and Durability Trade-offs

Redo log sizing is now a function of the pool rather than a fixed default. In MariaDB 10.8 and later, innodb_redo_log_capacity defaults to a fraction of the buffer pool; a reasonable setting is half the pool size, which gives you roughly 30 minutes of write headroom at typical rates. Too small, and checkpointing becomes continuous and fsync-bound; too large and crash recovery takes minutes on a small VPS.

The flush method matters more than most guides admit. innodb_flush_method = O_DIRECT avoids double-buffering between the pool and the OS page cache, which is the right choice for most SSD-backed instances. Keep innodb_flush_log_at_trx_commit = 1 for anything with financial or transactional correctness requirements; setting it to 2 trades up to one second of committed data on crash for a large write throughput gain, and that trade should be an explicit business decision, not a default. Verify the I/O path with iostat -x 1 and confirm await stays under the device’s baseline.

Temp Tables, Sort Space, and Disk Spills

On small instances, temp tables spill to disk and dominate the slow query log. Track Created_tmp_disk_tables and Created_tmp_tables globally with SHOW GLOBAL STATUS LIKE 'Created_tmp%';. If disk tables exceed 10% of total temp tables, either raise tmp_table_size and max_heap_table_size together (keep them equal, at 32M on a 4 GB host) or fix the queries creating them, usually GROUP BY on large TEXT columns.

Point tmpdir at fast local storage rather than a network volume, and verify free space with df -h /tmp. MariaDB does not honor TMPDIR unless you set the variable explicitly. If you see Error 28: No space left on device in the error log, check whether a single large sort filled the temp filesystem.

Validating the Change Under Real Load

Config changes are hypotheses until measured. Capture a baseline with SHOW GLOBAL STATUS before and after a representative workload, then compute queries per second, buffer pool physical reads, and time spent on disk. Use sysbench oltp_read_write against a dataset about twice the pool size to simulate an over-large working set, and confirm improvement in p95 query latency rather than averages.

After applying the final configuration, run mysqltuner --buffers for a second opinion and verify no swapping with vmstat 1 5. If you want the surrounding platform tuned as well — file descriptor limits, transparent huge pages, I/O scheduler — the managed versus unmanaged hosting overview explains which of these you own on a self-managed plan.

Summary: Size From RAM, Verify From Counters

Derive the buffer pool from a realistic RAM budget, keep per-connection buffers small, bound connections, and size the redo log relative to the pool. Then validate with physical read rate and p95 latency instead of the hit-ratio formula. These five changes cover most of the performance available on a small VPS without sacrificing durability.