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

Resolução

Monitore o desempenho de seus recursos e bancos de dados

Use o Amazon CloudWatch para determinar quando sua consulta aumenta a utilização de recursos.

Monitore as seguintes CloudWatch Metrics:

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

Use o console do Amazon RDS para determinar quando a consulta degrada o desempenho do banco de dados. Além disso, verifique seu status da sua instância de banco de dados RDS para identificar processos ativos ou agendados que possam afetar o desempenho do banco de dados. Verifique os eventos do Amazon RDS que ocorrem quando o desempenho do seu banco de dados é ruim.

Para visualizar a lista de sistemas operacionais (SO) em sua workload e métricas do sistema, use o Monitoramento aprimorado. Por padrão, o intervalo do monitoramento aprimorado é de 60 segundos. É uma prática recomendada definir um intervalo de 1 a 5 segundos para pontos de dados mais detalhados.

Verifique a workload que contribui para o consumo de recursos

Depois de determinar os momentos em que a consulta aumenta a utilização de recursos ou degrada o desempenho, Ative o Insights de Performance para identificar as consultas que você deve otimizar.

Importante: O Insights de Performance chegará ao fim de sua vida útil em 30 de novembro de 2025. É possível fazer o upgrade para o modo Avançado do Database Insights antes de 30 de novembro de 2025. 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 suportará 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 atributos 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.

Para verificar sua workload, conclua as seguintes etapas:

1. Acesse o gráfico de carga do banco de dados no painel do Insights de Performance.
2. Verifique a coluna Carga por esperas (AAS) para determinar a workload que usa mais eventos de espera no momento em que sua utilização de recursos aumenta ou a consulta degrada o desempenho.
   Observação: Faixas coloridas mais grossas no gráfico de carga indicam os tipos de espera que mais contribuem para sua workload. Se sua workload exceder constantemente o vCPU máximo, modifique sua classe de instância.
3. Na guia Top SQL, pesquise as consultas individuais que contribuem para a workload.

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

Melhore seu desempenho de consulta

Use a instrução EXPLAIN

Verifique o plano de operação da consulta para determinar se sua consulta usa os índices apropriados. Use EXPLAIN para otimizar sua consulta e ver detalhes sobre como o MySQL executa a consulta. Para obter mais informações, consulte Optimizing Queries with EXPLAIN no site do MySQL.

Para evitar varreduras completas da tabela, execute instrução EXPLAIN para determinar se sua consulta usa um índice. Na saída EXPLAIN, verifique os nomes das tabelas, as chaves usadas e o número de linhas examinadas na consulta. Para mais informações, consulte EXPLAIN statement no site do MySQL.

Se a saída não mostrar chaves em uso, crie um índice nas colunas usadas na cláusula WHERE. Se a tabela tiver a indexação necessária, verifique se as estatísticas da tabela estão atualizadas. Quando as estatísticas estão atualizadas, o otimizador de consultas usa os índices mais seletivos com a cardinalidade correta. Como resultado, o desempenho da sua consulta melhora. Para obter mais informações, consulte a tabela INFORMATION_SCHEMA STATISTICS no site do MySQL.

Use ANALYZE TABLE

Mantenha suas estatísticas de consulta atualizadas com ANALYZE TABLE. Para obter mais informações, consulte ANALYZE TABLE Statement no site do MySQL.

Use EXPLAIN ANALYZE

Para o MySQL 8.0, use EXPLAIN ANALYZE. A instrução EXPLAIN ANALYZE mostra onde o MySQL aloca horários em sua consulta e por que esse tempo é alocado. Quando a consulta for concluída, EXPLAIN ANALYZE salva o plano e suas medidas. Para mais informações, consulte Obtaining information with EXPLAIN ANALYZE [Obter informações com EXPLAIN ANALYZE] no site do MySQL.

Verifique o comprimento da lista de histórico

O InnoDB usa o multi-version concurrency control (MVCC). O MVCC mantém várias cópias do mesmo log para preservar a consistência da leitura. O tamanho da lista de histórico é o número total de logs de desfazer que contêm modificações na lista de histórico. Quando há uma transação de longa duração que grava ou lê dados, o comprimento da lista de histórico aumenta até que a transação seja concluída ou revertida. Além disso, o comprimento da lista de histórico aumenta quando outras transações estão modificando as tabelas que a transação de longa duração usa.

É uma prática recomendada evitar transações abertas ou de longa duração no banco de dados. Em vez disso, aloque seus dados em pequenos lotes. No entanto, se sua workload exigir várias transações abertas ou de longa duração, espere um alto comprimento de lista de histórico no banco de dados.

Se você não monitorar o comprimento da sua lista de histórico, o desempenho poderá diminuir com o tempo. O alto comprimento da lista de histórico também pode causar alto consumo 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 comprimento da lista de histórico. Se os threads de limpeza não conseguirem acompanhar as mudanças no banco de dados, o comprimento da lista de histórico permanecerá alto. Em casos extremos, também é possível enfrentar uma interrupção no banco de dados.

Para verificar o comprimento da lista do histórico, execute o seguinte comando:

SHOW ENGINE INNODB STATUS;

Observação: A instrução SHOW ENGINE INNODB STATUS obtém informações sobre processamento de transações, eventos de espera e deadlocks. Para obter mais informações, consulte SHOW ENGINE Statement no site do MySQL.

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 comprimento da lista de histórico, conclua as seguintes etapas:

1. Abra o console do Amazon RDS.
2. No painel de navegação, escolha Insights de Performance e selecione o banco de dados para o qual você deseja visualizar as métricas.
3. Escolha a guia Métricas,
4. No menu do painel Métricas, escolha Painel personalizado.
5. Escolha Adicionar widget e, em seguida, pesquise e selecione a métrica Trx Rseg History Len.
6. Escolha Adicionar widget.

Se as gravações DML fizerem com que o comprimento da lista de histórico aumente, peça ao administrador do banco de dados que encerre as instruções de gravação.

Observação: Demora muito tempo para reverter as atualizações da transação interrompida.

Resolva consultas bloqueadas

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.

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 mais informações, consulte 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?

Use a instrução SHOW PROFILE

Crie um perfil de suas consultas mais lentas para encontrar o status em que a sessão passa mais tempo. Para obter mais informações, consulte SHOW PROFILE Statement no site do MySQL.

Use a instrução SHOW FULL PROCESSLIST

Execute a instrução SHOW FULL PROCESSLIST e use o Monitoramento aprimorado para revisar a lista de operações que você executa atualmente no servidor de banco de dados. Para obter mais informações, consulte SHOW PROCESSLIST Statement no site do MySQL.

Informações relacionadas

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