Consultas de longa duração podem ser um sério obstáculo ao desempenho do seu banco de dados MySQL, causando tudo, desde tempos de resposta lentos até gargalos completos que afetam todos os usuários. Controlar essas consultas incômodas - saber o que são, por que acontecem e como gerenciá-las - é a chave para manter seu banco de dados funcionando perfeitamente.

Seja detectando-os antecipadamente, interrompendo-os ou configurando uma maneira de lidar com eles automaticamente, este guia ajuda você.

O que é uma consulta de longa duração?

Uma consulta de longa duração no MySQL é uma consulta que leva um período excepcionalmente longo para ser executada.
A duração específica que classifica uma consulta como "de longa duração" pode variar, dependendo dos padrões de desempenho do seu aplicativo. Geralmente, se uma consulta estiver sendo executada por mais tempo do que o normal e começar a deixar seu banco de dados lento, ela será considerada de longa duração.

Causas raízes de consultas de longa duração

As causas de consultas de longa duração podem ser diversas:

• Falta de indexação adequada – Sem indexação apropriada, o MySQL deve varrer a tabela inteira para recuperar os dados necessários. Este processo é altamente ineficiente, especialmente para tabelas grandes, pois consome tempo e recursos substanciais.

• Situações de carga pesada – Quando o servidor lida com um grande volume de consultas ou processa algumas consultas complexas simultaneamente, os recursos disponíveis (como CPU e memória) são sobrecarregados. Essa competição por recursos pode atrasar a execução de consultas, levando a tempos de execução mais longos, especialmente durante períodos de pico de uso.

• Contenção de bloqueio – Isso ocorre quando várias transações exigem acesso aos mesmos dados simultaneamente, mas são bloqueadas porque outras operações mantêm os bloqueios necessários. Por exemplo, se uma transação estiver atualizando uma linha, outra transação que queira ler ou atualizar a mesma linha terá que esperar até que a primeira seja concluída e libere o bloqueio.

• Normalização inadequada – Embora a normalização ajude a evitar a redundância de dados e melhore a integridade dos dados, bancos de dados excessivamente normalizados podem levar a consultas complexas envolvendo múltiplas junções. Isso pode degradar o desempenho. Por outro lado, a subnormalização pode levar à duplicação excessiva de dados, resultando em tabelas maiores e consultas mais lentas.

• Junções Grandes – Consultas que envolvem a união de tabelas grandes, especialmente sem índices adequados, podem ser lentas. O banco de dados deve corresponder as linhas nas tabelas com base nas condições de junção, um processo que pode consumir muitos recursos e ser lento sem uma indexação eficiente.

Identificando consultas de longa duração

Para gerenciar com eficácia consultas de longa duração, primeiro você precisa identificá-las. Aqui estão alguns métodos:

1. Usando SHOW PROCESSLIST

O MOSTRAR LISTA DE PROCESSOS; O comando é uma maneira rápida de obter um instantâneo de todas as consultas ativas em execução no seu servidor. Este comando exibe cada consulta junto com diversas informações importantes, incluindo há quanto tempo cada consulta está em execução. Aqueles com um valor alto de "Tempo" são provavelmente suas consultas de longa duração. Veja como você pode usar este comando:

MOSTRAR LISTA DE PROCESSOS COMPLETA;

Este comando listará todos os processos atuais, mostrará quem os iniciou, que tipo de comando eles estão executando e, principalmente, há quanto tempo estão nele. Se você detectar alguma consulta que esteja em execução há muito tempo, essas são suas consultas de longa duração. Você pode então decidir se deseja se aprofundar na otimização deles ou simplesmente eliminá-los se eles estiverem prejudicando o desempenho do seu sistema.

2. O log de consulta lenta

Configurar o log de consultas lentas é outra ótima estratégia para capturar essas consultas problemáticas. Este prático recurso do MySQL registra qualquer consulta que demore mais para ser executada do que um determinado limite. Não se trata apenas de capturar consultas de longa duração – também pode ajudá-lo a identificar consultas que não estão usando índices de forma eficiente.

Para colocar o log de consulta lenta em funcionamento, você precisará ajustar algumas configurações em seu arquivo de configuração do MySQL (my.cnf ou my.ini):

• Slow_query_log – Defina como 1 para ativar o log.
• Slow_query_log_file – Especifique o caminho do arquivo onde deseja salvar o log.
• Long_query_time – Defina o tempo mínimo de execução (em segundos) que qualifica uma consulta a ser registrada. Por exemplo, defini-lo como ‘2’ registrará quaisquer consultas que levem mais de dois segundos para serem executadas.

3. Esquema de Desempenho

O esquema de desempenho do MySQL é inestimável para uma investigação mais detalhada. Esta ferramenta foi projetada para monitorar eventos do servidor e rastrear métricas de desempenho, proporcionando uma visão mais clara da execução da consulta e do desempenho geral do sistema.

Certifique-se de que esteja habilitado em sua configuração do MySQL adicionando a seguinte linha:

[mysqld]
esquema_desempenho = ATIVADO

Depois de ativado, você pode explorar uma variedade de tabelas de esquema de desempenho para analisar o desempenho de suas consultas. Por exemplo, se você deseja identificar consultas de longa duração, você pode querer dar uma olhada na tabela events_statements_history_long. Veja como você pode consultá-lo:

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS 'Duração (segundos)'
DE performance_schema.events_statements_history_long
ONDE TIMER_WAIT > 10000000000;

Esta consulta ajuda você a encontrar qualquer consulta que esteja em execução há mais de 10 segundos. Fornece detalhes como o texto SQL e há quanto tempo cada consulta está em execução.

Eliminando manualmente consultas de longa duração

Quando você identifica uma consulta que está demorando muito e sobrecarregando os recursos do seu sistema, você tem a opção de encerrá-la manualmente. Isso é feito usando o comando KILL seguido do ID do processo específico da consulta.

Você pode encontrar o ID do processo executando o comando SHOW PROCESSLIST, que exibe todos os processos em execução no momento e seus respectivos IDs. Procure na lista quaisquer consultas que mostrem um valor alto de "Tempo", que indica há quanto tempo elas estão em execução.

Depois de identificar uma consulta problemática e anotar seu ID de processo, você pode encerrá-la usando o comando KILL:

MATAR [ID do processo];

Substitua [ID do processo] pelo número real da saída SHOW PROCESSLIST.

Tenha cuidado com essa abordagem. A interrupção abrupta de uma consulta às vezes pode causar problemas, como deixar seus dados em um estado inconsistente se a consulta estiver no meio da gravação ou atualização de informações.

Como automatizar a eliminação de consultas de longa duração

Configurar a automação para lidar com consultas de longa duração pode ser um verdadeiro salva-vidas, evitando que consultas lentas ou não otimizadas sobrecarreguem os recursos do banco de dados e tornem lento ou até mesmo travem todo o sistema. Mas tenha cuidado: usar esta ferramenta sem as verificações corretas pode, na verdade, ocultar problemas de desempenho mais profundos que precisam de sua atenção.

Sempre garanta que você tenha registro e monitoramento abrangentes para analisar o impacto das consultas eliminadas em seu aplicativo e considere melhorar essas consultas em vez de apenas eliminá-las automaticamente. Pense na rescisão automática como parte de uma estratégia maior para otimizar o desempenho, não como uma solução que resolve tudo.

1. Habilite o Agendador de Eventos

Primeiro, você precisa habilitar o MySQL Event Scheduler, que está desabilitado por padrão. O Agendador de Eventos permite criar e agendar tarefas que você deseja que o servidor execute automaticamente em horários predefinidos. Execute o seguinte comando:

SET GLOBAL event_scheduler = ON;

2. Crie um evento para eliminar consultas de longa duração

Com o agendador habilitado, a próxima etapa é definir o evento real que monitorará e eliminará as consultas de longa duração. O evento será executado a cada minuto para verificar se há consultas em execução por mais tempo do que um limite especificado (digamos, 60 segundos). Uma vez identificado, ele eliminará automaticamente essas consultas. Aqui está um detalhamento do código SQL para configurar este evento:

`CRIAR EVENTO kill_long_running_queries
NO AGENDAMENTO A CADA 1 MINUTO - Especifica a frequência com que o evento ocorre
FAZER
COMEÇAR
DECLARE concluído INT DEFAULT FALSE;
DECLARE proc_idINT; -- Variável para armazenar o ID do processo de cada consulta
DECLARE CURSOR cur1 PARA SELECIONAR ID DE information_schema.processlist
WHERE Comando = 'Consulta' AND Tempo > 60; -- Altere '60' para seu limite em segundos
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

read_loop: LOOP
BUSCAR cur1 EM proc_id;
SE feito ENTÃO
DEIXE read_loop;
TERMINAR SE;
MATAR proc_id; -- Elimina o processo identificado por proc_id
FIM DO CICLO;

FECHAR cur1;
FIM;`

3. Configure o tempo máximo de execução de consulta no MySQL

Controlar o tempo máximo de execução de uma consulta ajuda a evitar que o banco de dados fique preso por consultas de execução excessivamente longa. Isso é feito usando a variável de sistema max_execution_time no MySQL 5.7.8 e versões posteriores, definindo um limite de tempo de execução em todo o sistema para todas as consultas SELECT somente leitura:

DEFINIR GLOBAL max_execution_time = 2000;

Isso define o limite para 2.000 milissegundos (2 segundos)

Lembre-se, esta configuração não se aplica a procedimentos armazenados, funções ou gatilhos e é redefinida para o padrão na reinicialização do servidor, a menos que seja adicionada ao seu arquivo de configuração do MySQL:

[mysqld]
tempo_máximo_de_execução = 2000

4. Configure o tempo máximo de instrução no MariaDB

MariaDB, embora bifurcado do MySQL, oferece uma abordagem semelhante, mas distinta, para gerenciar tempos de execução de consultas. A partir do MariaDB 10.1.1, você pode usar a variável de sistema max_statement_time para esta finalidade:

SET GLOBAL max_statement_time = 2;

Isso limita o tempo de execução a 2 segundos para todas as consultas.

Para configuração persistente por meio de reinicializações do servidor, adicione esta linha ao seu arquivo de configuração MariaDB:

[mysqld]
max_statement_time = 2

Identifique e solucione consultas de longa duração com Query Analytics da Releem

A ferramenta de análise de consulta do Releem revoluciona a forma como você monitora e otimiza o desempenho do seu banco de dados. Ele coleta automaticamente informações detalhadas sobre as 100 principais consultas, fornecendo métricas importantes como o tempo médio de execução e o impacto geral de cada consulta na eficiência operacional do seu banco de dados.

Com Releem, não há necessidade de vasculhar manualmente a saída PROCESSLIST ou examinar o log de consultas lentas para identificar consultas com baixo desempenho. A ferramenta apresenta um painel intuitivo que permite classificar e identificar facilmente consultas que estão atrasadas ou consumindo muito tempo. Essa visão imediata ajuda você a identificar e resolver gargalos rapidamente.