MariaDB Memory and Buffer Pool Tuning on a Small VPS: A Step-by-Step Walkthrough

Default MariaDB installs are tuned to run on anything — a Raspberry Pi, a 128 GB RAM bare-metal box, an old laptop. That conservatism is deliberate, but it means your VPS is almost certainly leaving query throughput on the table. The two knobs that move the needle most on a modest VPS are the InnoDB buffer pool and the query cache replacement known as the buffer pool instance layout, followed by connection and temp-table handling.

This walkthrough assumes a 2–4 GB Linux VPS running MariaDB 10.6+ on Ubuntu or Debian, serving a web application. Every change is measured before and after, because “it feels faster” is not a benchmark.

Step 1: Establish a Baseline Before Touching Anything

Never tune against a moving target. Install sysbench and run a fixed OLTP workload so you have a number to beat:

sudo apt update && sudo apt install -y sysbench
sudo sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-user=root --mysql-db=bench \
  --table-size=100000 --tables=4 --threads=4 prepare



sudo sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-user=root --mysql-db=bench \
  --table-size=100000 --tables=4 --threads=4 --time=60 run | tee baseline.txt

Record transactions per second (TPS), 95th-percentile latency, and any “Threads fairness” warnings. Then capture the current config so you can roll back:

sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.bak
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

Step 2: Size the InnoDB Buffer Pool Correctly

The buffer pool caches table and index pages in memory. On a database-dedicated VPS, allocate 50–70% of total RAM. On a VPS that also runs Nginx and PHP, keep it at 25–40% so the web tier does not get pushed into swap.

For a 4 GB VPS running both web and database:

# /etc/mysql/mariadb.conf.d/60-tuning.cnf
[mysqld]
innodb_buffer_pool_size = 1280M
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

Notes on each choice:

buffer_pool_instances — split the pool into 1 instance per GB, max 8, to reduce mutex contention on multi-core VPSes.
innodb_log_file_size — 256 MB reduces checkpointing pressure. Larger redo logs mean less frequent flushes and better write throughput.
innodb_flush_method = O_DIRECT — bypasses the OS page cache, avoiding double-buffering on virtualised storage.
flush_log_at_trx_commit = 2 — flushes once per second instead of per commit. This trades up to one second of committed transactions on a sudden power loss for a large throughput gain. Keep it at 1 for financial data.

Check how much of your working set actually fits: if Innodb_buffer_pool_reads keeps climbing while Innodb_buffer_pool_read_requests grows much faster, the pool is too small for the working set and you are reading from disk.

Step 3: Fix Connection Handling

Applications that open a new connection per request waste memory in per-connection buffers. Right-size these rather than raising limits blindly:

max_connections = 150
thread_cache_size = 32
wait_timeout = 300
interactive_timeout = 300

Each connection can allocate up to sort_buffer_size + read_buffer_size + join_buffer_size, so those per-session buffers multiply by connection count. Keep them small (128 KB–1 MB) and let the buffer pool do the heavy lifting. If you are hitting connection limits, use a connection pooler instead of inflating max_connections.

Step 4: Tame Temporary Tables

MariaDB spills sorts and aggregates to disk when they exceed the in-memory limit. On a VPS with slow shared storage, that shows up as latency spikes:

tmp_table_size = 64M
max_heap_table_size = 64M
tmpdir = /dev/shm

Keep tmp_table_size and max_heap_table_size equal — MariaDB uses the smaller of the two. Setting tmpdir to a tmpfs keeps temporary tables in RAM; size it so it can never consume more than a quarter of your memory. Monitor with SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables' — if this grows steadily, find the offending queries with the slow query log rather than raising limits forever.

Step 5: Enable the Slow Query Log and Find the Real Problems

Config tuning cannot fix a missing index. Turn on the slow log at a low threshold for a day:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0

Then rank offenders:

sudo mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

Add indexes for the top offenders, re-run EXPLAIN on each query, and only then re-benchmark. A single missing composite index routinely beats every config change on this page.

Step 6: Re-Measure and Verify

Restart MariaDB, warm the pool by running the read workload once, then repeat the sysbench run:

sudo systemctl restart mariadb
sudo sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-user=root --mysql-db=bench \
  --table-size=100000 --tables=4 --threads=4 --time=60 run | tee after.txt

Compare TPS and 95th-percentile latency against baseline.txt. Expect a 20–60% throughput gain on undersized-RAM workloads. If numbers regressed, revert individual settings one at a time — tuning is empirical, and the config that wins on one workload loses on another.

Choosing a VPS That Can Actually Run a Database

None of the above matters if your VPS is oversold on CPU or has noisy-neighbour storage jitter. Before you tune, you need hardware whose baseline is decent: dedicated vCPU allocation, NVMe storage, and a provider that is honest about steal time. You can compare the plans we have tested in our VPS comparison table, and check the FAQ section at the bottom of that page for how to read steal time and IOPS guarantees before you buy.

For the memory-limited case, pair this article with our guidance on sizing swap correctly so MariaDB has headroom when the pool is cold.