How to tune MySQL for a low-RAM VPS

You just moved a small web app to a 1GB or 2GB VPS and MySQL is already eating half the RAM before Nginx or PHP even starts. I have hit this wall more than once: default MySQL settings are tuned for a dedicated database box, not for a $5 VPS that also runs your application. This guide walks you through tuning MySQL for a low-RAM VPS on Ubuntu 24.04 with MySQL 8.4 LTS, the current supported release. Every change is concrete, tested, and comes with a verify step so you can confirm the effect before you move on.

Prerequisites

• An Ubuntu 24.04 VPS with at least 1GB of RAM. The same values work on Debian 12 and AlmaLinux 9 with minor package-name differences.
• MySQL 8.4 LTS already installed (mysql-server). If you are on MySQL 8.0, upgrade: it reached end-of-life in April 2026 and no longer receives security patches.
• Root or sudo access.
• About 15 minutes. You will restart MySQL once at the end.

If you are starting from scratch, a Linux VPS with 2GB of RAM and a modern NVMe disk is enough for most small production databases, as long as you apply the tuning below.

Why default MySQL settings break on small servers

The stock my.cnf assumes hardware you do not have. Two settings dominate memory use. First, the InnoDB buffer pool defaults to 128MB on most distributions, which is actually reasonable for small servers, but the real problem is a long tail of per-connection buffers. Every connection can allocate its own sort_buffer_size, join_buffer_size, read_buffer_size, and read_rnd_buffer_size. With 100 connections and 256KB per buffer per connection, that is 100MB of RAM just for sorting and joining, before a single query runs.

The second hidden cost is performance_schema. It is enabled by default in MySQL 8.x and can consume 200MB to 400MB on a busy server, mostly for instrumented events you will never look at. On a 1GB box, that is the difference between a comfortable margin and constant swap thrashing.

Memory on a small VPS is a fixed budget. You need to cap the big consumers and shrink the per-connection defaults, then verify with real numbers instead of guessing.

Step 1 - Measure your current memory baseline

Before changing anything, record where you stand. This gives you a before/after comparison and tells you which setting matters most on your workload.

systemctl status mysql
free -h

Note the used figure and the swap line. If swap is already active before any load, you have a memory problem, not a tuning one.

Next, check what MySQL itself reports. The performance_schema memory allocation is visible in the global status:

mysql -e "SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE 'Performance_schema_memory%';"

On a default install you will often see several hundred megabytes. That single number justifies disabling it on a low-RAM VPS, which we do in Step 3.

mysql -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Write down those two values. They are the levers you will pull.

Step 2 - Set the InnoDB buffer pool correctly

The buffer pool is the biggest fixed consumer of RAM in MySQL. It holds cached data pages and indexes, and getting it wrong in either direction hurts. Too large, and the operating system starts swapping, which is far worse than a smaller cache. Too small, and every query hits disk.

For a low-RAM VPS that also runs an application, I set the buffer pool to 25-40% of total RAM. On a 1GB server that means 256M, on a 2GB server 512M. That leaves room for the web server, PHP-FPM, and the OS page cache. If MySQL is the only thing on the box, you can push toward 60-70%, but that is rare on a small VPS.

sudo mkdir -p /etc/mysql/mysql.conf.d
sudo tee /etc/mysql/mysql.conf.d/99-lowram.cnf > /dev/null <<'EOF'
[mysqld]
innodb_buffer_pool_size = 256M
innodb_buffer_pool_instances = 1
innodb_log_buffer_size = 8M
EOF

Why innodb_buffer_pool_instances = 1? Above 1GB, multiple instances reduce contention, but below 1GB a single instance avoids the per-instance overhead. It is a small win, but free.

innodb_log_buffer_size at 8M is plenty for a small server. Transactions that large are rare on a single-application VPS.

sudo mysql -e "SELECT @@innodb_buffer_pool_size/1024/1024 AS buffer_pool_mb;"

Expected output after restart: 256. If it still says 134217728 (128M), the file was not picked up, check the file name and location with mysqld --verbose --help | grep -A 1 'Default options'.

Step 3 - Cap connections and shrink per-session buffers

This is where most of the preventable memory waste lives. Default MySQL ships with max_connections = 151. On a 1GB VPS running a web app, you will never legitimately use that many, and each one reserves memory.

sudo tee -a /etc/mysql/mysql.conf.d/99-lowram.cnf > /dev/null <<'EOF'
max_connections = 50
sort_buffer_size = 512K
join_buffer_size = 512K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
performance_schema = OFF
EOF

Fifty connections is generous for a single small application. WordPress, a typical n8n instance, or a small API rarely needs more than 20-30 concurrent connections. Lowering this limit also protects you from a connection storm during a traffic spike: instead of the server exhausting RAM, new connections get rejected cleanly.

The four *_buffer_size values are the per-connection allocations I mentioned. Dropping them from the 256KB-1MB defaults to 512K reduces worst-case memory per connection significantly. The trade-off: very large sorts or joins spill to disk slightly earlier. On a low-RAM VPS, that is the right trade.

mysql -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -e "SHOW VARIABLES LIKE 'performance_schema';"

Expected: 50 and OFF after restart. Note that changing these requires a restart, which we do in Step 5.

Step 4 - Reduce MySQL memory with performance_schema disabled

Disabling performance_schema is the single biggest memory save on a small VPS. In MySQL 8.x it is on by default, and it can consume 200-400MB depending on version and activity. It is useful for deep performance analysis, but on a 1GB box it is a luxury you cannot afford.

The catch: with performance_schema = OFF, you lose access to performance_schema.* tables and the sys schema. You still have SHOW VARIABLES, SHOW STATUS, and the slow query log, which covers 95% of VPS troubleshooting. If you need sys schema diagnostics, you can re-enable it temporarily, capture what you need, and turn it back off.

低内存 VPS 上，关闭 performance_schema 能省下数百 MB 内存，这是最有效的 MySQL 优化手段。

On a low-RAM VPS, disabling performance_schema saves several hundred MB and is the single most effective MySQL optimization.

The setting is already in the file from Step 3. To confirm it took effect after restart:

mysql -e "SHOW VARIABLES LIKE 'performance_schema';"
mysql -e "SHOW STATUS LIKE 'Performance_schema_memory_used';"

The second command should return an empty set, which means the instrumentation is gone.

Step 5 - Add swap and restart MySQL

Swap is not a replacement for proper tuning, but it is a safety net. If memory usage spikes, swap keeps MySQL from being killed by the OOM killer. On a VPS with a fast NVMe disk, a small swap file is cheap insurance.

Ubuntu 24.04 uses a swap file by default (usually none on a minimal VPS). Create one if missing:

sudo swapon --show

If the output is empty, create a 1GB swap file:

sudo fallocate -l 1G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
echo '/swapfile none swap sw 0 0' | sudo tee -a /etc/fstab

Setting vm.swappiness to 10 keeps the kernel from swapping aggressively under normal load, while still using swap when needed:

echo 'vm.swappiness=10' | sudo tee /etc/sysctl.d/99-swappiness.conf
sudo sysctl -p /etc/sysctl.d/99-swappiness.conf

Now apply the MySQL changes, then verify the service is healthy:

sudo systemctl restart mysql
systemctl status mysql

Expected: active (running). Then check the memory impact:

free -h
ps aux | grep mysqld | grep -v grep

The RSS column in ps tells you the real resident memory of the MySQL process. On a 1GB VPS with this config, you should see it settle around 300-400MB, down from 700MB+ before tuning. The difference is the buffer pool plus the removed performance_schema overhead.

Troubleshooting

MySQL fails to start after the config change. Check the error log first, it is usually the quickest path:

sudo journalctl -u mysql --since "5 minutes ago"
sudo tail -n 50 /var/log/mysql/error.log

A common cause is a typo in the config file, or a setting that does not exist in MySQL 8.4. Run sudo mysqld --validate-config to test the file before restarting.

Connections are being rejected with "Too many connections". Your app genuinely needs more than 50. Raise max_connections to 75 or 100 and watch memory with free -h. If RAM jumps sharply, the per-connection buffers are the culprit, reconsider whether the app can use a connection pool (PHP-FPM's pm settings matter here more than MySQL).

Queries got slower after shrinking buffers. Expected in the worst case. The disk on a modern NVMe VPS handles the spill better than you think. If a specific query is slow, add an index instead of growing the buffers. performance_schema being off means you diagnose with EXPLAIN and the slow query log, which is exactly what I do in production.

Docker users. If you run MySQL in Docker on a low-RAM VPS, apply the same mysql flags or mount this config file into the container, and set a container memory limit so MySQL cannot consume the host. I cover the general pattern in the Docker Compose guide.

FAQ

Related articles

• Configure swap and optimize memory on a low-RAM VPS
• Set up a LEMP stack: Nginx, MariaDB, PHP 8.3 on Ubuntu 24.04
• How to install and configure Redis on a Linux VPS
• Optimize PHP-FPM for WordPress on a VPS

If you are tuning a fresh box, this runs cleanly on a 2GB RAM Linux VPS from thueVPS with NVMe storage and a dedicated Vietnam IPv4. The monthly billing and full root access make it easy to test these settings, reinstall the OS if you break something, and start over within minutes.