Tune MySQL Performance on Gentoo Linux

InnoDB settings (my.cnf)

[mysqld]
# ── Buffer pool ─────────────────────────────────────────────────────
# Set to 70-80% of RAM on dedicated MySQL server
innodb_buffer_pool_size        = 4G
# For >1 GB buffer pool, use multiple instances
innodb_buffer_pool_instances   = 4

# ── Redo log ─────────────────────────────────────────────────────────
innodb_log_file_size           = 512M
innodb_log_buffer_size         = 64M

# ── I/O ──────────────────────────────────────────────────────────────
# O_DIRECT bypasses OS cache (good when buffer pool is large)
innodb_flush_method            = O_DIRECT
# 1 = fully durable; 2 = up to 1s loss; 0 = up to 1s loss + crash risk
innodb_flush_log_at_trx_commit = 1

# ── Connections ───────────────────────────────────────────────────────
max_connections                = 200
thread_cache_size              = 50
back_log                       = 100

# ── Query cache (disabled in MySQL 8+) ───────────────────────────────
# query_cache_type = 0

# ── Temp tables ───────────────────────────────────────────────────────
tmp_table_size                 = 64M
max_heap_table_size            = 64M

# ── Slow queries ──────────────────────────────────────────────────────
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/slow.log
long_query_time                = 1
log_queries_not_using_indexes  = 1

Restart MySQL

systemctl restart mysql 2>/dev/null || rc-service mysql restart 2>/dev/null || rcctl restart mysqld

Check current buffer pool usage

SHOW STATUS LIKE 'Innodb_buffer_pool_%';

Find missing indexes

SELECT * FROM sys.statements_with_full_table_scans LIMIT 20;