MySQL and MariaDB Performance Tuning on VPS: Buffer Pool, Query Cache, Slow Log, and Indexes

By VPS.DO
July 29, 2026

Database performance is the most common bottleneck in web applications — and the most fixable without hardware upgrades. The default MySQL/MariaDB configuration is conservative and suited for minimal resource usage, not production performance. This guide identifies the highest-impact configuration changes: InnoDB buffer pool sizing, slow query identification and indexing, and connection management.

Baseline: Measure Current Performance

# Connect to MariaDB/MySQL
sudo mysql -u root -p

# Check current key variables
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'query_cache_type';

# Current status — running counters
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Step 1: InnoDB Buffer Pool — The Most Important Setting

The InnoDB buffer pool caches table data and indexes in memory. When data fits in the buffer pool, queries run against RAM (microseconds) instead of disk (milliseconds). This is the single highest-impact database configuration change.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
# Buffer pool: 50–70% of available RAM for a dedicated database server
# For a mixed-use VPS (web + db), use 25–40% of RAM

# 1 GB VPS: 256M
# 2 GB VPS: 512M–768M (if DB-heavy)
# 4 GB VPS: 1G–2G
# 8 GB VPS: 4G–6G
innodb_buffer_pool_size = 512M

# Number of buffer pool instances (improve concurrency for large buffer pools)
# Set to 1 for <1GB buffer pool, 4–8 for larger pools
innodb_buffer_pool_instances = 1

# How much of the buffer pool to flush at checkpoint
innodb_max_dirty_pages_pct = 75

Step 2: Disable Query Cache

The query cache is disabled in MySQL 8.0+ because it causes contention and was proven to hurt performance at high concurrency. In MariaDB, disable it explicitly:

[mysqld]
# Disable query cache — it causes mutex contention under load
query_cache_type = 0
query_cache_size = 0

Step 3: InnoDB I/O Settings

[mysqld]
# Write log before data pages (fsync behavior)
# O_DIRECT: reduces double buffering on Linux (recommended for VPS with NVMe)
innodb_flush_method = O_DIRECT

# How to flush logs and data to disk:
# 1 = safest (flush after every commit, uses fdatasync)
# 2 = flush logs every second (lose up to 1 second of data on crash — faster)
innodb_flush_log_at_trx_commit = 1

# Log file size — larger = fewer checkpoints = better write performance
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M

# Read/write threads (match to VPS vCPU count)
innodb_read_io_threads = 4
innodb_write_io_threads = 4

Step 4: Connection Settings

[mysqld]
# Maximum concurrent connections
# Rule: (RAM for connections) / ~1MB per connection = max_connections
# For 2 GB VPS: 100–150 connections
max_connections = 100

# Timeout idle connections (free up resources)
wait_timeout = 300
interactive_timeout = 300

# Thread cache — reuse threads instead of creating new ones per connection
thread_cache_size = 16

# Temporary tables in memory
tmp_table_size = 64M
max_heap_table_size = 64M

Step 5: Enable Slow Query Log

The slow query log is your most important diagnostic tool — it records every query taking longer than the threshold:

[mysqld]
# Enable slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log

# Log queries slower than N seconds (0.5 = 500ms)
long_query_time = 0.5

# Log queries that don't use indexes (even if fast — they won't scale)
log_queries_not_using_indexes = 1

# Limit log_queries_not_using_indexes entries (prevents log flooding)
log_throttle_queries_not_using_indexes = 10

sudo systemctl restart mariadb

# View slow queries
sudo tail -f /var/log/mysql/slow.log

# Analyze slow query log with mysqldumpslow
sudo mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
# -s t: sort by total query time
# -t 20: show top 20 queries

Step 6: Add Missing Indexes

# Find queries missing indexes via EXPLAIN
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
# Look for: "type: ALL" (full table scan) and "key: NULL" (no index used)

# Create an index on the column used in WHERE clause
CREATE INDEX idx_customer_id ON orders(customer_id);

# Re-run EXPLAIN — should now show "type: ref" and "key: idx_customer_id"
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

# Find all missing indexes automatically (pt-query-digest from Percona Toolkit)
sudo apt install -y percona-toolkit
pt-query-digest /var/log/mysql/slow.log | head -100

# Check index usage across the whole database
SELECT
    table_schema AS 'Database',
    table_name AS 'Table',
    index_name AS 'Index',
    column_name AS 'Column'
FROM information_schema.statistics
WHERE table_schema = 'mydb'
ORDER BY table_name, index_name;

Step 7: Monitor Buffer Pool Hit Rate

-- Check buffer pool effectiveness
SELECT
    FORMAT(Innodb_buffer_pool_read_requests / 
           (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100, 2)
    AS 'Buffer Pool Hit Rate %'
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr,
(
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r;

-- Target: >99% hit rate
-- If <95%: increase innodb_buffer_pool_size

Step 8: WordPress-Specific Optimization

-- Add missing indexes for common WordPress queries
-- (These are often missing on older WordPress installs)

ALTER TABLE wp_options ADD INDEX autoload_idx (autoload);
ALTER TABLE wp_postmeta ADD INDEX meta_value_idx (meta_value(20));
ALTER TABLE wp_usermeta ADD INDEX meta_value_idx (meta_value(20));

-- Find large wp_options entries (common WordPress bloat)
SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;

Complete Tuned Configuration

[mysqld]
# Memory
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 1

# I/O
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# Connections
max_connections = 100
wait_timeout = 300
thread_cache_size = 16
tmp_table_size = 64M
max_heap_table_size = 64M

# Cache (disabled)
query_cache_type = 0
query_cache_size = 0

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Getting Started

Database performance tuning delivers the biggest improvement for PHP/WordPress applications. On a 2 GB Ubuntu VPS at VPS.DO, increasing innodb_buffer_pool_size from the default 128 MB to 512 MB and enabling the slow query log to find and index missing columns typically reduces database query time by 40–70% without any application code changes.

Conclusion

MySQL/MariaDB performance tuning follows a clear priority: right-size the InnoDB buffer pool (most data should fit in memory), disable the query cache (it hurts under load), enable the slow query log (find real bottlenecks), and add indexes on columns used in WHERE clauses (eliminate full table scans). These four changes, applied in order, address 90% of database performance issues on VPS deployments.

MySQL and MariaDB Performance Tuning on VPS: Buffer Pool, Query Cache, Slow Log, and Indexes