MySQL/MariaDB Optimization on VPS — Speed Up Your Database

MySQL and MariaDB are the most widely used databases on VPS servers, but their default configurations are designed for low-memory machines — not modern cloud VPS environments. This guide walks you through the most impactful tuning steps to maximize database performance.

Why Is MySQL Slow

Understanding the root cause before tuning is critical. Most out-of-the-box MySQL installations suffer from these common issues:

Tuning InnoDB Buffer Pool

The InnoDB buffer pool is the single most important MySQL tuning parameter. It caches table data and indexes in RAM, avoiding expensive disk reads. The larger it is, the better — up to the limit of available RAM.

Check current buffer pool usage:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_reads';"

Key my.cnf Settings

The main MySQL/MariaDB config file is at /etc/mysql/my.cnf or /etc/my.cnf depending on your distro. Here's a well-tuned starting configuration:

[mysqld]
# Buffer Pool — adjust based on available RAM
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2

# Log File — larger = fewer checkpoints
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# I/O Settings
innodb_flush_log_at_trx_commit = 2    # Reduce I/O (loses ~1s data on crash)
innodb_flush_method = O_DIRECT        # Avoid OS double buffering

# Connections
max_connections = 150                  # Reduce from default 151-200
thread_cache_size = 16

# Query Cache (MariaDB only — removed in MySQL 8)
# query_cache_type = 1
# query_cache_size = 64M
# query_cache_limit = 2M

# Tmp Tables — keep in RAM
tmp_table_size = 64M
max_heap_table_size = 64M

# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Binary Log — disable if not using replication
# skip-log-bin

After editing, restart MySQL:

sudo systemctl restart mysql
# or for MariaDB
sudo systemctl restart mariadb

Enabling Slow Query Log

The slow query log is your most powerful tool for identifying bottleneck queries. Enable it before you start tuning, then analyze after a few hours of production traffic.

# Check current status
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

# Enable without restart
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Analyze with mysqldumpslow:

# Show 10 slowest queries by total time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Show most frequently executed slow queries
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

Index and Query Optimization

Good indexes can speed up queries by 100–1000x. Use EXPLAIN to verify your queries are using indexes efficiently.

-- Check query execution plan
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- Show existing indexes
SHOW INDEX FROM orders;

-- Add a composite index
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- Remove an unused index
DROP INDEX old_index_name ON orders;

Find unused indexes

-- MySQL 8 / MariaDB 10.6+
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema','mysql','sys','information_schema');

Using MySQLTuner

MySQLTuner automatically analyzes your MySQL/MariaDB installation and recommends tuning values. Run it after at least 24 hours of production traffic for meaningful recommendations.

# Download
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

# Run
perl mysqltuner.pl --user root --pass your_password

The output highlights:

• [!!] — Critical issues requiring immediate attention
• [OK] — Parameters already well-configured
• Variables to adjust — Recommended tuning values with specific numbers

MariaDB vs MySQL

For new VPS deployments, MariaDB is worth considering over MySQL:

Performance Monitoring

After tuning, monitor continuously to validate improvements:

# Real-time status
mysqladmin -u root -p status

# Active processes
SHOW PROCESSLIST;

# Key global status counters
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

Calculate Buffer Pool Hit Rate

-- Should be above 99%
SELECT
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
  AS buffer_pool_hit_rate
FROM (
  SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
  SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

Optimization Checklist

FAQ