Como habilitar o registro de consultas lentas no MySQL ou MariaDB

À medida que os bancos de dados crescem e se tornam um componente central dos aplicativos da Web, o ajuste de desempenho torna-se fundamental. No MySQL® e no MariaDB, uma das maneiras mais eficazes de diagnosticar gargalos de desempenho é ativar o registro de consultas lentas. Esse registro ajuda a identificar consultas que demoram muito para serem executadas, permitindo otimizá-las e melhorar o desempenho geral do seu banco de dados.

Neste guia, explicaremos o que é o log de consultas lentas, por que ele é importante e como ativá-lo e configurá-lo no MySQL e no MariaDB.

O que é o registro de consultas lentas?

O log de consultas lentas é um arquivo de log que registra as consultas SQL que demoram mais do que um determinado período de tempo (o limite) para serem executadas. Por padrão, esse limite é definido como 10 segundos, mas pode ser personalizado. As consultas que excedem esse limite de tempo são registradas, juntamente com informações como o tempo que a consulta levou e a data e hora da execução. Essa é uma ferramenta essencial para que os desenvolvedores e administradores de banco de dados identifiquem e otimizem as consultas de execução lenta, ajudando a melhorar o desempenho do banco de dados.

Por que ativar o registro de consultas lentas?

A ativação do registro de consultas lentas ajuda a:

Identificar gargalos de desempenho: As consultas lentas podem afetar significativamente o desempenho do seu aplicativo. O log ajuda a identificar essas consultas problemáticas.
Otimização: Ao examinar as consultas lentas, você pode identificar as áreas em que os índices, a reestruturação de consultas ou o armazenamento em cache podem melhorar o desempenho.
Monitorar o desempenho da consulta: Para o ajuste contínuo do desempenho, o log de consultas lentas fornece insights sobre como os tempos de execução das consultas mudam com o tempo.

Como ativar o registro de consultas lentas no MySQL ou MariaDB

A ativação do log de consultas lentas envolve a modificação do arquivo de configuração e o ajuste de algumas configurações diretamente na instância do MySQL/MariaDB. Veja a seguir como fazer isso passo a passo.

Etapa 1: acessar o arquivo de configuração do MySQL/MariaDB

Os arquivos de configuração do MySQL e do MariaDB geralmente estão localizados em:

Para MySQL: /etc/my.cnf ou /etc/mysql/my.cnf
Para o MariaDB: /etc/my.cnf.d/server.cnf ou /etc/mysql/mariadb.cnf

Use seu editor de texto preferido para abrir o arquivo de configuração. Por exemplo, com o nano, você pode executar o seguinte comando:

sudo nano /etc/my.cnf

Etapa 2: Modificar o arquivo de configuração

No arquivo de configuração, localize a seção [mysqld] seção. Se ela não existir, você pode criá-la. Adicione ou modifique as seguintes linhas para ativar o registro de consultas lentas:

[mysqld]
 slow_query_log = 1
 slow_query_log_file = /var/log/mysql/slow-query.log
 long_query_time = 2

slow_query_log = 1: Ativa o registro de consulta lento.
slow_query_log_file: Especifica o local onde o arquivo de registro de consulta lenta será salvo. Você pode definir um caminho e um nome de arquivo personalizados, mas certifique-se de que o diretório tenha as permissões de gravação adequadas para o MySQL/MariaDB.
long_query_time = 2: define o limite do que se qualifica como uma consulta “lenta”. Nesse caso, qualquer consulta que demore mais de 2 segundos será registrada. Você pode ajustar esse valor de acordo com suas necessidades de desempenho.

Etapa 3: reiniciar o serviço MySQL/MariaDB

Depois de salvar as alterações no arquivo de configuração, reinicie o serviço MySQL ou MariaDB para que as alterações tenham efeito.

Para sistemas que usam systemd:

sudo systemctl restart mysqld # For MySQL
 sudo systemctl restart mariadb # For MariaDB

Para sistemas que usam o init.d:

sudo service mysqld restart # For MySQL
 sudo service mariadb restart # For MariaDB

Etapa 4: Verificar se o registro de consultas lentas está ativado

Para verificar se o registro de consulta lento está ativado, faça login no cliente MySQL/MariaDB executando:

mysql -u root -p

Uma vez conectado, execute o seguinte comando:

SHOW VARIABLES LIKE 'slow_query_log';

Você deverá ver algo parecido com isto:

+-----------------+-------+
 | Variable_name   | Value |
 +-----------------+-------+
 | slow_query_log  | ON    |
 +-----------------+-------+

Isso confirma que o registro de consulta lenta está ativado.

Etapa 5: análise do registro de consultas lentas

O log de consultas lentas registra as consultas no arquivo especificado na configuração. Para visualizar o conteúdo do log de consultas lentas, você pode usar um visualizador de texto como less, cat ou tail:

sudo less /var/log/mysql/slow-query.log

Você verá entradas semelhantes às seguintes:

# Time: 2024-10-11T12:45:23.489187Z
 # User@Host: root[root] @ localhost []
 # Query_time: 4.561529 Lock_time: 0.000115 Rows_sent: 1 Rows_examined: 50000
 SET timestamp=1697030723;
 SELECT * FROM large_table WHERE column = 'value';

O registro fornece detalhes como o tempo de execução da consulta, o tempo de bloqueio, o número de linhas enviadas e examinadas e a consulta SQL real.

Configuração opcional do registro de consultas lentas

Há outras opções de configuração que podem ser definidas para adaptar o log de consultas lentas às suas necessidades específicas.

1. Consultas de registro sem índices

Você também pode ativar o registro em log de consultas que não usam índices, que geralmente são uma fonte de problemas de desempenho. Adicione a seguinte linha à sua configuração do MySQL/MariaDB:

log_queries_not_using_indexes = 1

Isso registrará qualquer consulta que não use um índice, ajudando-o a identificar e otimizar as consultas com baixo desempenho.

2. Limitar o tamanho do arquivo de registro

Para evitar que o log de consultas lentas fique muito grande, você pode rotacionar os logs ou limitar seu tamanho. Nos sistemas Linux, você pode usar o logrotate para gerenciar o tamanho e a rotação dos arquivos de log do MySQL/MariaDB.

Aqui está um exemplo básico para /etc/logrotate.d/mysql:

/var/log/mysql/slow-query.log {
 daily
 rotate 7
 missingok
 notifempty
 compress
 delaycompress
 postrotate
 /usr/bin/systemctl reload mysql >/dev/null 2>&1 || true
 endscript
 }

Essa configuração rotaciona o arquivo de log diariamente e mantém os últimos sete logs, garantindo que o lento log de consultas não consuma muito espaço em disco.

Etapa 6: Desativar o registro de consulta lento

Depois de identificar e otimizar as consultas lentas, talvez você queira desativar o log de consultas lentas para economizar recursos. Para fazer isso, edite o arquivo de configuração novamente e defina slow_query_log como 0:

[mysqld]
 slow_query_log = 0

Reinicie o serviço MySQL ou MariaDB para aplicar as alterações.

Conclusão

O registro de consultas lentas é uma ferramenta inestimável para o ajuste de desempenho do banco de dados no MySQL e no MariaDB. Ao registrar as consultas que demoram muito para serem executadas, é possível identificar os gargalos e otimizar o desempenho das consultas. Quer esteja gerenciando um banco de dados pequeno ou um aplicativo corporativo de grande escala, ativar o log de consultas lentas é uma maneira proativa de garantir que seus bancos de dados permaneçam rápidos e eficientes.

Seguindo as etapas deste guia, você poderá ativar, configurar e analisar o log de consultas lentas para otimizar o desempenho do banco de dados MySQL ou MariaDB de forma eficaz.