Como Ativar o Log de Consultas Lentas no MySQL e MariaDB

O slow query log é uma funcionalidade de diagnóstico integrada no MySQL e MariaDB que regista todas as instruções SQL cujo tempo de execução excede um limite configurável. Captura a duração da consulta, o tempo de bloqueio, as linhas examinadas, as linhas enviadas e o texto SQL completo — fornecendo aos administradores de bases de dados e programadores um registo de auditoria preciso, baseado em ficheiros, de cada consulta que está a degradar o desempenho da aplicação.

Ativá-lo é uma das ações de maior impacto que pode tomar durante a otimização do desempenho da base de dados. Ao contrário das ferramentas de monitorização genéricas, o slow query log identifica com precisão as instruções responsáveis pela latência, tornando-o indispensável para a otimização de índices, reestruturação de consultas e planeamento de capacidade em qualquer servidor — desde um ambiente de VPS Hosting de inquilino único até um cluster de base de dados dedicado com múltiplos nós.

Por Que o Slow Query Log É Importante Além da Monitorização Básica

A maioria das equipas recorre ao EXPLAIN ou SHOW PROCESSLIST de forma reativa, após os utilizadores reportarem lentidão. O slow query log funciona de forma proativa: acumula evidências ao longo de horas ou dias de tráfego real, capturando infratores intermitentes que nunca aparecem durante uma janela de inspeção manual.

Os principais benefícios operacionais incluem:

• Isolamento de bottlenecks — distingue varreduras completas de tabelas com uso intensivo de CPU de problemas de contenção de bloqueios usando os rácios Query_time vs. Lock_time
• Análise de lacunas de índices — o sinalizador log_queries_not_using_indexes identifica todas as consultas que realizam uma varredura completa, independentemente do seu tempo de execução bruto
• Deteção de regressões — comparar instantâneos do log antes e depois de uma implementação revela se o novo código introduziu padrões de consulta mais lentos
• Evidência para planeamento de capacidade — valores Rows_examined que são ordens de magnitude superiores a Rows_sent indicam índices em falta ou mal utilizados, que se agravam sob carga

MySQL vs. MariaDB: Comparação de Funcionalidades do Slow Query Log

Ambos os motores partilham a mesma infraestrutura central de slow query log herdada do MySQL 5.1, mas o MariaDB estendeu-a de várias formas significativas.

As opções log_slow_verbosity e log_slow_rate_limit no MariaDB são particularmente valiosas em ambientes de produção de alto rendimento onde o registo de cada consulta lenta poderia tornar-se em si uma sobrecarga de desempenho.

Passo 1: Localizar o Ficheiro de Configuração

O MySQL e o MariaDB leem a sua configuração a partir de caminhos predefinidos diferentes, dependendo da distribuição e do método de instalação.

MySQL:

/etc/my.cnf (baseado em RPM: RHEL, CentOS, AlmaLinux, Rocky Linux)
/etc/mysql/my.cnf (Debian/Ubuntu)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu com pacote mysql-server)

MariaDB:

/etc/my.cnf.d/server.cnf (baseado em RPM)
/etc/mysql/mariadb.conf.d/50-server.cnf (Debian/Ubuntu)
/etc/mysql/mariadb.cnf (layouts Debian mais antigos)

Se não tiver a certeza de qual ficheiro está ativo, consulte o processo em execução:
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
Isto imprime a lista ordenada exata de ficheiros que o daemon lê no arranque, incluindo quaisquer diretórios !includedir.
Abra o ficheiro de configuração principal com o seu editor preferido:
sudo nano /etc/my.cnf
Passo 2: Adicionar Diretivas do Slow Query Log a [mysqld]
Todos os parâmetros do slow query log pertencem à secção [mysqld]. Se a secção não existir, crie-a no início do ficheiro.
[mysqld]
# Core slow query log settings
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 1

# Log queries that skip index usage entirely
log_queries_not_using_indexes = 1

# Avoid flooding the log with index warnings on low-traffic tables
min_examined_row_limit  = 100

# Log slow administrative statements (ALTER TABLE, OPTIMIZE TABLE, etc.)
log_slow_admin_statements = 1
Descrição dos parâmetros:

slow_query_log = 1 — ativa a funcionalidade; defina como 0 para desativar sem remover o bloco
slow_query_log_file — caminho absoluto para o ficheiro de log; o utilizador do processo MySQL/MariaDB (mysql) deve ter acesso de escrita ao diretório pai
long_query_time = 1 — limite em segundos, aceita valores decimais (por exemplo, 0.5 para 500 ms); o valor predefinido de 10 segundos é quase sempre demasiado permissivo para aplicações web
log_queries_not_using_indexes — regista consultas de varredura completa independentemente de long_query_time; combine com min_examined_row_limit para suprimir ruído de tabelas pequenas
min_examined_row_limit — uma consulta deve examinar pelo menos este número de linhas antes de se qualificar para registo em log_queries_not_using_indexes; evita que pesquisas triviais de linha única poluam o log
log_slow_admin_statements — captura operações ao nível do esquema que bloqueiam tabelas e são frequentemente ignoradas como fontes de latência

Adições específicas do MariaDB que vale a pena ativar em produção:
# MariaDB only — extended per-query statistics in the log
log_slow_verbosity      = query_plan,explain

# MariaDB only — log only 1 in every N qualifying queries (rate limiting)
log_slow_rate_limit     = 10
log_slow_verbosity = query_plan,explain adiciona o plano de execução do otimizador diretamente em cada entrada do log, eliminando a necessidade de executar EXPLAIN manualmente após o facto — uma economia de tempo significativa ao diagnosticar consultas que apenas aparecem sob padrões de carga de produção.
Passo 3: Criar o Ficheiro de Log e Definir Permissões
Se o diretório de destino não existir, crie-o e atribua a propriedade antes de reiniciar o serviço. Ignorar este passo é uma das razões mais comuns pelas quais o slow query log falha silenciosamente ao ativar.
sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
Em sistemas com SELinux ativo (RHEL, CentOS, AlmaLinux), o contexto do ficheiro também deve ser definido corretamente:
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
A falha em definir o contexto SELinux correto faz com que o daemon arranque com sucesso mas ignore silenciosamente a escrita no ficheiro de log — um caso extremo frustrante que não produz nenhum erro óbvio em /var/log/messages.
Passo 4: Reiniciar o Serviço de Base de Dados
Aplique as alterações de configuração reiniciando o serviço. Em distribuições baseadas em systemd (o padrão em qualquer servidor Linux moderno):
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
Em sistemas mais antigos baseados em init.d:
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
Após o reinício, verifique se o serviço arrancou corretamente:
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
Qualquer configuração incorreta em my.cnf impedirá o arranque e aparecerá na saída do journal.
Passo 5: Ativar o Slow Query Log em Tempo de Execução (Sem Reinício)
Para servidores de produção onde um reinício é perturbador, o MySQL e o MariaDB suportam a ativação do slow query log dinamicamente através de SET GLOBAL. As alterações feitas desta forma têm efeito imediato, mas não persistem após um reinício do serviço, a menos que também sejam escritas em my.cnf.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL min_examined_row_limit = 100;
Esta é a abordagem correta para diagnósticos de emergência num sistema em produção — ative-o, capture uma amostra de 15 a 30 minutos durante o pico de tráfego e, em seguida, desative-o novamente sem tocar no ficheiro de configuração ou reiniciar o daemon.
Passo 6: Verificar a Configuração
Ligue-se ao cliente MySQL ou MariaDB:
mysql -u root -p
Em seguida, execute uma correspondência de padrão na tabela de variáveis do sistema:
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
Saída esperada para uma instância corretamente configurada:
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/slow-query.log |
+-------------------------------+-------------------------------+

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
Também pode confirmar que o log está a ser escrito verificando o contador de slow query:
SHOW GLOBAL STATUS LIKE 'Slow_queries';
Este contador incrementa sempre que uma consulta excede long_query_time, independentemente de o registo em ficheiro estar ativo — útil para confirmar que consultas lentas estão realmente a ocorrer antes de gastar tempo a analisar um ficheiro de log vazio.
Passo 7: Ler e Interpretar o Log em Bruto
Use tail para monitorizar o log em tempo real durante um teste de carga ou janela de pico de tráfego:
sudo tail -f /var/log/mysql/slow-query.log
Uma entrada típica do log tem este aspeto:
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: app_user[app_user] @ 10.0.1.45 []  Id: 1042
# Query_time: 4.561529  Lock_time: 0.000115  Rows_sent: 1  Rows_examined: 847293
# Bytes_sent: 512
SET timestamp=1697030723;
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
O que cada campo indica:

Query_time — tempo total de execução em segundos (tempo de relógio)
Lock_time — tempo gasto à espera de bloqueios de tabela ou linha; um rácio elevado de Lock_time para Query_time aponta para contenção, não para um índice em falta
Rows_sent — linhas devolvidas ao cliente
Rows_examined — linhas que o motor de armazenamento analisou para produzir o resultado; um rácio de Rows_examined / Rows_sent acima de 100:1 é um sinal forte de um índice em falta ou com baixa seletividade
Bytes_sent — presente na verbosidade estendida do MariaDB; útil para identificar consultas que devolvem conjuntos de resultados desnecessariamente grandes

No exemplo acima, a consulta examinou 847.293 linhas para devolver 1 linha. Adicionar um índice em customer_email reduziria Rows_examined para aproximadamente 1, reduzindo o tempo de execução de 4,5 segundos para sub-milissegundo.
Passo 8: Analisar o Log com mysqldumpslow e pt-query-digest
Ler o ficheiro de log em bruto é impraticável em escala. Duas ferramentas agregam e classificam as consultas lentas por impacto total.
Utilizar mysqldumpslow (Incluído com MySQL/MariaDB)
# Top 10 queries by total execution time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by average execution time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log
mysqldumpslow normaliza os parâmetros das consultas (substituindo valores literais por N ou S) para que consultas estruturalmente idênticas com valores de parâmetros diferentes sejam agrupadas — essencial para identificar padrões de alta frequência.
Utilizar pt-query-digest (Percona Toolkit — Recomendado para Produção)
# Install Percona Toolkit (Debian/Ubuntu)
sudo apt-get install percona-toolkit

# Install Percona Toolkit (RHEL/CentOS/AlmaLinux)
sudo yum install percona-toolkit

# Generate a full digest report
sudo pt-query-digest /var/log/mysql/slow-query.log

# Show only the top 5 queries by total time
sudo pt-query-digest --limit 5 /var/log/mysql/slow-query.log

# Output to a file for later review
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_query_report.txt
pt-query-digest produz um relatório classificado que mostra a impressão digital de cada consulta, o tempo total de execução, o tempo médio, a contagem de chamadas e a distribuição por percentil. É significativamente mais poderoso do que mysqldumpslow e é a ferramenta padrão utilizada por DBAs profissionais para análise de slow log.
Passo 9: Configurar a Rotação de Logs com logrotate
Sem rotação, o slow query log cresce indefinidamente. Num servidor ocupado com long_query_time definido para 1 segundo, o ficheiro pode atingir vários gigabytes em dias.
Crie uma configuração dedicada de logrotate:
sudo nano /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
Principais diretivas explicadas:

rotate 14 — retém 14 dias de arquivos comprimidos; ajuste com base no seu orçamento de disco e requisitos de auditoria
compress / delaycompress — comprime os ficheiros rotacionados com gzip, mas atrasa a compressão por um ciclo para evitar comprimir um ficheiro que o daemon ainda pode ter aberto
postrotate — executa mysqladmin flush-logs após a rotação, o que sinaliza ao daemon para fechar o identificador do ficheiro de log atual e abrir um novo; sem isto, o MySQL/MariaDB continua a escrever no ficheiro renomeado até ao próximo reinício

Force uma rotação manual para testar a configuração:
sudo logrotate -f /etc/logrotate.d/mysql-slow
Passo 10: Desativar o Slow Query Log Quando Já Não For Necessário
O registo contínuo de slow query com um limite baixo (por exemplo, 0,5 segundos) num servidor de alto tráfego adiciona uma sobrecarga de I/O mensurável. Desative-o assim que tiver recolhido dados suficientes:
Através do ficheiro de configuração (persistente):
[mysqld]
slow_query_log = 0
Em seguida, reinicie o serviço:
sudo systemctl restart mysqld   # or mariadb
Através de variável em tempo de execução (imediato, não persistente):
SET GLOBAL slow_query_log = 'OFF';
O método em tempo de execução é preferível durante as horas de produção — tem efeito em milissegundos sem qualquer tempo de inatividade.
Avançado: Utilizar performance_schema como Complemento
O slow query log captura consultas que excedem um limite de tempo. A tabela performance_schema events_statements_summary_by_digest captura estatísticas agregadas para cada padrão de consulta distinto, independentemente do tempo de execução. Utilizar ambos em conjunto fornece uma imagem completa.
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)     AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 6)     AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
Esta consulta identifica os 10 padrões de consulta mais demorados em todo o histórico de instruções — incluindo consultas rápidas que são executadas milhões de vezes e dominam coletivamente o tempo de CPU, o que o slow query log nunca capturaria.
Considerações sobre o Ambiente de Alojamento
O limite ideal de long_query_time depende muito do papel e do perfil de recursos do servidor:

Ambientes de alojamento partilhado — tipicamente sem acesso direto a my.cnf; use SET GLOBAL se o fornecedor de alojamento conceder privilégio SUPER ou SYSTEM_VARIABLES_ADMIN, ou solicite acesso ao slow log através do painel de controlo
Ambientes VPS — o acesso root completo significa controlo total sobre todos os parâmetros de configuração; uma instalação de VPS com cPanel expõe as definições do slow query log através do Editor de Configuração MySQL do WHM, que escreve diretamente em my.cnf

Se a sua pilha de aplicações incluir um frontend web gerido através de um Painel de Controlo VPS, correlacionar os timestamps do slow query log com os timestamps do log de acesso HTTP da sua aplicação é um método eficaz para rastrear a latência da base de dados até pedidos específicos voltados para o utilizador.

Matriz de Decisão Prática: Escolher o Limite Correto

Lista de Verificação Técnica e Principais Conclusões

Antes de concluir uma investigação de slow query, verifique cada um dos seguintes pontos:

• A secção [mysqld] em my.cnf contém slow_query_log = 1, um caminho slow_query_log_file válido e um long_query_time apropriado para o seu perfil de tráfego
• O ficheiro de log e o seu diretório pai são propriedade do utilizador do sistema mysql com permissões de escrita; em sistemas SELinux, o contexto do ficheiro está definido como mysqld_log_t

SHOW VARIABLES LIKE '%slow_query%' confirma slow_query_log = ON e o caminho correto do ficheiro após o reinício do serviço
SHOW GLOBAL STATUS LIKE 'Slow_queries' mostra um contador não nulo e a incrementar, confirmando que consultas qualificadas estão realmente a ocorrer
log_queries_not_using_indexes está ativado e combinado com min_examined_row_limit para evitar que pesquisas triviais de linha única inundem o log
log_slow_admin_statements está ativado para capturar ALTER TABLE, OPTIMIZE TABLE e operações DDL semelhantes que são fontes comuns de bloqueios de tabela inesperados
Uma configuração logrotate está em vigor com um hook postrotate que chama mysqladmin flush-logs

FAQ

A ativação do slow query log afeta o desempenho da base de dados?

Com um limite de 1 segundo ou superior numa carga de trabalho de produção típica, a sobrecarga é negligenciável — geralmente inferior a 1% do tempo total de execução das consultas. A sobrecarga torna-se mensurável apenas quando long_query_time é definido abaixo de 0,1 segundos ou quando log_queries_not_using_indexes está ativado num esquema com muitas tabelas pequenas sem índice. Use log_slow_rate_limit (MariaDB) ou aumente min_examined_row_limit para mitigar isto.

Posso ativar o slow query log sem reiniciar o MySQL ou o MariaDB?

Sim. Use SET GLOBAL slow_query_log = 'ON' e SET GLOBAL long_query_time = 1 a partir de qualquer sessão de cliente MySQL com privilégio SUPER ou SYSTEM_VARIABLES_ADMIN. A alteração tem efeito imediato. Escreva os mesmos valores em my.cnf para os tornar persistentes após reinícios.

Qual é a diferença entre Query_time e Lock_time no slow query log?

Query_time é o tempo total decorrido (tempo de relógio) desde que o servidor recebeu a consulta até enviar a última linha ao cliente. Lock_time é a parte desse total gasta à espera de adquirir bloqueios de tabela ou linha. Uma consulta com Lock_time próximo de Query_time é um problema de contenção de bloqueios, não um problema de índice — a solução envolve o design de transações ou a redução do âmbito de bloqueio, não a adição de índices.

Por que o meu ficheiro de slow query log está vazio mesmo que slow_query_log = ON?

As causas mais comuns são: (1) nenhuma consulta excedeu realmente long_query_time ainda — verifique com SHOW GLOBAL STATUS LIKE 'Slow_queries'; (2) o caminho do ficheiro de log não existe ou o utilizador mysql não tem permissão de escrita; (3) em sistemas SELinux, o contexto do ficheiro está incorreto; (4) a variável slow_query_log_file aponta para um caminho diferente do ficheiro que está a inspecionar — confirme com SHOW VARIABLES LIKE 'slow_query_log_file'.

Como encontro a consulta mais prejudicial no slow query log?

Execute pt-query-digest e ordene por R/Call (linhas examinadas por chamada) ou Response time (tempo cumulativo total). A consulta no topo do ranking Response time está a consumir o maior tempo agregado de base de dados e deve ser o primeiro alvo para análise EXPLAIN e otimização de índices. Se pt-query-digest não estiver disponível, use mysqldumpslow -s t -t 1 para extrair a consulta com o maior tempo total.