Cutting MySQL Memory on a Small VPS: A Verified, Repeatable Procedure

A MySQL or MariaDB server that swaps, gets OOM-killed, or refuses new connections is almost always misconfigured for the memory it has — not short of memory. On a 1 GB or 2 GB VPS you cannot give the database “enough” RAM, so you have to give it the right RAM. This procedure walks through where MySQL’s memory actually goes, which settings to cut, and how to verify the result instead of hoping.

1. Establish a baseline before touching anything

You cannot prove an improvement without a before-and-after. Record the process RSS and the kernel’s assessment of available memory under your normal workload:

ps -o rss= -C mysqld | awk '{printf "mysqld RSS: %.0f MB\n", $1/1024}'
free -m
cat /proc/meminfo | grep -E 'MemAvailable|SwapCached'

MemAvailable is the honest number — not free. If it collapses toward zero while your server runs, you have a real problem to solve rather than a cosmetic one.

2. Find the largest consumer: the InnoDB buffer pool

On almost every small install this is the biggest line item, and it is frequently sized by copy-paste. Check whether it is actually being used:

mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';"
mysql -e "SELECT @@innodb_buffer_pool_size/1024/1024 AS pool_mb;"

Divide pages_data by pages_total. That ratio is your real fill level. If it is under half on a read-heavy workload, shrink the pool to match the working set — you reclaim RAM for free. A common safe starting point on 1 GB is 256 MB; on 2 GB, 512 MB. Grow only when the fill ratio stays high under load.

3. Reclaim per-connection buffers

These settings are harmless in isolation but multiply by connection count. Defaults like a 256 KB sort_buffer_size and 128 KB join_buffer_size are generous for small applications and can be cut without user-visible impact:

[mysqld]
max_connections        = 40
sort_buffer_size       = 256K
join_buffer_size       = 128K
read_buffer_size       = 128K
read_rnd_buffer_size    = 128K
tmp_table_size         = 16M
max_heap_table_size    = 16M

Do not set max_connections to a fantasy number you will never reach. Use your app’s real pool size plus headroom. A server that allows 500 connections but only ever sees 20 pays planning overhead for connections that never exist.

4. Disable what you do not query

The performance schema is a diagnostics engine, and it is expensive on tiny hosts. If you are not actively querying it, turn it off in the config and it stops consuming memory entirely:

[mysqld]
performance_schema = OFF

The same reasoning applies to the query cache on MariaDB, which is disabled by default in modern versions for good reason, and to unused storage engines you have compiled in but never instantiate.

5. Make pressure visible instead of fatal

Even a perfect budget can be exceeded by a runaway query. Tune the kernel so rising memory pressure shows up as swap activity you can observe, and cap the database’s own footprint so it cannot consume the whole host:

sysctl vm.swappiness=10
sysctl vm.overcommit_memory=0

For a hard ceiling, use a systemd slice or cgroup v2 MemoryMax on the MySQL unit. This is the difference between a database that degrades gracefully and one that takes your SSH session down with it. Hosting the database on a properly-sized virtual server with predictable memory accounting makes these guardrails far easier to reason about.

6. Verify the change

Restart MySQL, replay the same workload you baselined, and re-measure the exact commands from step 1. Then confirm the buffers are actually at their new values, because a typo in the config block silently leaves the old default in place:

mysql -e "SELECT @@sort_buffer_size, @@join_buffer_size, @@innodb_buffer_pool_size;"
ps -o rss= -C mysqld | awk '{printf "new mysqld RSS: %.0f MB\n", $1/1024}'

The difference between the old RSS and the new one is your reclaimed memory. Keep the config in version control alongside the measurements, so the next administrator inherits a documented budget rather than a mystery. A box that stays inside its memory envelope is exactly the kind of workload premium cloud solutions are built to run reliably — but only after someone does the arithmetic.