MariaDB Performance Tuning for VPS Servers

Key Configuration Parameters

Edit /etc/mysql/mariadb.conf.d/50-server.cnf (or /etc/my.cnf):

InnoDB Buffer Pool

The most impactful setting — allocate 60-70% of available RAM:

[mysqld]
# For a 4GB RAM server
innodb_buffer_pool_size = 2560M
innodb_buffer_pool_instances = 4

Query and Thread Settings

# Connection limits
max_connections = 200
max_connect_errors = 100000

# Thread handling
thread_cache_size = 16
thread_handling = pool-of-threads  # MariaDB-specific

# Query cache (disabled in MariaDB 10.6+ by default)
query_cache_type = 0

# Temp tables
tmp_table_size = 64M
max_heap_table_size = 64M

InnoDB Optimization

# Log files
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# Flushing
innodb_flush_log_at_trx_commit = 2  # Better performance (slight durability risk)
innodb_flush_method = O_DIRECT

# I/O
innodb_io_capacity = 1000       # SSD
innodb_io_capacity_max = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 4

Identifying Slow Queries

# Enable slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # Log queries taking > 1 second
log_queries_not_using_indexes = 1

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

Check Current Settings

# Buffer pool usage
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

# Connection usage
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_%';

# Table cache
SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW VARIABLES LIKE 'table_open_cache';

Quick Wins

• Add indexes on columns used in WHERE, JOIN, and ORDER BY
• Use EXPLAIN to analyze query execution plans
• Avoid SELECT * — fetch only needed columns
• Use connection pooling in your application