Reducing MySQL RAM on a small VPS: buffer and connection tuning

A common situation on a small Cloud VPS database server: the dashboard shows RAM sitting at an alarming level day after day. The machine has not crashed and the websites still work, but with 1 GB of RAM and no swap, a single load spike is enough for the kernel to kill mysqld. This article is a process for dealing with high MySQL RAM usage on a small VPS: measure where the memory goes, rule out wrong leads, understand the fixed memory areas of MySQL 8.0, and tune each parameter to the real resources instead of keeping the defaults.

Symptoms

• A machine with 1 vCPU, about 1 GB of RAM and no swap, running MySQL 8.0 with an almost default configuration.
• RAM usage above 90%, with only a few dozen MB of available RAM left.
• mysqld takes most of the RAM, even though the data on disk is smaller than that.
• No errors, no slow sites. MySQL has been running continuously for months without a restart.

This is a "not yet happened" kind of incident. A machine of the same RAM size with no swap will sooner or later end up like the one in nginx OOM on a balancer: when one small machine carries many websites, except that the process killed is the database.

Investigating MySQL RAM

Step 1: MySQL RAM or another process

free -h -w
ps aux --sort=-%mem | head -10
ss -nt 'sport = :3306'
vmstat 1 3

Compare the RSS of mysqld with other processes (journald, exporters), the kernel's share (Slab, PageTables) and the page cache the operating system can reclaim. If mysqld takes the largest share, the next question is what is inside the process.

Step 2: look at MySQL RAM at the process level

PID=$(pidof mysqld)
cat /proc/$PID/smaps_rollup
grep -E 'VmPeak|VmRSS|VmSwap|VmData|RssAnon|RssFile' /proc/$PID/status
du -sh /var/lib/mysql/*/

If the total data on disk is much smaller than the RSS, most of the MySQL RAM is not cached data but fixed memory areas and accumulated heap.

Step 3: compare the MySQL RAM configuration with the defaults

[mysqld]
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log
max_connections = 400
skip-log-bin

A file like this leaves almost everything at the MySQL 8.0 defaults. According to the MySQL documentation on how MySQL uses memory, the main components are:

Read the table correctly: per-connection buffers are only allocated when there is a connection and when a statement needs them, so that figure is a ceiling, not current usage. A high max_connections hurts in a different way: Performance Schema sizes many internal tables based on the maximum number of connections and open tables; see the Performance Schema memory allocation model.

Step 4: rule out hung queries or a memory leak

Many people's first hypothesis when they see high MySQL RAM is "a heavy query is running" or "MySQL is leaking memory". Take a few snapshots a few seconds apart:

for i in 1 2 3; do
  ss -nt 'sport = :3306' | tail -n +2 | wc -l
  grep VmRSS /proc/$PID/status
  cat /proc/$PID/task/*/status | grep '^State' | sort | uniq -c
  sleep 3
done

If there are only one or two connections, every thread is sleeping and the RSS does not change between snapshots, then there is no hung query and RAM is not growing with queries. The hypothesis is ruled out within seconds. You can cross-check with the numbers MySQL reports itself:

SELECT event_name, FORMAT_BYTES(current_number_of_bytes_used) AS used
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY current_number_of_bytes_used DESC LIMIT 10;

Root cause

High MySQL RAM on a small machine is usually the sum of the following factors:

• The default configuration assumes a bigger machine. MySQL 8.0 defaults suit machines with several GB of RAM.
• Performance Schema is enabled and sized for a large max_connections, while in practice there are only a few connections.
• table_open_cache is far too large compared with the real number of tables.
• No swap, so there is no buffer when RAM fills up.
• Running for a very long time without a restart. The glibc allocator does not necessarily return freed heap to the operating system, so RSS reflects an old peak rather than current demand. This is a hypothesis; a clear drop in RSS right after a restart is a sign that supports it.

Fixing high MySQL RAM

Add missing indexes through migrations

If the processlist or slow log shows full table scans, add indexes through the project's migrations, not by hand on the server. Indexes reduce I/O and CPU; the RAM drop seen right after a restart mostly comes from clearing the accumulated heap, not from the indexes.

Add swap as a safety net

fallocate -l 512M /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
echo '/swapfile none swap sw 0 0' >> /etc/fstab
echo 'vm.swappiness=10' > /etc/sysctl.d/99-swappiness.conf
sysctl --system

vm.swappiness=10 makes the kernel push pages to swap only when RAM is genuinely short. Swap here is a net against OOM, not an extension of MySQL RAM for day-to-day work.

Tune mysqld.cnf to reduce MySQL RAM

# /etc/mysql/mysql.conf.d/mysqld.cnf: example for a 1 GB machine with a few hundred tables
max_connections = 100
table_open_cache = 1000
table_definition_cache = 400
innodb_buffer_pool_size = 128M
tmp_table_size = 12M
max_heap_table_size = 12M
thread_cache_size = 12

Before restarting, check the configuration with mysqld --validate-config. One syntax error on a machine with a single database stops the whole application. Also check that no parameter is declared twice: MySQL uses the last value read, but two conflicting lines are a trap for whoever edits the file next.

grep -rn 'max_connections' /etc/mysql/
mysqld --validate-config && systemctl restart mysql

Measure again after each step

Record used RAM, available RAM and the RSS of mysqld (the real MySQL RAM) before and after each change, then watch the dashboard for a few weeks to see whether RAM creeps back up. Numbers taken right after a restart always look better than reality.

Preventing high MySQL RAM

• To keep MySQL RAM in check on small machines, every Cloud VPS database server with less than 2 GB of RAM should have swap and a low vm.swappiness.
• Set max_connections from the real connection count: SHOW GLOBAL STATUS LIKE 'Max_used_connections' after a few weeks of running.
• Set table_open_cache and table_definition_cache from SELECT COUNT(*) FROM information_schema.tables.
• Compare the memory MySQL reports itself with the process RSS. A large gap is a sign of accumulated heap.
• Alert on available RAM, not only on the percentage used: many dashboards count page cache as "used" as well.
• Limit journald (SystemMaxUse=50M) on machines with small disks and little RAM.
• If MySQL RAM still grows back, prefer adding RAM to the machine over disabling Performance Schema, since it is a diagnostic tool you need for query incidents.

Lessons

• High MySQL RAM is not necessarily a query problem. A few snapshots with every thread sleeping are enough to rule out hung queries within seconds.
• Defaults are not neutral. As in The Database tier: multi-tenant MySQL on small VPS instances, every parameter needs to be calculated from the real RAM.
• A restart is a painkiller. It gives back some RAM, but without the right configuration MySQL RAM will creep back up over time.
• Measure first, change, then measure again. Only with before and after numbers for each step can you tell which step actually helped.

Next article

MySQL 8.4 picks the wrong plan for a <> condition: splitting the query in two. When a query still hangs for a very long time even with an index in place, and why a tiny "not equal" condition is enough to send the optimizer down the wrong path.