MariaDB / Como configurar e ler o log de consultas lentas

O log de consultas lentas do MariaDB registra SQL que ultrapassa um limite de tempo ou atende a outras condições de registro ativadas. Para ler um arquivo, ative slow_query_log, inclua FILE em log_output e confira o limite e os filtros da conexão que executa a consulta. Valores em execução podem ser alterados por SQL sem reiniciar; para persistir após um reinício, salve-os em um arquivo de opções.

O que é registrado e o que preparar

O log ajuda a investigar o tempo de execução de SQL no servidor e as linhas examinadas. Ativá-lo não acelera automaticamente as consultas: use os dados para analisar planos de execução e índices. A resposta de uma página também inclui rede e aplicação, portanto não equivale ao tempo do SQL.

O limite habitual é long_query_time, em segundos: 1 significa um segundo. Uma condição de ausência de índice pode incluir consultas rápidas; limites de linhas, filtros e amostragem podem omitir consultas acima do limite. A visão geral do log de consultas lentas do MariaDB explica essas condições.

O guia considera um servidor Linux gerenciado por você. É preciso ter uma conta SQL administrativa e acesso do sistema para editar configurações. Variáveis globais exigem o privilégio correspondente na sua versão. Se houver acesso negado, use uma conta administrativa em vez de ampliar os privilégios da aplicação. Serviços gerenciados podem exigir grupos de parâmetros e visualizadores próprios.

O MariaDB 10.11 introduziu novos nomes para algumas variáveis; os antigos abaixo continuam como aliases. Os exemplos usam esses nomes conhecidos, cuja disponibilidade deve ser verificada no servidor real. A referência de variáveis do servidor MariaDB detalha escopo e versões.

Ativar sem reiniciar e verificar uma consulta de teste

Registrar valores e guardar os ajustes originais

Na autenticação local por socket comum no Ubuntu, conecte com sudo mariadb. Em outro ambiente, use o método configurado, como mariadb -u conta_administrativa -p. Registre versão, diretório de dados e valores globais. Um alias pode aparecer com seu nome novo na saída.

SELECT VERSION(), @@GLOBAL.datadir;
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'slow_query_log', 'log_slow_query',
  'slow_query_log_file', 'log_slow_query_file',
  'long_query_time', 'log_slow_query_time',
  'log_output', 'general_log', 'log_slow_rate_limit',
  'min_examined_row_limit', 'log_queries_not_using_indexes',
  'log_slow_filter', 'log_slow_disabled_statements'
);

Para desfazer um teste temporário, guarde estes valores globais na mesma conexão administrativa. As variáveis de usuário @saved_... só existem nessa conexão e desaparecem ao desconectar. Salve também os resultados em suas anotações e execute a restauração posterior nessa mesma conexão.

SET @saved_slow = @@GLOBAL.slow_query_log,
    @saved_file = @@GLOBAL.slow_query_log_file,
    @saved_output = @@GLOBAL.log_output,
    @saved_time = @@GLOBAL.long_query_time;

Ativar o arquivo e distinguir os valores de sessão

O exemplo usa saída em arquivo. Um nome relativo como mariadb-slow.log é resolvido dentro de @@GLOBAL.datadir. Se já existe um log em operação, você pode manter o caminho atual.

SET GLOBAL slow_query_log_file = 'mariadb-slow.log';
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = ON;

log_output também controla a saída do log geral de consultas. Se o servidor já usa TABLE, examine essa necessidade antes de aplicar o exemplo. Escolha FILE,TABLE se precisar dos dois destinos. NONE impede a saída mesmo quando o registro está ativo.

Valores globais são padrões para novas conexões; não reescrevem automaticamente o limite de cada sessão existente. Compare os dois escopos abaixo. Um pool pode reaproveitar conexões antigas: planeje reconexões ou ajustes de sessão. Consulte GLOBAL e SESSION em SHOW VARIABLES.

SELECT @@GLOBAL.long_query_time AS global_seconds,
       @@SESSION.long_query_time AS session_seconds,
       @@GLOBAL.slow_query_log AS global_log,
       @@SESSION.slow_query_log AS session_log;

Executar um teste controlado e restaurar a sessão

O teste salva os ajustes da sessão e define temporariamente um segundo, mínimo de zero linhas e amostragem de um. Ele limpa filtros apenas nessa sessão. SLEEP não examina linhas de tabela e poderia ser excluído por um mínimo positivo. Confira também se os tipos de instrução desativados não suprimem SELECT.

SET @session_log = @@SESSION.slow_query_log,
    @session_time = @@SESSION.long_query_time,
    @session_rows = @@SESSION.min_examined_row_limit,
    @session_rate = @@SESSION.log_slow_rate_limit,
    @session_filter = @@SESSION.log_slow_filter;
SET SESSION slow_query_log = ON;
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 0;
SET SESSION log_slow_rate_limit = 1;
SET SESSION log_slow_filter = '';
SELECT SLEEP(1.2) AS slow_log_probe_13708;

Uma chamada concluída normalmente retorna 0. Depois de terminar, o log deve conter slow_log_probe_13708, pois o tempo supera o limite. A duração real varia conforme a carga. Esse teste verifica o caminho de registro, não desempenho ou índices.

Após verificar o arquivo, restaure os ajustes da sessão na mesma conexão SQL. Como ela volta aos valores anteriores, confira separadamente a coleta real, por exemplo com uma nova conexão que herde os padrões globais desejados.

SET SESSION slow_query_log = @session_log;
SET SESSION long_query_time = @session_time;
SET SESSION min_examined_row_limit = @session_rows;
SET SESSION log_slow_rate_limit = @session_rate;
SET SESSION log_slow_filter = @session_filter;

Persistir a configuração no Ubuntu ou Rocky Linux

SET GLOBAL não substitui um arquivo de opções persistente. Identifique os arquivos e argumentos realmente usados pelo serviço. Examine a saída antes de compartilhar para não expor ajustes sensíveis.

sudo systemctl cat mariadb
sudo mariadbd --help --verbose
sudo mariadbd --print-defaults

1. Identifique e faça backup da configuração. Pacotes comuns do Ubuntu usam /etc/mysql/mariadb.conf.d/; no Rocky, /etc/my.cnf.d/. O guia de MariaDB do Rocky Linux também documenta esse último local. Após confirmar o caminho incluído, você pode criar 60-slow-log.cnf no Ubuntu ou slow-query.cnf no Rocky. Confira qualquer arquivo já existente com o mesmo nome antes de editar.
2. Adicione as opções de servidor abaixo. Procure arquivos posteriores e argumentos que substituam valores e resolva duplicações conflitantes. Um segundo é um ponto de partida, não uma meta universal.

[mariadb]
slow_query_log=ON
slow_query_log_file=mariadb-slow.log
log_output=FILE
long_query_time=1
log_slow_rate_limit=1
min_examined_row_limit=0

3. Reinicie em manutenção e confira os valores. Verifique com mariadbd --print-defaults se o arquivo é lido e reinicie como segue. Adapte o último comando à autenticação local e ao nome real do serviço.

sudo systemctl restart mariadb
sudo systemctl status mariadb --no-pager
sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'slow_query_log%'; SHOW GLOBAL VARIABLES LIKE 'long_query_time'; SHOW GLOBAL VARIABLES LIKE 'log_output';"

Abra uma nova conexão, confira caminho, limite, destino e valores de sessão e repita o teste. Outro caminho absoluto exige escrita para a conta do servidor e revisão do AppArmor no Ubuntu ou SELinux no Rocky. Começar com um nome relativo no diretório de dados existente evita novas regras para outra pasta. A configuração dos arquivos de opções do MariaDB explica a ordem de busca.

Ler o arquivo ou a tabela de log

Examinar o arquivo e resumir duração ou frequência

Use o caminho confirmado por SQL. Substitua /var/lib/mysql/ se seu diretório for diferente. tail -F acompanha o nome do arquivo e mostra novos registros; pressione Ctrl+C para encerrar a visualização.

sudo tail -n 80 /var/lib/mysql/mariadb-slow.log
sudo tail -F /var/lib/mysql/mariadb-slow.log

Localize a consulta de teste ou outra instrução no arquivo original e examine juntos Query_time, Lock_time, Rows_sent e Rows_examined. mariadb-dumpslow agrupa SQL semelhante em logs maiores. Se estiver ausente, confira os pacotes da distribuição; ele também pode estar instalado com o nome antigo mysqldumpslow.

sudo mariadb-dumpslow -s t -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s at -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s c -t 10 /var/lib/mysql/mariadb-slow.log

-s t ordena por tempo acumulado, -s at por tempo médio e -s c por contagem. -t 10 solicita os dez primeiros grupos. Números e textos são abstraídos por padrão para formar os grupos. Use o resumo para priorizar e depois examine o SQL original e o plano.

Quando a saída TABLE está configurada

Se log_output inclui TABLE ou FILE,TABLE, uma conta autorizada pode consultar mysql.slow_log. No exemplo apenas com FILE, a ausência de novas linhas na tabela não indica falha da gravação em arquivo.

SELECT start_time, query_time, lock_time,
       rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 20;

As capacidades e custos dos destinos são diferentes: a saída EXPLAIN estendida, por exemplo, só é gravada em arquivo. Não altere sem análise esse destino compartilhado; consulte como escrever logs do MariaDB em tabelas.

Escolher quais consultas investigar primeiro

Considere frequência e tempo acumulado, não apenas a execução individual mais lenta. Uma consulta de cinco segundos uma vez e outra de 0,2 segundo mil vezes têm prioridades diferentes pela média ou pelo total. Porém, um limite de um segundo omitiria a segunda pela condição temporal normal. Um log filtrado não representa toda a carga do banco.

Não adicione um índice apenas por causa de uma entrada do log. Uma varredura completa pode ser adequada para uma tabela pequena, e índices custam gravações e armazenamento. Compare planos antes e depois nas mesmas condições realistas. Diagnósticos como ANALYZE podem executar as instruções de fato: avalie antes carga e possíveis alterações.

Diagnosticar registros ausentes ou excessivos

• Ativo, mas vazio: confira NONE, destino arquivo/tabela, instância conectada e ativação e limite da sessão. Uma consulta em andamento pode aparecer somente após terminar.
• O teste não aparece: examine mínimo de linhas, log_slow_filter, instruções excluídas e log_slow_rate_limit. 1000 não significa registrar toda instrução elegível; use 1 na sessão de teste controlada.
• Consultas rápidas aparecem: verifique log_queries_not_using_indexes ou not_using_index no filtro. Aumentar o limite de tempo nem sempre elimina essa condição.
• Erro de acesso ao arquivo: confira conta do servidor, pastas superiores, negações AppArmor/SELinux e espaço livre. Os direitos do usuário do cliente não são os do processo servidor.

Adapte os caminhos destas verificações Linux. Em falhas de inicialização, examine caminhos negados e opções desconhecidas em vez de desligar toda a política de segurança.

sudo journalctl -u mariadb -b -n 100 --no-pager
sudo journalctl -k -b --no-pager
sudo ls -ld /var/lib/mysql
sudo ls -l /var/lib/mysql/mariadb-slow.log
df -h /var/lib/mysql

Encerrar a coleta, restaurar ajustes e gerenciar arquivos

As instruções seguintes desativam a coleta sem apagar registros existentes. Se o arquivo de opções ainda a ativa, o próximo reinício voltará a habilitá-la. Ajuste também a configuração persistente conforme o objetivo.

SET GLOBAL slow_query_log = OFF;
SET SESSION slow_query_log = OFF;

Se a mesma conexão administrativa que salvou os valores originais continua aberta, restaure os ajustes globais com este SQL. Após desconectar, não use variáveis de usuário desaparecidas; utilize os valores originais anotados separadamente. Confira sessões de aplicações existentes e arquivos de opções de forma independente.

SET GLOBAL slow_query_log = OFF;
SET GLOBAL slow_query_log_file = @saved_file;
SET GLOBAL log_output = @saved_output;
SET GLOBAL long_query_time = @saved_time;
SET GLOBAL slow_query_log = @saved_slow;

Para coleta prolongada, gerencie retenção, tamanhos e espaço. Apenas renomear um arquivo pode deixar o servidor escrevendo pelo identificador antigo. Revise a política logrotate existente do pacote e a reabertura. FLUSH SLOW LOGS fecha e reabre o log lento; não cria uma política de arquivamento, compressão ou exclusão. Exige o privilégio RELOAD correspondente.

FLUSH SLOW LOGS;

Alinhe a operação ao guia de rotação dos logs MariaDB no Linux e à referência FLUSH. Se o arquivo está no diretório de dados, confirme que a rotação abrange esse caminho. O SQL pode conter dados pessoais ou segredos: restrinja o acesso aos logs e backups e remova valores sensíveis antes de compartilhar.