How to Optimize MariaDB on Your VPS for Better Query Performance

MariaDB is one of the most popular database servers for VPS-hosted applications, from WordPress sites to custom web apps. But default MariaDB configurations are conservative — optimized for a wide range of hardware rather than your specific workload. With a few targeted adjustments, you can dramatically improve query throughput, reduce latency, and make better use of your VPS’s available memory. For VPS plans with enough RAM to handle a database workload, check out the provider comparison table.

Before You Start: Benchmark Your Baseline

Always measure before and after. Install sysbench to run a standard OLTP benchmark:

sudo apt install -y sysbench
# Prepare test data
sysbench /usr/share/sysbench/oltp_read_write.lua --table-size=100000 prepare
# Run benchmark
sysbench /usr/share/sysbench/oltp_read_write.lua --table-size=100000 --threads=4 run

Record the transactions per second (TPS) and latency numbers. You’ll compare these after tuning.

1. InnoDB Buffer Pool — The Most Important Setting

The InnoDB buffer pool caches table data and indexes in memory. This is the single most impactful setting for MariaDB performance. Set it to 50-70% of your VPS’s total RAM (but leave enough for the OS and other processes):

# For a VPS with 4 GB RAM — set buffer pool to 2 GB
[mysqld]
innodb_buffer_pool_size = 2G

On servers with 8 GB or more, consider using multiple buffer pool instances to reduce contention:

innodb_buffer_pool_instances = 4

Set innodb_buffer_pool_instances to the same number of CPU cores for best results.

2. Query Cache — Use It Carefully

The query cache was removed in MySQL 8.0 but is still available in MariaDB. For read-heavy workloads with repetitive queries (like WordPress), it can help:

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

For write-heavy workloads, leave the query cache disabled (query_cache_type = 0) — the invalidation overhead can actually slow things down.

3. Log File and Redo Log Tuning

The InnoDB log file size affects write performance during crash recovery and checkpoint operations. A larger log file means fewer checkpoints and better write performance:

innodb_log_file_size = 512M
innodb_log_buffer_size = 64M

Note: Changing innodb_log_file_size requires shutting down MariaDB, removing old log files, and restarting. Plan for brief downtime.

4. Connection and Thread Settings

Default connection limits are low. For a web application behind a connection pooler, increase these:

max_connections = 150
thread_cache_size = 8
# Thread pool (MariaDB specific) — great for high concurrency
thread_handling = pool-of-threads
thread_pool_size = 4  # Set to number of CPU cores

5. Temporary Table and Sort Buffer Tuning

Queries involving GROUP BY, ORDER BY, or DISTINCT use temporary tables and sort buffers. If your application runs many such queries, increase these:

tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M

Be careful with sort_buffer_size and join_buffer_size — these are per-connection buffers, so setting them too high can exhaust memory under heavy concurrency.

6. Complete Configuration Example

Here’s a complete /etc/mysql/mariadb.conf.d/99-vps-tuning.cnf for a 4 GB VPS running a read-heavy web application:

[mysqld]
# InnoDB
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
# Query cache
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M
# Connections
max_connections = 150
thread_cache_size = 8
thread_handling = pool-of-threads
thread_pool_size = 4
# Temp tables
tmp_table_size = 64M
max_heap_table_size = 64M
# Buffers
sort_buffer_size = 2M
join_buffer_size = 2M
# Other
max_allowed_packet = 64M
expire_logs_days = 7

7. Apply and Verify

Restart MariaDB and run the benchmark again:

sudo systemctl restart mariadb
sysbench /usr/share/sysbench/oltp_read_write.lua --table-size=100000 --threads=4 run

Compare the new TPS and latency numbers against your baseline. With proper tuning, you should see 2-5x improvement in query throughput, especially for workloads that fit within the buffer pool.

8. Ongoing Monitoring

Use the built-in SHOW STATUS and SHOW VARIABLES commands to monitor MariaDB health. Key metrics to track:

• Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads — hit ratio should be > 99%
• Qcache_hits vs Qcache_inserts — query cache effectiveness
• Threads_connected vs max_connections — headroom
• Slow_queries — anything above zero is worth investigating

Database performance depends heavily on the underlying hardware. Choosing a VPS provider with fast NVMe SSD storage and sufficient RAM is critical. Compare VPS hosting plans to find a configuration that matches your database workload requirements.