How to Optimize MySQL & MariaDB Performance on Cloud VPS (2026)

Why Default MySQL Configurations Are Inefficient

Default MySQL and MariaDB installations are packaged with conservative memory thresholds designed to boot safely on tiny 512MB RAM machines. If you run a high-RAM VPS on Contabo (such as 8GB, 16GB, or 32GB) or a Hostinger KVM VPS, leaving the default innodb_buffer_pool_size at 128MB wastes over 90% of your available hardware resources.

Diagnosing Bottlenecks with MySQLTuner

Before modifying any configuration files, install MySQLTuner, a battle-tested Perl diagnostic script that analyzes your running database metrics:

sudo apt update && sudo apt install -y mysqltuner
sudo mysqltuner

Review the key metrics:

• InnoDB buffer pool hit rate: Should be 99%+ (anything less indicates disk swapping).
• Temporary tables created on disk: Should be under 15% of total temp tables.
• Slow queries: Must be logged and indexed.

Step 1: Sizing the InnoDB Buffer Pool

The innodb_buffer_pool_size is the single most critical performance directive in MySQL. It caches database tables, indexes, and active transactions directly in memory.

• Dedicated DB Server: Set to 70% - 75% of total system RAM.
• Shared Web/DB Server (WordPress + Nginx): Set to 50% - 60% of system RAM.

Step 2: Creating a Production MySQL Configuration File

Create a custom override file in /etc/mysql/conf.d/99-performance.cnf:

sudo nano /etc/mysql/conf.d/99-performance.cnf

For an 8GB RAM VPS, apply these optimized parameters:

[mysqld]
# Memory & Buffer Pool Tuning (4GB allocated to DB)
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Connection and Thread Tuning
max_connections = 150
thread_cache_size = 50
table_open_cache = 4000
open_files_limit = 65535

# Temporary Tables and Joins
tmp_table_size = 64M
max_heap_table_size = 64M
join_buffer_size = 4M
sort_buffer_size = 4M

# Slow Query Logging for Ongoing Profiling
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1.5

Step 3: Understanding innodb_flush_log_at_trx_commit = 2

By default, value 1 forces an expensive disk sync on every single SQL commit. Setting this to 2 writes the log buffer to OS cache every transaction and only flushes to physical disk once per second. On high-traffic eCommerce stores and forums, this single modification cuts disk write I/O by up to 80% while retaining ACID durability against everything except sudden host hardware power loss.

Step 4: Restarting and Validating

Restart the MySQL service and verify memory allocation:

sudo systemctl restart mysql
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Monitor your query latency in the slow query log to identify unindexed database tables:

sudo tail -f /var/log/mysql/mysql-slow.log

Frequently Asked Questions (FAQ)

How do I know if my database buffer pool is saturated?

Execute: mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';". If Innodb_buffer_pool_reads (reads that had to fetch from disk) is very high compared to Innodb_buffer_pool_read_requests, increase your buffer pool size.

Should I use MariaDB or MySQL 8 on Ubuntu?

MariaDB 10.11+ offers higher multi-threaded query execution and native Aria storage engine support, while MySQL 8.4 is the enterprise standard with superior JSON document manipulation. Both perform exceptionally well when tuned properly.