Tune MariaDB for a small VPS

MariaDB's defaults are sized for modest machines, but a small VPS can still run out of memory when a busy site or app gets going. This guide adjusts three settings that matter most for a server with 1 to 4 GB of RAM, verifies them, and shows how to roll back if something goes wrong.

Before you begin

An Ubuntu 24.04 VPS with 1 to 4 GB of RAM and MariaDB installed and running.
SSH access as a sudo user, such as alex.
A fresh backup of your databases. The backup guide covers this. Tuning is low risk, but a backup costs nothing.
A rough idea of what else runs on the server, such as a web server or PHP. Those programs need memory too.

Step 1: Measure memory and check current settings

Start by seeing how much memory is free. The available column is the one that matters, because it counts memory the system can reclaim from caches.

free -h
sudo mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
sudo mariadb -e "SHOW VARIABLES LIKE 'max_connections';"

               total        used        free      shared  buff/cache   available
Mem:           1.9Gi       612Mi       208Mi        12Mi       1.2Gi       1.3Gi
Swap:          2.0Gi          0B       2.0Gi

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

The default buffer pool is 128 MB, which is a safe starting point. Values are in bytes, so 134217728 is 128 MB.

Step 2: Choose values for your memory size

The InnoDB buffer pool caches table and index data, and it is the biggest memory setting. On a shared server, keep it modest so the web server and application have room. max_connections caps simultaneous clients, and each connection uses memory. key_buffer_size serves older MyISAM tables; a small value is enough if you use mostly InnoDB.

1 GB RAM: innodb_buffer_pool_size = 128M, max_connections = 30
2 GB RAM: innodb_buffer_pool_size = 256M, max_connections = 50
4 GB RAM: innodb_buffer_pool_size = 768M, max_connections = 100

The examples below use the 2 GB values.

Step 3: Back up the config and edit it

Save a copy of the configuration file first, so you can undo your change. Then open the file in nano.

mkdir -p ~/backups
sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf ~/backups/50-server.cnf.bak
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find the [mysqld] section and add or change these lines. Keep the other settings as they are.

innodb_buffer_pool_size = 256M
max_connections         = 50
key_buffer_size         = 16M

Save with Ctrl+O, Enter, then exit with Ctrl+X.

Step 4: Restart and verify

Restart MariaDB so it reads the file. Then confirm the new buffer pool size in bytes. 256 MB is 268435456 bytes.

sudo systemctl restart mariadb
systemctl is-active mariadb
sudo mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

active
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

Step 5: Watch peak connections and memory

Let the server run through a normal day, then check the highest number of connections it has seen. If the peak sits close to max_connections, raise the limit carefully or fix the application that holds connections open.

sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections';"
free -h

Step 6: Check for swapping

Swapping means the system is moving memory to disk, which makes everything slow. The si and so columns show data swapped in and out per second. Sustained non-zero values mean the server is short of memory.

vmstat 5 3

procs -----------memory---------- ---swap-- -----io----
 r  b   swpd   free   buff  cache   si   so    bi    bo
 0  0      0 208412  12044 1228800    0    0     3    21

Careful: Do not set the buffer pool above about half of the server's RAM when a web application shares the machine. If the kernel runs out of memory, it stops processes, and MariaDB is a likely victim.

Troubleshooting

MariaDB will not start after editing: run sudo journalctl -u mariadb -n 30 --no-pager to find the bad line, or restore the backup with sudo cp ~/backups/50-server.cnf.bak /etc/mysql/mariadb.conf.d/50-server.cnf and restart.
"Too many connections" errors: raise max_connections a little, or fix the application so it closes connections it no longer needs.
Your value does not change: another file may override it. List the extra folders with ls /etc/mysql/conf.d /etc/mysql/mariadb.conf.d and check for a conflicting line.
Processes disappear without errors: check for out-of-memory kills with sudo journalctl -k | grep -i "out of memory", then lower the buffer pool.

Next steps

Keep watching memory for a week after each change, and revisit the numbers when your traffic grows. The memory and disk monitoring guide shows how to see what else is using RAM on the same server.