MariaDB and MySQL Performance Tuning on Linux

A default MySQL or MariaDB installation is conservative — configured to work on minimal hardware. Most production servers have significantly more RAM and CPU than the defaults assume, which means the database is leaving performance on the table.

These are the settings I tune when setting up a database server. Not all of them apply to every situation, but most are safe improvements for typical web application workloads.

Where Configuration Lives

# MySQL
/etc/mysql/mysql.conf.d/mysqld.cnf    # Ubuntu/Debian
/etc/my.cnf                            # CentOS/RHEL

# MariaDB
/etc/mysql/mariadb.conf.d/50-server.cnf   # Ubuntu/Debian
/etc/my.cnf.d/server.cnf                   # CentOS/RHEL

Always test config changes before restarting:

mysqld --validate-config
# or
mysqld --verbose --help > /dev/null  # Also validates config

InnoDB Buffer Pool

This is the most important setting. The InnoDB buffer pool caches data and indexes in memory. The more you can give it, the less disk I/O you need.

General rule: 70-80% of available RAM for a dedicated database server.

[mysqld]
innodb_buffer_pool_size = 4G    # Adjust for your RAM

On a 8GB RAM server dedicated to MySQL, I’d set this to 5–6GB. Leave enough for the OS, other processes, and MySQL’s own non-buffer-pool memory usage.

Check current usage:

SELECT 
    FORMAT(pool_size * page_size / 1024 / 1024, 2) AS pool_mb,
    FORMAT(data_pages * page_size / 1024 / 1024, 2) AS data_mb,
    FORMAT(free_pages * page_size / 1024 / 1024, 2) AS free_mb,
    FORMAT((pool_size - free_pages) / pool_size * 100, 2) AS hit_rate_pct
FROM information_schema.INNODB_BUFFER_POOL_STATS;

If the buffer pool is consistently nearly full (hit rate > 95%), consider increasing the size. If it’s mostly empty, the setting is too high and you’re wasting memory.

Buffer Pool Instances

For large buffer pools, split into multiple instances:

innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8    # One per GB, typically

Multiple instances reduce contention on the buffer pool mutex. For buffer pools under 1GB, one instance is fine.

Query Cache

# MySQL 5.7 and MariaDB < 10.5 — disable it on write-heavy workloads
query_cache_type = 0
query_cache_size = 0

The query cache was deprecated in MySQL 5.7 and removed in 8.0. For MariaDB, it still exists but is often a bottleneck on heavily concurrent write workloads because the cache mutex becomes a contention point. Disable it if you have many writes; keep it for read-heavy, mostly-static datasets.

InnoDB Log Settings

innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2

innodb_log_file_size — larger log files mean less checkpoint flushing, better write performance. 128M–1GB is a typical range. After changing this, remove the existing ib_logfile* files and restart (MariaDB/MySQL recreates them).

innodb_flush_log_at_trx_commit = 2 — writes log to disk every second instead of every transaction. Improves performance at the cost of potentially losing up to 1 second of transactions on a crash. For most web apps this is acceptable; for financial transactions, keep it at 1.

Connection Settings

max_connections = 200
thread_cache_size = 50

max_connections — maximum simultaneous connections. Each connection uses ~10MB of memory. Don’t set this too high — thousands of simultaneous connections means thousands of threads and lots of memory, which usually hurts more than it helps.

thread_cache_size — reuse threads rather than creating new ones for each connection. Reduces overhead when connections are short-lived (typical for PHP apps using persistent connections poorly).

Check current max connections in use:

SHOW STATUS LIKE 'Max_used_connections';

If this is approaching your limit, increase max_connections. If it’s much lower than your limit, you might have it set too high.

Temporary Tables

tmp_table_size = 128M
max_heap_table_size = 128M

When MySQL needs to create temporary tables for complex queries, it prefers in-memory tables. If a temp table exceeds these limits, it spills to disk (much slower). Check if this is happening:

SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';

If the ratio of disk to total is significant (say, more than 5-10%), consider increasing these values.

Slow Query Log

This is invaluable for finding performance problems:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1    # Log queries taking more than 1 second
log_queries_not_using_indexes = 1    # Also log queries without indexes

After running for a few days, analyze the slow query log:

mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log

-s t sorts by total query time, -t 20 shows top 20. The output shows which queries are consuming the most cumulative time — those are the ones worth optimizing.

Key Settings for My Typical Config

Here’s what I actually put in /etc/mysql/mariadb.conf.d/99-performance.cnf on a typical 8GB web server:

[mysqld]
# InnoDB
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

# Connections
max_connections = 150
thread_cache_size = 50

# Temp tables
tmp_table_size = 128M
max_heap_table_size = 128M

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

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

Monitoring Key Metrics

-- Buffer pool hit rate (should be > 99%)
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';

-- Connection usage
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Table lock waits (high values indicate locking issues)
SHOW STATUS LIKE 'Table_locks_waited';

-- InnoDB row operations
SHOW STATUS LIKE 'Innodb_rows_%';

Automate with a monitoring tool — Netdata auto-discovers MySQL/MariaDB and shows all this visually.

Adding Indexes (The Most Impactful Optimization)

All the configuration tuning in the world won’t fix a missing index. After enabling the slow query log, I look for log_queries_not_using_indexes hits first.

Check query execution plans:

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

If type shows ALL (full table scan), you probably need an index. Add it:

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

The order of columns in composite indexes matters. Put equality conditions first, range conditions last. The MySQL optimizer explains this, but empirically: put the column you filter on most selectively first.

Index improvements are often 10–100x faster than any configuration change. Always look at query plans before tuning server settings.

For keeping your database backed up alongside these performance optimizations, the PostgreSQL backup guide covers backup strategies that apply similarly to MySQL/MariaDB — regular dumps and checking that restores actually work.