Optimizing MySQL and MariaDB Performance

Introduction

Database performance directly impacts application response times. These tuning parameters apply to both MySQL and MariaDB on typical VPS configurations.

Key my.cnf Settings

Edit /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
# InnoDB Buffer Pool — set to 50-70% of available RAM
innodb_buffer_pool_size = 2G

# Log file size — larger = better write performance, longer crash recovery
innodb_log_file_size = 512M

# Flush method — O_DIRECT avoids double-buffering with OS cache
innodb_flush_method = O_DIRECT

# Buffer pool instances (1 per GB of buffer pool)
innodb_buffer_pool_instances = 2

# Thread cache
thread_cache_size = 16

# Query cache (disable for write-heavy workloads)
query_cache_type = 0
query_cache_size = 0

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Connection limits
max_connections = 200

# Sort and join buffers
sort_buffer_size = 4M
join_buffer_size = 4M

Identify Slow Queries

# Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# Analyze slow queries
mysqldumpslow -s t /var/log/mysql/slow.log

Check Buffer Pool Efficiency

SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

If Innodb_buffer_pool_reads is high relative to Innodb_buffer_pool_read_requests, increase the buffer pool size.

After Changes

sudo systemctl restart mariadb