Queries lentas são as assassinas silenciosas do desempenho de bancos de dados. Uma única query não otimizada executando milhares de vezes por dia pode derrubar um servidor MySQL que de outra forma funcionaria bem. O slow query log do MySQL é sua primeira linha de defesa — ele captura cada query que excede um limite de tempo, fornecendo os dados necessários para identificar e eliminar gargalos de desempenho. Combinado com ferramentas de análise como mysqldumpslow e pt-query-digest, você pode encontrar, priorizar e corrigir sistematicamente as piores ofensoras na sua carga de trabalho.
Pré-requisitos
- MySQL 5.7+ ou MySQL 8.0 (comandos funcionam em ambos; diferenças menores indicadas)
- Acesso root ou administrativo ao servidor MySQL
- Acesso shell ao servidor executando MySQL
- Percona Toolkit instalado (para
pt-query-digest) - Conhecimento básico de SQL e arquivos de configuração do MySQL
Ativando o Slow Query Log
O slow query log está desativado por padrão na maioria das instalações do MySQL. Você pode ativá-lo permanentemente via configuração ou temporariamente em tempo de execução.
Configuração Permanente (my.cnf)
Adicione estas linhas ao seu arquivo de configuração do MySQL, normalmente em /etc/mysql/my.cnf ou /etc/my.cnf:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_output = FILEReinicie o MySQL para aplicar:
sudo systemctl restart mysqlConfiguração em Tempo de Execução (Sem Reinício)
Ative o slow query log sem reiniciar o servidor:
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';Verifique as configurações:
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';Saída esperada:
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/slow.log |
+---------------------+-------------------------------+Opções de log_output
O MySQL suporta três destinos de saída:
- FILE — grava no arquivo especificado por
slow_query_log_file(padrão, recomendado) - TABLE — grava na tabela
mysql.slow_log(consultável mas adiciona overhead) - NONE — desativa o registro apesar de
slow_query_log=1
Use FILE para produção. A opção TABLE adiciona overhead mensurável de escrita e faz o schema mysql crescer de forma imprevisível.
Configurando Parâmetros do Log
long_query_time
Este limite determina quais queries são registradas. O padrão de 10 segundos é muito alto para a maioria das cargas de trabalho.
-- Registrar queries que levem mais de 0.5 segundos
SET GLOBAL long_query_time = 0.5;
-- Registrar TODAS as queries (útil para auditorias curtas, não para produção)
SET GLOBAL long_query_time = 0;Valores iniciais recomendados:
| Ambiente | long_query_time | Justificativa |
|---|---|---|
| Desenvolvimento | 0 | Captura tudo para análise |
| Staging | 0.1 | Detecta queries >100ms |
| Produção | 1.0 | Foco em queries claramente lentas |
| Agressivo | 0.5 | Equilíbrio entre ruído e cobertura |
log_queries_not_using_indexes
Este parâmetro registra queries que fazem varreduras completas de tabela, independente do tempo de execução:
SET GLOBAL log_queries_not_using_indexes = 1;É extremamente valioso para detectar queries que são rápidas agora mas degradarão conforme as tabelas crescerem. Porém, pode gerar muitas entradas de log em bancos com muitas tabelas pequenas.
min_examined_row_limit
Filtra queries triviais exigindo um número mínimo de linhas examinadas:
SET GLOBAL min_examined_row_limit = 1000;Isso evita que o log se encha com buscas rápidas de uma única linha que não usam índice.
Análise com mysqldumpslow
O mysqldumpslow vem incluído com o MySQL e não requer instalação adicional. Ele faz parsing do slow query log, abstrai valores literais e agrupa queries semelhantes.
Uso Básico
# Top 10 queries por tempo total
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# Top 10 queries por frequência
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# Top 10 queries por tempo médio
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
# Filtrar queries que correspondam a um padrão
mysqldumpslow -s t -t 10 -g "orders" /var/log/mysql/slow.logOpções de Ordenação
| Flag | Ordena por |
|---|---|
-s t | Tempo total |
-s c | Frequência (contagem) |
-s at | Tempo médio |
-s l | Tempo de lock |
-s al | Tempo médio de lock |
-s r | Linhas examinadas |
-s ar | Média de linhas examinadas |
Exemplo de Saída
Count: 1523 Time=2.45s (3731s) Lock=0.00s (1s) Rows=245.3 (373534), root[root]@localhost
SELECT * FROM orders WHERE customer_id = N AND status = 'S' ORDER BY created_at DESC LIMIT N;Isso indica que a query foi executada 1.523 vezes, com média de 2,45 segundos cada, examinando 245 linhas por execução e consumindo 3.731 segundos no total.
Limitações
O mysqldumpslow é útil para verificações rápidas mas tem limitações significativas:
- Sem análise de percentis (P95, P99)
- Sem distribuição de tempo de resposta
- Não pode ler logs em formato binário ou TABLE
- Opções limitadas de filtragem e relatórios
Análise Aprofundada com pt-query-digest
O pt-query-digest do Percona Toolkit é o padrão da indústria para análise de slow query logs. Fornece informações muito mais detalhadas que o mysqldumpslow.
Instalação
# Debian/Ubuntu
sudo apt-get install percona-toolkit
# RHEL/CentOS
sudo yum install percona-toolkit
# A partir do código fonte
wget https://www.percona.com/downloads/percona-toolkit/LATEST/tarball/percona-toolkit-LATEST.tar.gz
tar xzf percona-toolkit-LATEST.tar.gz
cd percona-toolkit-* && perl Makefile.PL && make && sudo make installUso Básico
# Relatório completo
pt-query-digest /var/log/mysql/slow.log
# Relatório das últimas 24 horas
pt-query-digest --since '24h' /var/log/mysql/slow.log
# Filtrar por banco de dados
pt-query-digest --filter '$event->{db} eq "myapp"' /var/log/mysql/slow.log
# Saída para arquivo
pt-query-digest /var/log/mysql/slow.log > /tmp/slow-report.txtEstrutura do Relatório
A saída possui três seções:
1. Resumo Geral
# 2.1M QPS, 1.2x concurrency, 45% of time in query
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 3731s 100ms 45s 2.45s 4.12s 1.33s 1.85s
# Lock time 1s 0 15ms 10us 22us 38us 8us
# Rows sent 373534 0 1000 245 480.0 180.3 198.0
# Rows examine 2.1M 0 50000 1382 3200 2100 8502. Perfil (Ranking de Queries)
# Rank Query ID Response time Calls R/Call Item
# ==== ================================ ============== ====== ======= ====
# 1 0xE77769C62EF669AA1A6... 1800.0 48.2% 1523 1.1819 SELECT orders
# 2 0xA4B9D8C1F3E5A7B2C9... 950.3 25.5% 892 1.0654 SELECT products
# 3 0xF1C2D3E4A5B6C7D8E9... 480.1 12.9% 3201 0.1500 SELECT users3. Detalhe por Query — para cada query classificada, você obtém o fingerprint completo, distribuição de tempos, recomendações EXPLAIN e uma query de exemplo com valores literais.
mysqldumpslow vs pt-query-digest vs MySQL Enterprise Monitor vs PMM
| Recurso | mysqldumpslow | pt-query-digest | MySQL Enterprise Monitor | Percona PMM |
|---|---|---|---|---|
| Custo | Grátis (incluído) | Grátis (open source) | Licença comercial | Grátis (open source) |
| Instalação | Nenhuma | Percona Toolkit | Servidor dedicado | Docker/VM |
| Monitoramento em tempo real | Não | Não | Sim | Sim |
| Análise histórica | Básica | Excelente | Excelente | Excelente |
| Fingerprinting | Básico | Avançado | Avançado | Avançado |
| Análise de percentis | Não | Sim (P95, P99) | Sim | Sim |
| Dashboard visual | Não | Não | Sim | Sim (Grafana) |
| Integração EXPLAIN | Não | Parcial | Sim | Sim |
| Alertas | Não | Não | Sim | Sim |
| Ideal para | Verificações rápidas | Análise profunda | Equipes enterprise | Equipes que querem UI gratuita |
Otimização de Queries Lentas
Uma vez identificadas as queries mais lentas, siga esta abordagem sistemática.
Passo 1: EXPLAIN da Query
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;Colunas-chave a examinar:
- type: Deve ser
ref,rangeouconst. EviteALL(varredura completa de tabela) - key: Qual índice o MySQL escolheu.
NULLsignifica que nenhum índice foi usado - rows: Linhas estimadas examinadas. Números altos indicam índices ausentes
- Extra: Observe
Using filesorteUsing temporary
No MySQL 8.0, use EXPLAIN ANALYZE para estatísticas reais de execução:
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;Passo 2: Adicionar Índices Ausentes
Com base na saída do EXPLAIN, crie índices compostos que cubram as cláusulas WHERE e ORDER BY:
-- Cobre WHERE customer_id AND status, ORDER BY created_at
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);Regras de design de índices:
- Colunas de igualdade primeiro (
customer_id,status) - Colunas de range/ordenação por último (
created_at) - Inclua colunas do SELECT se criar um covering index
- Evite indexação excessiva — cada índice desacelera escritas
Passo 3: Reescrever Padrões Problemáticos
Reescritas comuns que melhoram o desempenho:
-- RUIM: Subquery executada por linha
SELECT * FROM orders WHERE customer_id IN (
SELECT id FROM customers WHERE region = 'US'
);
-- BOM: JOIN executa uma vez
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'US';
-- RUIM: SELECT * busca colunas não utilizadas
SELECT * FROM orders WHERE id = 42;
-- BOM: Seleciona apenas colunas necessárias
SELECT id, status, total, created_at FROM orders WHERE id = 42;
-- RUIM: Função em coluna indexada impede uso do índice
SELECT * FROM orders WHERE YEAR(created_at) = 2025;
-- BOM: Varredura por range usa o índice
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';Cenário do Mundo Real
Você gerencia o banco de dados de uma plataforma de e-commerce. Usuários relatam que páginas de produtos carregam lentamente durante horários de pico (14h-18h), mas o resto do site funciona bem. Métricas do servidor mostram picos de CPU coincidindo com as reclamações, mas memória e I/O de disco estão normais.
Passo 1: Ative o slow query log com long_query_time=0.5 durante os horários de pico.
Passo 2: Após 2 horas de registro, execute pt-query-digest:
pt-query-digest --since '2h' /var/log/mysql/slow.logPasso 3: O relatório revela que a query principal consome 62% do tempo total:
SELECT p.*, c.name as category_name,
(SELECT AVG(rating) FROM reviews WHERE product_id = p.id) AS avg_rating,
(SELECT COUNT(*) FROM reviews WHERE product_id = p.id) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.category_id = 15 AND p.active = 1
ORDER BY created_at DESC LIMIT 20;Passo 4: EXPLAIN mostra que as subqueries correlacionadas executam uma vez por linha de produto (2.400 vezes por carregamento de página). Corrija reescrevendo com JOIN:
SELECT p.*, c.name as category_name,
COALESCE(r.avg_rating, 0) AS avg_rating,
COALESCE(r.review_count, 0) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
SELECT product_id, AVG(rating) as avg_rating, COUNT(*) as review_count
FROM reviews GROUP BY product_id
) r ON r.product_id = p.id
WHERE p.category_id = 15 AND p.active = 1
ORDER BY p.created_at DESC LIMIT 20;Passo 5: Adicione um índice composto:
CREATE INDEX idx_products_category_active_created
ON products (category_id, active, created_at DESC);Resultado: O tempo da query cai de 2,4 segundos para 12 milissegundos. O carregamento de páginas volta ao normal.
Armadilhas e Casos Especiais
- Rotação de logs é essencial. Sem rotação, o slow log pode consumir todo o espaço disponível em disco. Use
logrotateou o comandoFLUSH SLOW LOGSdo MySQL com um cron job. - Monitoramento de espaço em disco. Configurar
long_query_time=0em um servidor movimentado pode gerar gigabytes de dados de log por hora. Sempre monitore o uso de disco ao reduzir o limite. - Atraso de replicação pelo logging. Em réplicas com
log_slow_replica_statements=1(MySQL 8.0.26+), registrar queries replicadas pode aumentar o atraso. MonitoreSeconds_Behind_Source. - Inundação por log_queries_not_using_indexes. Tabelas pequenas de lookup (países, status) acionarão este flag mesmo que varreduras completas em tabelas de 50 linhas sejam ideais. Use
min_examined_row_limitpara filtrá-las. - Prepared statements. Por padrão, prepared statements não são registrados. Configure
log_slow_extra=1(MySQL 8.0.14+) para capturar estatísticas extras. - Mudanças em tempo de execução não persistem. Mudanças com SET GLOBAL são perdidas ao reiniciar. Sempre atualize o
my.cnfalém dos comandos em tempo de execução.
Resolução de Problemas
Arquivo do slow query log não é criado: Verifique se o MySQL tem permissões de escrita no diretório de logs:
sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysqlLog mostra zero queries apesar de slow_query_log=ON:
Verifique se o long_query_time não está configurado muito alto:
SELECT @@global.long_query_time;Se mostra 10.000000, queries precisam levar mais de 10 segundos para serem registradas. Reduza para 1.
pt-query-digest reporta “No events processed”:
O arquivo de log pode estar vazio ou o formato não é reconhecido. Certifique-se de que log_output=FILE (não TABLE). Verifique permissões:
ls -la /var/log/mysql/slow.log
file /var/log/mysql/slow.logmysqldumpslow mostra “Permission denied”:
A ferramenta precisa de acesso de leitura ao arquivo de log. Execute com sudo ou adicione seu usuário ao grupo mysql:
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.logQueries aparecem no slow log mas EXPLAIN mostra execução rápida:
Isso geralmente significa contenção de locks. Verifique o campo Lock_time na entrada do log. Tempos altos de lock indicam problemas de locking no nível da tabela ou linha, não problemas de otimização de query.
Resumo
- Ative o slow query log com
slow_query_log=1e configurelong_query_timepara 1 segundo ou menos - Use
log_queries_not_using_indexespara detectar queries que degradarão conforme os dados crescerem - Comece com
mysqldumpslowpara análise rápida; mude parapt-query-digestpara análise de nível produção - Foque no tempo total (frequência × tempo médio) em vez de apenas a execução mais lenta
- Use
EXPLAINeEXPLAIN ANALYZE(MySQL 8.0) para entender planos de execução antes de criar índices - Projete índices compostos com colunas de igualdade primeiro e colunas de range/ordenação por último
- Reescreva subqueries correlacionadas como JOINs para melhorias de uma ordem de magnitude
- Sempre implemente rotação de logs e monitore espaço em disco ao usar o slow query log em produção