Tune MariaDB on a VPS using real workload data

Most MariaDB tuning guides paste a giant configuration file and call it optimisation. The safer approach is to measure, change one pressure point, and keep enough memory for Linux and the application.

Establish a baseline

Collect these during normal and peak traffic:

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW ENGINE INNODB STATUS;

On Linux, watch free -h, vmstat 1, and iostat -xz 1. Tuning the database will not fix an unindexed query or a disk already at 100% utilisation.

Size the InnoDB buffer pool

For a dedicated database VPS, start around 60% of RAM. When the web application shares the host, start closer to 35–45%.

On an 8 GB combined app/database VPS:

[mariadb]
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 3

Do not allocate all free memory. Connections, sorts, temporary tables, backups, and the kernel page cache need room.

Control connection cost

Every connection can allocate per-thread buffers. A huge max_connections creates the possibility of a huge memory spike.

max_connections = 120
thread_cache_size = 32
wait_timeout = 300

Use application connection pooling and set the limit slightly above observed peak usage. If Max_used_connections approaches the ceiling, determine why before increasing it.

Find slow queries

slow_query_log = 1
long_query_time = 0.5
log_queries_not_using_indexes = 0

Analyse the log with mariadb-dumpslow or another query-analysis tool. Use EXPLAIN on repeated expensive queries, add justified indexes, and compare execution plans.

Watch temporary tables

Queries that spill temporary tables to disk are slower, but raising limits globally can multiply memory use:

tmp_table_size = 64M
max_heap_table_size = 64M

Both values cap the effective in-memory size. Increase them only if Created_tmp_disk_tables is high and query plans cannot be improved.

Preserve durability

For transactional data, keep:

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Relaxing these can improve benchmarks while risking recent committed transactions during a crash. Make that trade explicitly, not accidentally.

Apply changes in a file under /etc/mysql/mariadb.conf.d/, validate with mariadbd --validate-config when supported, and restart during a maintenance window. Compare the same workload before and after. A change without a measured outcome is just a new default.

Make the change reversible

Copy the active configuration before editing and record the values you changed:

sudo cp -a /etc/mysql/mariadb.conf.d/50-server.cnf /root/50-server.cnf.before-tuning
sudo mariadbd --validate-config
sudo systemctl restart mariadb
sudo systemctl is-active mariadb

Immediately run a login, a representative read, a representative write, and the application's health check. Keep the previous file until the workload has completed a normal peak period.

Add indexes safely

Use EXPLAIN first and check index size against available disk. On a large table, an online or concurrent method may be preferable to a blocking migration; read the MariaDB version's exact ALTER behaviour before choosing flags. Schedule schema changes separately from memory tuning so a slower query plan cannot be blamed on the wrong change.

Back up before structural changes, monitor replication lag if replicas exist, and set an explicit slow-query log retention policy. The best tuning result is usually one removed query or one justified index, not a larger collection of global buffers.