Tune MySQL and MariaDB memory settings for a small VPS

A default MySQL or MariaDB install is sized for a machine far larger than a small VPS. It will happily reserve more memory than you have, push the box into swap, and make every query slow. In this guide you will measure what the database is using, set the handful of values that actually matter, and restart safely.

The goal is simple: the database should fit in RAM with room left for the web server, PHP and the operating system.

Before you start

You need:

• A Linux VPS running Ubuntu 22.04/24.04, Debian 12, AlmaLinux or Rocky 8–9.
• Root or sudo access, and a working SSH session. If you are new to this, see Connect to your Linux VPS with SSH.
• MySQL or MariaDB already installed and running. If not, use Install MySQL 8.4 on Ubuntu and Debian or Install MariaDB on AlmaLinux and Rocky.
• A rough idea of what else runs on the box. A database-only VPS can use far more RAM for the buffer pool than one also running Nginx, PHP-FPM and a panel.

Find the config file and current values

Config file locations differ by distribution and by whether you run MySQL or MariaDB. The full list is in Where is my.cnf?. Common paths:

• Ubuntu/Debian MySQL: /etc/mysql/mysql.conf.d/mysqld.cnf
• Ubuntu/Debian MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf
• AlmaLinux/Rocky: /etc/my.cnf.d/mysql-server.cnf or /etc/my.cnf.d/mariadb-server.cnf

Check what the server is actually using right now:

mysql -u root -p -e "SHOW VARIABLES WHERE Variable_name IN
('innodb_buffer_pool_size','max_connections','innodb_log_file_size',
'innodb_redo_log_capacity','tmp_table_size','max_heap_table_size',
'sort_buffer_size','join_buffer_size','performance_schema');"

Then look at the machine itself:

free -m

Note the total and available columns. If available is small and swap is already in use, the database is oversized for this VPS.

Set innodb_buffer_pool_size

This is the single most important value. InnoDB caches table and index pages here, and reads served from this pool never touch disk. Too small and every query hits the disk; too large and the kernel starts killing processes.

A practical starting point, assuming the VPS is mostly database:

• 1 GB RAM: 256M
• 2 GB RAM: 512M
• 4 GB RAM: 1G to 1.5G
• 8 GB RAM: 3G to 4G

If the same VPS also runs a web stack, use the lower end of the range or roughly 25–40% of RAM. Add the setting under the [mysqld] section:

[mysqld]
innodb_buffer_pool_size = 512M

The buffer pool is allocated at startup, so the value must be a size the server can claim immediately.

Tune max_connections and per-connection buffers

Each connection costs memory. The base thread overhead is small, but per-query buffers such as sort_buffer_size, join_buffer_size and read_buffer_size are allocated as needed, and a busy server with large values multiplies that across many connections.

Start with:

[mysqld]
max_connections = 75
sort_buffer_size = 512K
join_buffer_size = 512K
read_buffer_size = 256K
tmp_table_size = 32M
max_heap_table_size = 32M

Keep tmp_table_size and max_heap_table_size equal, otherwise the smaller one wins. Raising them lets more temporary work stay in memory instead of spilling to disk, but every concurrent query can use that much, so do not set them to hundreds of megabytes on a small VPS.

If your application reports "Too many connections", raise max_connections in steps and watch memory with free -m. Persistent connections from PHP-FPM can hold slots open; see Tune PHP-FPM pools if that is your stack.

Other values worth changing

Redo log. A larger redo log reduces disk writes during heavy inserts. On MySQL 8.0.30 and newer the setting is innodb_redo_log_capacity; on older MySQL and on MariaDB it is innodb_log_file_size. Sizes and behaviour have changed across versions, so check the vendor documentation for your release before setting it.

[mysqld]
innodb_flush_method = O_DIRECT

O_DIRECT avoids double buffering on Linux and is the usual choice for InnoDB.

Performance schema. It is useful for diagnostics but costs memory. On a very small VPS you can disable it:

[mysqld]
performance_schema = OFF

You lose query instrumentation, so turn it back on if you need to profile something.

Swap behaviour. If the VPS has no swap, add a small swap file so the kernel has somewhere to go under pressure. Then lower the tendency to swap:

sudo sysctl -w vm.swappiness=10

Make it permanent in /etc/sysctl.d/99-swappiness.conf.

Apply the changes

Validate the file syntax before restarting where your version supports it:

sudo mysqld --validate-config

Then restart:

sudo systemctl restart mysql

Use mariadb instead of mysql on MariaDB hosts. If the service fails to start, read the last lines of the error log immediately:

sudo journalctl -u mysql -n 50 --no-pager

Check it works

Confirm the service is up and the values took effect:

systemctl status mysql --no-pager
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Watch memory for a few minutes under normal traffic:

free -m

The available figure should stay comfortably above zero and swap use should not climb. Then check how well the buffer pool is serving reads:

mysql -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN
('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads','Threads_connected');"

Divide Innodb_buffer_pool_reads by Innodb_buffer_pool_read_requests. A very small ratio means almost everything is served from memory, which is what you want. For a broader view of the box, see Monitor CPU, memory, disk and logs.

Troubleshooting

• Service will not start after the edit. Usually a typo or a value larger than available RAM. Check journalctl -u mysql -n 50, fix the line, and restart. Keep a copy of the original file so you can revert in one move.
• The database process is killed by the OOM killer. The buffer pool plus other services exceed RAM. Lower innodb_buffer_pool_size by 25% and restart, then watch free -m again.
• "Too many connections" during traffic peaks. Raise max_connections gradually, but confirm you have memory headroom first. Fixing leaked connections in the application is usually the better answer.
• Settings seem to have no effect. You edited the wrong file, or a later file overrides it. Check the include order described in Where is my.cnf? and confirm with SHOW VARIABLES.
• Queries got slower after tuning. The buffer pool may now be too small for your working set. Compare table sizes with Check database and table sizes and size the pool against the data you actually query.

Next steps

• Where is my.cnf? MySQL and MariaDB config files explained
• How to check database and table sizes in MySQL and MariaDB
• Tune PHP-FPM pools (pm.max_children) for your RAM
• Monitor CPU, memory, disk and logs on a Linux server

If the database keeps outgrowing the box, a larger Linux VPS with more RAM is cheaper than fighting swap.