MySQL Configuration Tuning

# my.cnf — InnoDB buffer pool configuration
[mysqld]
# Set to 60-80% of available RAM
# Example: 16 GB server → 10-12 GB buffer pool
innodb_buffer_pool_size = 10G

# Multiple instances reduce mutex contention (1 per 1 GB)
innodb_buffer_pool_instances = 10

# Warm buffer pool on restart (avoids cold-start I/O storm)
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# Monitor buffer pool hit ratio
SELECT
    (1 - (SELECT variable_value FROM performance_schema.global_status
          WHERE variable_name = 'Innodb_buffer_pool_reads') /
         (SELECT variable_value FROM performance_schema.global_status
          WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    ) * 100 AS buffer_pool_hit_ratio_pct;
-- Target: > 99%

# Shortcut via sys schema
SELECT * FROM sys.innodb_buffer_stats_by_schema
ORDER BY pages_allocated DESC LIMIT 10;

# my.cnf — write performance and connection tuning
[mysqld]
# Redo log capacity (MySQL 8.0.30+)
innodb_redo_log_capacity = 4G   # larger = better throughput, slower recovery
# Older MySQL: innodb_log_file_size = 1G  (redo log = 2 × log_file_size)

# Durability vs performance trade-off
innodb_flush_log_at_trx_commit = 1   # safest: flush to disk on every commit
# = 2: flush to OS buffer on commit (lose up to 1s on crash)
# = 0: flush every second (lose up to 1s on crash + OS crash)
sync_binlog = 1                       # flush binlog to disk on every commit

# Connection tuning
max_connections = 500             # max concurrent connections
thread_cache_size = 50            # cache 50 idle threads (reduce thread creation)
wait_timeout = 600                # close idle connections after 10 min
interactive_timeout = 600

# I/O method (Linux) — O_DIRECT bypasses OS page cache (reduces double buffering)
innodb_flush_method = O_DIRECT

# Check current connection overhead
SHOW STATUS LIKE 'Threads_created';    # high = thread_cache too small
SHOW STATUS LIKE 'Connections';        # total connection attempts
SHOW STATUS LIKE 'Max_used_connections';  # peak simultaneous connections

# my.cnf — temp tables and optimizer settings
[mysqld]
# Temp table memory limits
tmp_table_size = 256M           # max in-memory temp table size
max_heap_table_size = 256M      # must match tmp_table_size

# Join buffer for non-indexed joins
join_buffer_size = 4M           # per-join allocation (not global pool)

# Sort buffer
sort_buffer_size = 4M           # per-sort allocation

# Key buffer (MyISAM only — for InnoDB, use buffer pool)
key_buffer_size = 32M

# Check if temp tables are spilling to disk
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';   # spilled to disk
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';         # total temp tables
# Ratio: disk / total > 10% → increase tmp_table_size

# EXPLAIN to check sorts and temp tables
EXPLAIN SELECT customer_id, COUNT(*) FROM orders
GROUP BY customer_id ORDER BY COUNT(*) DESC;
-- "Using temporary; Using filesort" = memory temp table + sort
-- Increase tmp_table_size or add a covering index