Por que minha consulta está lenta no Amazon RDS for MySQL?

Breve descrição

Para melhorar a performance da consulta, considere os seguintes fatores:

• Utilização de recursos (como CPU, memória e armazenamento)
• Análise da carga de trabalho
• Ajuste e monitoramento de consultas

Resolução

Utilização de recursos (como CPU, memória e armazenamento)

Para entender a causa primária de quaisquer problemas de performance do banco de dados, verifique todos os recursos de todo o servidor que sua instância está usando. Você pode monitorar sua carga de trabalho e pesquisar o momento em que a performance da consulta estava normal em comparação com o momento em que ela começou a demorar muito para ser executada.

Use as métricas do Amazon CloudWatch para monitorar esses recursos durante um período de tempo que inclui dias em que a performance foi considerada normal. Você também pode visualizar métricas de performance no console do Amazon RDS para monitorar a performance do banco de dados.

Você também pode verificar o status da instância para identificar outros processos ativos ou agendados que possam estar afetando a performance do banco de dados. No console do Amazon RDS, verifique os eventos que ocorreram enquanto o banco de dados estava com um desempenho precário.

Análise da carga de trabalho

Para analisar a carga de trabalho que contribui para o consumo de recursos, use o Performance Insights. O Performance Insights fornecerá uma análise gráfica de todas as suas consultas e esperas que contribuam para o aumento do consumo de recursos.

O Performance Insights usa a carga de trabalho como métrica principal em vez de usar o número de vCPUs para uma instância. Se a carga de trabalho atual excede o limite da vCPU, o servidor está sobrecarregado. Se o servidor estiver sobrecarregado, verifique as consultas que estão contribuindo para sua carga de trabalho e identifique maneiras de otimizar suas consultas. Em seguida, considere modificar sua classe de instância.

Sua carga de trabalho do Performance Insights também pode ser dividida em eventos em espera. Investigue as principais esperas que consomem recursos, dividindo a carga do banco de dados pelo número de eventos de espera. As faixas de cores mais grossas no gráfico de carga indicam os tipos de espera que mais contribuem para a carga de trabalho. Para obter mais informações, consulte Monitoramento da carga do banco de dados com o Performance Insights no Amazon RDS.

Você também pode usar o log de consulta lenta (habilitado em seu grupo de parâmetros personalizado) para identificar consultas de execução lenta.

Em seguida, você pode usar as métricas do Amazon CloudWatch para verificar se a quantidade de trabalho realizado em sua instância aumentou. Por exemplo:

• Conexões de banco de dados: o número de sessões do cliente que estão conectadas à instância de banco de dados.
• Taxa de transferência de recebimento de rede (MB/segundo): A taxa de tráfego de rede de e para a instância de banco de dados.
• Taxa de transferência de gravação e leitura: o número médio de megabytes lidos ou gravados em disco por segundo.
• Latência de gravação e leitura: O tempo médio para uma operação de leitura ou gravação em milissegundos.
• IOPS (leitura e gravação): O número médio de operações de leitura ou gravação de disco por segundo.
• Espaço de armazenamento livre (MB): A quantidade de espaço em disco não usada atualmente pela instância de banco de dados.

As métricas de latência indicam o tempo necessário para concluir a operação de E/S de disco de leitura ou gravação. A correlação das métricas de latência com o aumento de conexões de banco de dados ou métricas de transferência pode indicar que a carga de trabalho é o motivo da execução lenta da consulta. Para obter mais informações sobre como identificar fatores de uso, consulte Como posso visualizar o que está usando o armazenamento em uma instância de banco de dados do Amazon RDS que está executando o MySQL?

Você também pode usar o Monitoramento Aprimorado para recuperar a lista de sistemas operacionais envolvidos na carga de trabalho e as métricas do sistema subjacentes. Por padrão, o intervalo de monitoramento para o Monitoramento Avançado é de 60 segundos. É uma prática recomendada definir isso para intervalos de 1 a 5 segundos para pontos de dados mais granulares.

Otimização de consulta

Depois que a consulta de longa duração for identificada a partir de um log de consulta lento ou do Performance Insights, considere maneiras de melhorar o desempenho da consulta. Para ajustar uma consulta, considere as seguintes abordagens:

• Para encontrar os estados nos quais se gastou mais tempo, crie um perfil de suas consultas mais lentas. Para obter mais informações, consulte Instrução SHOW PROFILE no site do MySQL.
• Execute o comando SHOW FULL PROCESSLIST junto com o Monitoramento avançado. Quando usados juntos, você pode revisar a lista de operações que estão sendo executadas no servidor de banco de dados.
• Use o comando SHOW ENGINE INNODB STATUS para obter informações sobre processamento de transações, esperas e bloqueios.
• Encontre quaisquer consultas de bloqueio e resolva o bloqueio. Para mais informações, consultePor que uma consulta à minha instância de banco de dados do Amazon RDS for MySQL foi bloqueada quando não havia outra sessão ativa?
• Publique logs do MySQL no Amazon CloudWatch. Os registros de log são alternados a cada hora para manter 2% do limite de espaço de armazenamento alocado. Eles são então expurgados se tiverem mais de duas semanas de idade ou se o tamanho combinado exceder o limite de 2%.
• Defina um alarme do Amazon CloudWatch para que você possa monitorar o uso de recursos e ser alertado sempre que os limites forem excedidos.
• Encontre o plano de execução da consulta e verifique se a consulta está usando índices apropriados. Você pode otimizar sua consulta usando o plano EXPLAIN e revisar detalhes sobre como o MySQL executa a consulta.
• Mantenha suas estatísticas de consulta atualizadas com ainstrução de tabela ANALYZE. Os otimizadores de consulta às vezes podem escolher planos de execução ruins devido a estatísticas desatualizadas. Isso pode levar a uma baixa performance de consulta devido a estimativas de cardinalidade imprecisas em tabelas e índices.
• O MySQL 8.0 agora usa uma instrução EXPLAIN ANALYZE. A instrução EXPLAIN ANALYZE é uma ferramenta de criação de perfil para suas consultas que mostra onde o MySQL gasta tempo em sua consulta e por quê. Com o EXPLAIN ANALYZE, o MySQL planeja, instrumenta e executa a consulta enquanto conta linhas e mede o tempo gasto em vários pontos do plano de execução. Quando a consulta for concluída, EXPLAIN ANALYZE imprimirá o plano e suas medidas em vez do resultado da consulta.
• Esteja ciente de que No MySQL versão 8 as esperas de bloqueio estão listadas no Esquema de performance da tabeladata_lock_waits. Por exemplo:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

Para obter mais informações, consulteUsar transações do InnoDB e informações de bloqueio no site do MySQL.

---

Informações relacionadas

Ajustando o Amazon RDS for MySQL com Performance Insights

Como posso identificar e resolver a alta utilização de CPU em minhas instâncias de Amazon RDS for MySQL, MariaDB ou Aurora for MySQL?