Como soluciono problemas de consulta lenta e melhoro seu desempenho no Amazon RDS para MySQL?

Breve descrição

No Amazon RDS, os seguintes problemas podem causar lentidão no desempenho da consulta:

• Problemas de workload e utilização de recursos, como indexação inadequada e uso ineficiente do grupo de buffer
• Plano de execução de consulta ineficiente
• Contenção de recursos
• Transações de bloqueio

Para resolver esses problemas, analise as Amazon CloudWatch Metrics, Insights de Performance, Database Insights e Monitoramento aprimorado para identificar gargalos de desempenho. Em seguida, resolva o problema de gargalo e otimize o desempenho da consulta.

Resolução

Importante: o Insights de Performance chegará ao fim de sua vida útil em 30 de junho de 2026. É possível fazer o upgrade para o modo Avançado do Database Insights antes de 30 de junho de 2026. Se você não fizer o upgrade, os clusters de banco de dados que usam o Insights de Performance usarão como padrão o modo Padrão do Database Insights. Somente o modo Avançado do Database Insights será compatível com planos de execução e análises sob demanda. Se seus clusters usarem como padrão o modo Padrão, talvez você não consiga usar esses recursos no console. Para ativar o modo Avançado, consulte Ativação do modo Avançado do Database Insights para Amazon RDS e Ativação do modo Avançado do Database Insights para Amazon Aurora.

Observação: se você receber mensagens de erro ao executar comandos da AWS Command Line Interface (AWS CLI), consulte Solução de problemas da AWS CLI. Além disso, verifique se você está usando a versão mais recente da AWS CLI.

Monitore o desempenho de seus recursos e bancos de dados

Para solucionar problemas de desempenho da sua consulta, analise as Amazon CloudWatch Metrics para identificar a causa do problema. Para identificar quando uma consulta aumenta o uso de um recurso específico ou diminui o desempenho do banco de dados, use o console do CloudWatch ou a AWS CLI para monitorar as seguintes métricas:

• DatabaseConnections
• NetworkReceiveThroughput
• WriteThroughput
• ReadThroughput
• WriteLatency
• ReadLatency
• WriteIOPS
• ReadIOPS
• FreeStorageSpace
• BurstBalance

Se o desempenho do seu banco de dados for ruim, verifique o status da instância de banco de dados do RDS para ver se há processos ativos ou programados que possam afetar o desempenho. Além disso, analise seus eventos do Amazon RDS para ver se há eventos que possam afetar o desempenho do banco de dados.

Analise seu workload e utilização de recursos

Se o desempenho da sua consulta estiver lento, consulte as outras consultas em seu workload para ver se elas afetam o desempenho da sua consulta. Para identificar as consultas que você deve otimizar, é possível ativar o modo Avançado do Database Insights para Amazon RDS ou Amazon Aurora.

Se sua instância for reiniciada, sua instância de banco de dados poderá perder dados em cache e diminuir o desempenho da consulta. Para evitar esse problema de cache frio, configure os seguintes parâmetros para acelerar o grupo de buffer de aquecimento após uma reinicialização:

• innodb_buffer_pool_dump_at_shutdown
• innodb_buffer_pool_load_at_startup
• innodb_buffer_pool_dump_pct

Para otimizar o desempenho da consulta, é uma prática recomendada monitorar o quanto sua instância de banco de dados utiliza o grupo de buffer do InnoDB. Para obter mais informações, consulte Buffer pool (Grupo de buffer) no site do MySQL. Para monitorar o status do grupo de buffer do InnoDB, analise os seguintes contadores de banco de dados do Insights de Performance:

• Para saber o número de solicitações de leitura lógica, analise o contador Innodb_buffer_pool_read_requests.
• Para saber o número de leituras lógicas que o InnoDB não consegue satisfazer no grupo de buffer e precisou ler diretamente do disco, consulte Innodb_buffer_pool_reads.
• Use Innodb_buffer_pool_hit_ratio para a porcentagem de leituras que o InnoDB pode satisfazer a partir do grupo de buffer.
• Analise Innodb_buffer_pool_usage para saber a porcentagem do grupo de buffer do InnoDB que contém páginas de dados.

Para identificar consultas de execução lenta, também é possível ativar slow_query_log em seu grupo de parâmetros e publicar os logs no CloudWatch Logs.

Otimize o desempenho da sua consulta

Para otimizar o desempenho da sua consulta, execute os seguintes comandos com base nas necessidades do seu plano de execução de consulta. Para obter mais informações, consulte EXPLAIN output format (formato de saída de EXPLAIN) no site do MySQL.

Use EXPLAIN para otimizar suas consultas

Para ver detalhes sobre o desempenho da sua consulta e por que ela pode estar atrasada, execute o comando EXPLAIN. Para obter mais informações, consulte Optimizing Queries with EXPLAIN (Otimizando consultas com EXPLAIN) no site do MySQL.

Para saber se sua consulta usa um índice, execute a consulta EXPLAIN. Na saída de EXPLAIN, analise os nomes das tabelas, as chaves que estão em uso e o número de linhas que a consulta examinou. Para obter mais informações, consulte EXPLAIN statement (Declaração EXPLAIN) no site do MySQL. Analise a saída e, em seguida, realize as seguintes ações:

• Se a saída não mostrar as chaves em uso, crie um índice nas colunas na cláusula WHERE.
• Se a tabela tiver a indexação necessária, verifique se as estatísticas da tabela estão atualizadas. Para obter mais informações, consulte The INFORMATION_SCHEMA STATISTICS table (A tabela INFORMATION_SCHEMA STATISTICS) no site do MySQL.

Use ANALYZE TABLE para atualizar estatísticas da sua consulta

Se as estatísticas da sua tabela não estiverem atualizadas, a consulta poderá ter um desempenho ruim. Para atualizar as estatísticas da sua consulta, execute o comando ANALYZE TABLE. Para obter mais informações, consulte ANALYZE TABLE statement (Declaração ANALYZE TABLE) no site do MySQL.

Use EXPLAIN ANALYZE para ver como suas consultas alocam tempo

Para identificar qual parte da execução da consulta está lenta, execute a consulta EXPLAIN ANALYZE para ver como o MySQL aloca tempo em sua consulta. Quando a consulta for concluída, EXPLAIN ANALYZE salva o plano e suas medidas. Para obter mais informações, consulte Obtaining information with EXPLAIN ANALYZE (Obter informações com EXPLAIN ANALYZE) no site do MySQL. Também é possível usar SHOW PROFILE para criar um perfil de suas consultas mais lentas e encontrar o status em que a sessão passa mais tempo. Para obter mais informações, consulte SHOW PROFILE statement (Declaração SHOW PROFILE) no site do MySQL.

Use SHOW FULL PROCESSLIST e Monitoramento aprimorado para analisar as operações

Execute o comando SHOW FULL PROCESSLIST para visualizar a lista de operações que são executadas no servidor do banco de dados. Também é possível usar o Monitoramento aprimorado para analisar essa lista. Para obter mais informações, consulte SHOW PROCESSLIST statement (Declaração SHOW PROCESSLIST) no site do MySQL.

Verifique o tamanho da lista de histórico

O sistema de transações do InnoDB mantém o Controle de simultaneidade de várias versões (Multi-Version Concurrency Control, MVCC). Se seu workload exigir várias transações abertas ou de longa duração, espere um grande tamanho da lista de histórico no banco de dados. É uma prática recomendada evitar transações abertas ou de longa duração no banco de dados. Para obter mais informações, consulte O tamanho da lista de histórico do InnoDB aumentou significativamente.

Se você não monitorar o tamanho da sua lista de histórico, seu desempenho pode diminuir com o tempo. O grande tamanho da lista de histórico também pode causar alta utilização de recursos, desempenho lento e inconsistente da instrução SELECT e aumento do armazenamento.

Observação: as transações de longa duração não são a única causa dos picos no tamanho da lista de histórico. Se os threads de limpeza não conseguirem acompanhar as mudanças no banco de dados, o tamanho da lista de histórico permanecerá grande. Em casos extremos, também é possível ocorrer uma interrupção no banco de dados.

Use o comando SHOW ENGINE INNODB STATUS para obter informações sobre processamento de transações, eventos de espera e deadlocks. Para obter mais informações, consulte SHOW ENGINE Statement (Declaração SHOW ENGINE) no site do MySQL. Execute a consulta SHOW ENGINE INNODB STATUS para verificar o tamanho da sua lista de histórico:

SHOW ENGINE INNODB STATUS;

Exemplo de saída:

\------------ TRANSACTIONS ------------Trx id counter 26368570695  Purge done for
 trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

Para usar o Insights de Performance para verificar o tamanho da lista de histórico, conclua as seguintes etapas:

1. Abra o console do Amazon RDS.
2. No painel de navegação, clique em Insights de Performance e selecione o banco de dados do qual você deseja visualizar as métricas.
3. Selecione Métricas.
4. Na página Métricas do painel, selecione Painel personalizado.
5. Clique em Adicionar widget e, em seguida, selecione a métrica Trx Rseg History Len.
6. Clique em Adicionar widget.

Observação: se as gravações em linguagem de manipulação de dados (Data Manipulation Language, DML) aumentarem o tamanho da lista de histórico, peça ao administrador do seu banco de dados que encerre as consultas de gravação.

Resolva consultas bloqueadas

Se sua consulta for executada por um longo período de tempo, uma consulta diferente pode estar bloqueando sua consulta. No MySQL 8.0, é possível encontrar esperas de bloqueio no esquema de desempenho da tabela data_lock_waits. Para obter mais informações, consulte Using InnoDB transaction and locking information (Usar informações de transação e bloqueio no InnoDB) no site do MySQL. Execute a consulta a seguir para identificar transações de bloqueio:

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;

Informações relacionadas

Como soluciono problemas de uma instância do RDS para MySQL ou MariaDB que mostra armazenamento cheio?

Por que uma consulta a uma instância de banco de dados do Amazon RDS para MySQL foi bloqueada quando não há outra sessão ativa?