MySQL/MariaDB Performance Optimization (my.cnf): Complete Tuning Guide

Introduction

MySQL and MariaDB performance optimization is critical for applications that demand high throughput, low latency, and efficient resource utilization. While these database systems come with sensible default configurations, they rarely match the specific needs of your hardware, workload, or application requirements.

A properly tuned MySQL/MariaDB server can handle significantly more queries per second, reduce response times dramatically, and utilize system resources more efficiently. The difference between default and optimized configurations can mean the difference between handling hundreds versus thousands of concurrent users, or query response times measured in seconds versus milliseconds.

This comprehensive guide focuses on optimizing the my.cnf (or my.ini) configuration file, covering buffer pools, cache settings, connection management, query optimization, and system-specific tuning for different workload patterns. Whether you're running a small application or managing enterprise-scale databases, this guide provides actionable optimization strategies.

Understanding MySQL/MariaDB Architecture

Before diving into optimization, understanding key components helps:

• InnoDB Buffer Pool: Primary memory cache for InnoDB data and indexes
• Query Cache: Caches SELECT statement results (removed in MySQL 8.0)
• Thread Cache: Caches threads to reduce overhead of connection handling
• Table Cache: Caches table file descriptors
• Connection Management: How connections are established and maintained
• Query Optimizer: Determines optimal query execution plans

Prerequisites

Before optimizing your database:

• MySQL or MariaDB installed and running
• Root or sudo access to server
• Understanding of current workload characteristics
• Baseline performance metrics documented
• Configuration backup created
• Access to monitoring tools

Creating Baseline Metrics

Document current performance before making changes:

# Connect to MySQL
mysql -u root -p

# Get current status
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;

# Export current configuration
mysqladmin -u root -p variables > /backup/mysql_vars_before_$(date +%Y%m%d).txt

# Get current performance metrics
mysql -u root -p -e "SHOW GLOBAL STATUS" > /backup/mysql_status_before_$(date +%Y%m%d).txt

# Backup current configuration
sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.pre_optimization

System Information

Gather system specifications:

# CPU cores
nproc

# Total memory
free -h

# Disk information
df -h
lsblk

# Disk type (SSD vs HDD)
lsblk -d -o name,rota

# Check if running in virtual environment
systemd-detect-virt

Configuration File Location

Find your MySQL/MariaDB configuration file:

Ubuntu/Debian:

• /etc/mysql/my.cnf (main config)
• /etc/mysql/mysql.conf.d/mysqld.cnf (server config)
• /etc/mysql/conf.d/ (additional configs)

CentOS/Rocky Linux:

• /etc/my.cnf (main config)
• /etc/my.cnf.d/ (additional configs)

Check active configuration files:

mysql -u root -p -e "SELECT @@global.basedir, @@global.datadir, @@global.pid_file"

# Find configuration file location
mysql --help | grep "Default options" -A 1

InnoDB Configuration

InnoDB is the default storage engine and requires the most tuning attention.

InnoDB Buffer Pool

The buffer pool is the most important parameter for InnoDB performance:

[mysqld]
# InnoDB Buffer Pool Size
# Recommendation: 70-80% of RAM for dedicated database server
# Recommendation: 50-60% of RAM for shared server

# For 16GB RAM dedicated server:
innodb_buffer_pool_size = 12G

# For 32GB RAM dedicated server:
innodb_buffer_pool_size = 24G

# For 64GB RAM dedicated server:
innodb_buffer_pool_size = 48G

# Buffer pool instances (1 per GB, max 64)
# For buffer pool < 1GB, use 1 instance
# For 12GB, use 12 instances
innodb_buffer_pool_instances = 12

# Load buffer pool on startup (faster restarts)
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

# Percentage to scan for dirty pages
innodb_buffer_pool_dump_pct = 25

Sizing Guidelines:

InnoDB Log Files

Configure transaction log files:

[mysqld]
# Log file size (total log size = log_file_size * log_files_in_group)
# Larger values reduce checkpoint frequency but increase recovery time
# Recommendation: 25% of buffer pool size or 1-2GB per file

innodb_log_file_size = 2G
innodb_log_files_in_group = 2

# Log buffer size (writes before flushing to disk)
# 16MB - 32MB is usually sufficient
innodb_log_buffer_size = 32M

# Flush log to disk behavior
# 0 = flush every second (fast, some risk)
# 1 = flush on each commit (safe, slower)
# 2 = flush every second with OS flush (balanced)
innodb_flush_log_at_trx_commit = 2

# For maximum durability (ACID compliance), use:
# innodb_flush_log_at_trx_commit = 1
# For maximum performance with some risk:
# innodb_flush_log_at_trx_commit = 0

InnoDB I/O Configuration

Optimize I/O operations:

[mysqld]
# Flush method (for Linux with SSD)
innodb_flush_method = O_DIRECT

# For NVMe drives or high-performance SSD
innodb_flush_method = O_DIRECT_NO_FSYNC

# I/O capacity (IOPS your storage can handle)
# HDD: 100-200
# SSD: 2000-20000
# NVMe: 10000-50000
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Number of I/O threads
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# For high-concurrency systems, increase threads:
# innodb_read_io_threads = 8
# innodb_write_io_threads = 8

# Use separate file per table
innodb_file_per_table = 1

# Purge threads (cleanup old row versions)
innodb_purge_threads = 4

# Thread concurrency (0 = automatic)
innodb_thread_concurrency = 0

InnoDB Locking

Optimize locking behavior:

[mysqld]
# Lock wait timeout (seconds)
innodb_lock_wait_timeout = 50

# Deadlock detection
innodb_deadlock_detect = ON

# Print deadlock information
innodb_print_all_deadlocks = OFF

InnoDB Additional Settings

[mysqld]
# Double write buffer (safety vs performance)
# ON = safer, OFF = faster (only disable on battery-backed RAID)
innodb_doublewrite = ON

# Page size (default 16K, usually optimal)
innodb_page_size = 16K

# Adaptive hash index
innodb_adaptive_hash_index = ON

# Change buffer max size (% of buffer pool)
innodb_change_buffer_max_size = 25

# NUMA awareness (for multi-socket systems)
innodb_numa_interleave = ON

Connection and Thread Management

Connection Settings

[mysqld]
# Maximum simultaneous connections
# Calculate: (Available RAM - buffer pool - OS) / per-thread buffers
max_connections = 500

# Reserved connections for admin users
max_connect_errors = 1000000

# Connection timeout
connect_timeout = 10
wait_timeout = 600
interactive_timeout = 600

# Thread cache (reuse threads for new connections)
# Recommendation: 8 + (max_connections / 100)
thread_cache_size = 50

# Thread stack size
thread_stack = 256K

# Temporary table size for in-memory operations
tmp_table_size = 64M
max_heap_table_size = 64M

Connection Calculation Example:

For 16GB RAM server with 12GB buffer pool:

Available for connections: 4GB
Per-thread memory: ~4MB (read_buffer + sort_buffer + join_buffer + etc.)
Safe max_connections: 1000 (4GB / 4MB)
Recommended: 500-750 (leave headroom)

Thread Handling

[mysqld]
# Thread handling method
# one-thread-per-connection (default)
# pool-of-threads (MariaDB feature, better for many connections)
thread_handling = pool-of-threads

# Thread pool size (MariaDB only)
thread_pool_size = 16  # Usually match CPU cores

# Thread pool max threads
thread_pool_max_threads = 1000

Query Cache (MySQL 5.7 and Earlier)

Note: Query cache is removed in MySQL 8.0 due to performance issues with modern hardware.

For MySQL 5.7 and MariaDB:

[mysqld]
# Query cache type
# 0 = OFF
# 1 = ON (cache all SELECT queries except SQL_NO_CACHE)
# 2 = DEMAND (only cache queries with SQL_CACHE)
query_cache_type = 1

# Query cache size (0 = disabled)
# 64MB - 256MB for most workloads
query_cache_size = 128M

# Maximum result set size to cache
query_cache_limit = 4M

# Minimum result set size to cache
query_cache_min_res_unit = 4K

# Fragmentation threshold for memory compaction
query_cache_wlock_invalidate = OFF

Query Cache Guidelines:

• Disable for write-heavy workloads
• Useful for read-heavy workloads with repeated queries
• Monitor fragmentation: SHOW STATUS LIKE 'Qcache%';
• If hit rate < 20%, consider disabling

Buffer and Cache Settings

Table and Key Caches

[mysqld]
# MyISAM key buffer (only if using MyISAM tables)
key_buffer_size = 256M

# Table open cache (number of open tables)
# Increase if 'opened_tables' is high
table_open_cache = 4000
table_definition_cache = 2000

# Open files limit
open_files_limit = 8000

# Metadata locks cache
metadata_locks_hash_instances = 64

Sort and Join Buffers

[mysqld]
# Sort buffer (per connection, for ORDER BY and GROUP BY)
# Keep reasonable, allocated per sort operation
sort_buffer_size = 2M

# Read buffer (for sequential table scans)
read_buffer_size = 2M

# Random read buffer (for sorting after index scan)
read_rnd_buffer_size = 4M

# Join buffer (for joins without indexes)
join_buffer_size = 2M

# Range query buffer
range_alloc_block_size = 4K

Per-Connection Buffer Warning:

These buffers are allocated per connection per operation:

Total potential memory = max_connections * (
    sort_buffer_size +
    read_buffer_size +
    read_rnd_buffer_size +
    join_buffer_size
)

Keep individual buffers reasonable (2-4MB) to prevent memory exhaustion.

Binary Logging Configuration

Binary logs impact performance but are essential for replication and point-in-time recovery:

[mysqld]
# Enable binary logging
log_bin = /var/lib/mysql/mysql-bin

# Binary log format
# ROW = safer, more disk space
# STATEMENT = less disk space, potential inconsistencies
# MIXED = automatic choice
binlog_format = ROW

# Binary log cache size (per transaction)
binlog_cache_size = 1M

# Maximum binlog size before rotation
max_binlog_size = 1G

# Automatic purge of old binary logs
expire_logs_days = 7
# Or for MySQL 8.0+:
# binlog_expire_logs_seconds = 604800  # 7 days

# Sync binary log to disk
# 0 = don't sync (fast, risky)
# 1 = sync on commit (safe, slower)
# N = sync every N commits (balanced)
sync_binlog = 1

# For performance over durability:
# sync_binlog = 100

Slow Query Logging

Essential for identifying performance bottlenecks:

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

# Query execution time threshold (seconds)
long_query_time = 2

# Log queries not using indexes
log_queries_not_using_indexes = 1

# Throttle logging of queries not using indexes
log_throttle_queries_not_using_indexes = 10

# Log slow admin statements (ALTER, OPTIMIZE, etc.)
log_slow_admin_statements = 1

# Log slow replica statements
log_slow_slave_statements = 1

# Minimum number of rows examined to log
min_examined_row_limit = 1000

Analyze slow queries:

# Use mysqldumpslow to analyze
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Or use pt-query-digest (Percona Toolkit)
pt-query-digest /var/log/mysql/slow-query.log

Network and Protocol Settings

Network Configuration

[mysqld]
# TCP/IP settings
back_log = 500
max_allowed_packet = 256M

# Skip reverse DNS lookup (performance boost)
skip-name-resolve

# Disable host cache
host_cache_size = 0

# Network buffer sizes
net_buffer_length = 16K
net_read_timeout = 30
net_write_timeout = 60

Performance Schema

Monitor and diagnose performance issues:

[mysqld]
# Enable performance schema
performance_schema = ON

# Performance schema memory (auto-sized by default)
# Increase if running out of memory
performance_schema_max_table_instances = 12500
performance_schema_max_table_handles = 4000

# Statement instrumentation
performance-schema-instrument = 'stage/%=ON'
performance-schema-instrument = 'statement/%=ON'

# Consumer configuration
performance-schema-consumer-events-stages-current = ON
performance-schema-consumer-events-statements-current = ON

Query performance schema:

-- Top queries by execution time
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 AS avg_ms,
    SUM_TIMER_WAIT/1000000000 AS sum_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Table with most I/O
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_READ + COUNT_WRITE AS total_io
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY total_io DESC
LIMIT 10;

Workload-Specific Optimizations

High-Traffic Web Application

[mysqld]
# Optimized for many connections, mixed read/write
max_connections = 1000
thread_cache_size = 100

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

query_cache_type = 0  # Disable for MySQL 5.7

table_open_cache = 4000
tmp_table_size = 128M
max_heap_table_size = 128M

slow_query_time = 1

Read-Heavy Reporting

[mysqld]
# Optimized for complex queries, reporting
max_connections = 200
thread_cache_size = 50

innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 20

# Larger buffers for complex queries
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
join_buffer_size = 4M

tmp_table_size = 256M
max_heap_table_size = 256M

# Query cache useful for repeated reports
query_cache_type = 1  # MySQL 5.7
query_cache_size = 256M

Write-Heavy OLTP

[mysqld]
# Optimized for high transaction throughput
max_connections = 500
thread_cache_size = 100

innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 16

# Optimize for writes
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M

innodb_write_io_threads = 8
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000

# Disable query cache
query_cache_type = 0

sync_binlog = 100  # Trade safety for speed

Small VPS (2GB RAM)

[mysqld]
# Minimal resource usage
max_connections = 100
thread_cache_size = 8

innodb_buffer_pool_size = 768M
innodb_buffer_pool_instances = 1

innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

sort_buffer_size = 512K
read_buffer_size = 512K
join_buffer_size = 512K

tmp_table_size = 32M
max_heap_table_size = 32M

table_open_cache = 400

Monitoring Performance

Key Metrics to Monitor

-- Buffer pool hit ratio (should be > 99%)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

-- Calculate hit ratio
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio
FROM
    (SELECT
        variable_value AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE variable_name = 'Innodb_buffer_pool_reads') AS reads,
    (SELECT
        variable_value AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS requests;

-- Thread cache hit ratio (should be > 90%)
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Connections';

-- Table cache usage
SHOW GLOBAL STATUS LIKE 'Open%tables';
SHOW GLOBAL VARIABLES LIKE 'table_open_cache';

-- Query cache statistics (MySQL 5.7)
SHOW GLOBAL STATUS LIKE 'Qcache%';

-- Temporary table usage
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- Connection usage
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Tuning Tools

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

# Percona Configuration Wizard
# Visit: https://tools.percona.com/wizard

# pt-variable-advisor (Percona Toolkit)
pt-variable-advisor localhost

# MySQL Performance Schema queries
# Available in MySQL Workbench or custom scripts

Applying Configuration Changes

Testing Configuration

# Test configuration for syntax errors
mysqld --help --verbose | grep -A 1 'Default options'

# Dry run (doesn't actually start server)
mysqld --verbose --help 2>&1 | grep "^config-file"

# Check specific variable
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Applying Changes

# Restart MySQL to apply changes
sudo systemctl restart mysql

# Verify new settings
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"

# Check error log for issues
sudo tail -f /var/log/mysql/error.log

# Monitor performance after changes
mysqladmin -u root -p status
mysqladmin -u root -p extended-status

Dynamic Variables

Some variables can be changed without restart:

-- Change dynamically (lost on restart)
SET GLOBAL max_connections = 1000;
SET GLOBAL innodb_io_capacity = 2000;

-- Verify change
SHOW VARIABLES LIKE 'max_connections';

-- Make permanent in my.cnf for next restart

Complete Optimized Configuration Example

Production configuration for 32GB RAM dedicated server:

[mysqld]
# Basic Settings
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql

# Character Set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Connection Settings
max_connections = 500
max_connect_errors = 1000000
connect_timeout = 10
wait_timeout = 600
interactive_timeout = 600
thread_cache_size = 50
thread_stack = 256K

# InnoDB Buffer Pool
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 24
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

# InnoDB Log Files
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2

# InnoDB I/O
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_file_per_table = 1
innodb_purge_threads = 4

# Buffers
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 2M

# Table Cache
table_open_cache = 4000
table_definition_cache = 2000
open_files_limit = 8000

# Temporary Tables
tmp_table_size = 128M
max_heap_table_size = 128M

# Binary Logging
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
max_binlog_size = 1G
expire_logs_days = 7
sync_binlog = 1

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

# Error Log
log_error = /var/log/mysql/error.log

# Network
max_allowed_packet = 256M
skip-name-resolve

# Performance Schema
performance_schema = ON

Troubleshooting Performance Issues

High CPU Usage

-- Find expensive queries
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

-- Check for full table scans
SHOW GLOBAL STATUS LIKE 'Select_scan';
SHOW GLOBAL STATUS LIKE 'Select_full_join';

High Memory Usage

# Check buffer pool usage
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "BUFFER POOL"

# Check connection memory
mysql -u root -p -e "SELECT * FROM sys.memory_by_user_by_current_bytes;"

Slow Queries

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

# Or use pt-query-digest
pt-query-digest /var/log/mysql/slow-query.log | head -100

Conclusion

MySQL/MariaDB performance optimization is an iterative process requiring continuous monitoring, testing, and adjustment. This guide has provided comprehensive tuning strategies for the my.cnf configuration file.

Key Takeaways

1. Buffer Pool is Critical: Allocate 70-80% of RAM on dedicated servers
2. Monitor Before and After: Document baseline metrics
3. Workload-Specific Tuning: Optimize for your specific use case
4. Test Changes: Apply changes incrementally and monitor impact
5. Regular Review: Performance tuning is ongoing, not one-time

Optimization Checklist

• ✓ Size innodb_buffer_pool appropriately
• ✓ Configure buffer pool instances
• ✓ Optimize InnoDB log files
• ✓ Set appropriate connection limits
• ✓ Configure thread cache
• ✓ Enable slow query logging
• ✓ Disable query cache (MySQL 8.0) or tune it (5.7)
• ✓ Optimize I/O settings for storage type
• ✓ Monitor key performance metrics
• ✓ Use performance schema for analysis
• ✓ Regular performance audits

Additional Resources

• MySQL Performance Tuning
• MariaDB Performance Tuning
• Percona Database Performance Blog
• MySQLTuner Script

Remember: measure, optimize, measure again. Performance tuning is an empirical science requiring data-driven decisions.