Performance Tuning MySQL

Configuração de Memória

# /etc/mysql/mysql.conf.d/performance.cnf
[mysqld]
# Buffer pool (InnoDB) — >70% da RAM disponível para DB dedicado
innodb_buffer_pool_size = 8G          # 70-80% RAM total
innodb_buffer_pool_instances = 4      # 1 instância a cada ~2GB

# Log buffer
innodb_log_file_size = 1G             # Total transactions que pode recovery
innodb_log_buffer_size = 64M

# Temp tables
tmp_table_size = 128M
max_heap_table_size = 128M

# Connections
max_connections = 500
thread_cache_size = 32

Query Cache (MySQL < 8.0)

# MySQL 5.7 — query cache (removido no 8.0)
query_cache_type = 1
query_cache_size = 256M

MySQL 8.0: query cache removido

No MySQL 8.0, o query cache foi removido por problemas de escalabilidade.
Alternativas:
├── ProxySQL (caching inteligente)
├── Redis/Memcached (cache externo)
└── Application-level caching

Slow Query Log

-- Configurar
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;       -- queries > 2 segundos
SET GLOBAL log_queries_not_using_indexes = ON;

-- Analisar
mysqldumpslow /var/log/mysql/slow.log

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

-- Exemplo de saída:
-- # Query 1: 0.08QPS, 0.09x concurrency, ID 0xABC
-- # Exec time: 123s total, 2.5s avg
-- # Lock time: 0s total
-- # Rows sent: 100 avg, 10000 max

Índices e EXPLAIN

-- ANALYZE TABLE para atualizar estatísticas
ANALYZE TABLE clientes;

-- EXPLAIN: identificar full table scans
EXPLAIN FORMAT=JSON 
SELECT * FROM pedidos WHERE cliente_id = 1\G

-- pt-index-usage (Percona)
pt-index-usage /var/log/mysql/slow.log --user=root --password=xxx

Status e Métricas

-- Cache hit ratio (ideal > 95%)
SELECT 
    (1 - (Innodb_buffer_pool_reads / GREATEST(Innodb_buffer_pool_read_requests, 1))) * 100
    AS buffer_pool_hit_ratio
FROM performance_schema.global_status;

-- Threads conectadas (vs max_connections)
SELECT VARIABLE_VALUE AS max_conn
FROM performance_schema.global_variables
WHERE VARIABLE_NAME = 'max_connections';

-- Locks aguardando
SELECT * FROM performance_schema.data_lock_waits;

-- Tabelas sem índices primários
SELECT t.table_schema, t.table_name, t.engine
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
    ON t.table_schema = c.table_schema 
    AND t.table_name = c.table_name 
    AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema NOT IN ('mysql', 'performance_schema', 'sys')
    AND c.constraint_name IS NULL;

Configuração por Perfil

# OLTP (WEB APP) — muitas transações pequenas
innodb_buffer_pool_size = 70% RAM
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1   # segurança máxima
innodb_io_capacity = 1000

# OLAP / Analytics — queries pesadas
innodb_buffer_pool_size = 80% RAM
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 2   # performance
innodb_io_capacity = 2000
tmp_table_size = 256M
max_heap_table_size = 512M

# Batch / Big Data — alta throughput
innodb_buffer_pool_size = 60% RAM
innodb_log_file_size = 8G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Buffer pool é o parâmetro mais importante — aloque 70-80% da RAM. Slow query log é essencial para identificar gargalos. pt-query-digest do Percona Toolkit analisa queries lentas. Evite query cache no MySQL 8.