Tune MySQL and MariaDB performance on a VPS

Default MySQL performance settings are written for tiny machines. On a VPS a few changes, mainly the InnoDB buffer pool, plus fixing the slowest queries, usually give the biggest speed-up. This guide works for MySQL 8 and MariaDB 10.6+.

Before you start

• Root SSH access and a recent database backup.
• Know your RAM (free -h) and what else runs on the server (web server, PHP-FPM, Redis).
• Find the config file: usually /etc/mysql/my.cnf plus /etc/mysql/mariadb.conf.d/ on Debian/Ubuntu, or /etc/my.cnf.d/ on AlmaLinux/Rocky. Put your changes in a new file such as /etc/mysql/conf.d/tuning.cnf under a [mysqld] section.

Size the InnoDB buffer pool

The buffer pool caches table data and indexes in RAM. It is the single most important setting.

• Dedicated database server: 60–70% of RAM.
• Web and database on the same VPS: 25–40% of RAM, leaving room for PHP and the OS.
• No point making it much larger than your total data plus indexes.

Check the data size:

SELECT ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables WHERE engine='InnoDB';

Example for a 4 GB VPS that also runs the website:

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
max_connections = 100
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 2000
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

On MySQL 8.0.30+ use innodb_redo_log_capacity = 512M instead of innodb_log_file_size.

Understand the key settings

Restart and confirm:

systemctl restart mysql || systemctl restart mariadb

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

Find slow queries

After a day of traffic, summarise the slow log:

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Take the worst query and run it with EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

If type is ALL and rows is large, MySQL scans the whole table. An index on the filtered and sorted columns usually fixes it:

ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

Check the result

• Buffer pool hit rate: in SHOW ENGINE INNODB STATUS\G look for a hit rate close to 1000/1000.
• Run mysqltuner (apt install mysqltuner) after 24 hours of uptime for further hints. Treat its suggestions as hints, not rules.
• Watch memory with free -h; the server should not start swapping.

Troubleshooting

• MySQL will not start after the change: read journalctl -u mysql or the error log; a typo or a value that needs more RAM than available is the usual cause.
• Too many connections: find who holds them with SHOW PROCESSLIST; often the fix is fewer PHP workers or closing idle connections, not a higher limit.
• OOM killer stops mysqld: lower the buffer pool and max_connections, or add swap as a safety net.

MySQL tuning FAQ

What is the most important MySQL setting?

innodb_buffer_pool_size. Get that right, then fix slow queries with indexes; most other settings have small effects.

Should I switch from MySQL to MariaDB for speed?

Rarely worth it on its own. Configuration and queries matter far more than the fork.

Can I tune MySQL on shared cPanel hosting?

No, server settings are managed by the host. You can still add indexes and optimise tables through phpMyAdmin.

Next steps

• Back up MySQL with mysqldump
• Optimise and repair tables in phpMyAdmin
• Troubleshoot high CPU and RAM usage