Como soluciono problemas de uma declaração SELECT de execução lenta em meu cluster de banco de dados compatível com o Aurora MySQL?

Breve descrição

Sua declaração SELECT pode ser executada lentamente em seu cluster de banco de dados compatível com o Aurora MySQL pelos seguintes motivos:

• Você está usando demais os recursos do sistema de banco de dados.
• O banco de dados está bloqueado.
• A declaração SELECT faz varreduras completas de tabelas em tabelas grandes. Ou a consulta não tem os índices necessários.
• As transações de longa duração estão aumentando o tamanho da lista de histórico (HLL) do InnoDB.

Resolução

Observação: se você receber erros 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.

Use o CloudWatch Database Insights para verificar por que uma declaração SELECT é lenta

Ative o Insights de Performance e use o Database Insights para detectar consultas que causam alta carga de banco de dados. Monitore dimensões como SQL, Usuários e Esperas no gráfico de carga do banco de dados. Monitore também a seção Consultas SQL lentas da guia Telemetria do banco de dados. Além disso, é possível analisar a execução de consultas a partir das estatísticas SQL do Insights de Performance. Por exemplo, se o número de linhas que você examinou por chamada for maior que o normal, o plano de execução será ineficiente.

Use métricas para monitorar seus recursos de sistemas de instância de banco de dados

Alta CPU, pouca memória ou grandes workloads que excedem os recursos da sua classe de instância de banco de dados podem fazer com que sua declaração SELECT seja executada lentamente. Para monitorar seus recursos de instância de banco de dados, use as seguintes ferramentas:

• Use as Amazon CloudWatch Metrics para o Amazon Aurora para monitorar a utilização da CPU.
• Use o Monitoramento aprimorado para visualizar detalhes das métricas do sistema operacional (SO).
• Use os dados de processos do sistema operacional na guia Telemetria do banco de dados para verificar se sua carga de banco de dados excede a vCPU máxima.

As buscas de disco podem fazer com que a declaração SELECT seja executada lentamente. Para minimizar a E/S do disco, o mecanismo de banco de dados armazena em cache o bloco lido do disco. Quando o banco de dados precisa dos mesmos dados, ele os busca na memória em vez do disco. Para determinar se você atende uma consulta específica do disco ou da memória, use as seguintes métricas:

• Verifique a métrica ReadIOPS para ver o número de operações de E/S de disco. É uma prática recomendada manter esse valor o mais baixo possível.
• Verifique a métrica BufferCacheHitRatio para ver a porcentagem de solicitações atendidas pelo cache de buffer. É uma prática recomendada manter esse valor o mais alto possível.
• Verifique a métrica FreeableMemory para ver a memória disponível da instância de banco de dados. É uma prática recomendada manter esse valor estável. Memória disponível insuficiente pode causar baixo BufferCacheHitRatio e alto ReadIOPS.
  Observação: se a métrica BufferCacheHitRatio cair e a declaração SELECT estiver lenta, o mecanismo processará as consultas dos volumes subjacentes.

As buscas de armazenamento local por disco também podem fazer com que a declaração SELECT seja executada lentamente. Compatível com o Aurora MySQL, usa armazenamento local para tabelas temporárias manuais e internas. Para obter mais informações, consulte Novo comportamento de tabela temporária no Aurora MySQL versão 3. Para monitorar e resolver buscas em disco de armazenamento local, verifique as métricas do sistema operacional Rdstemp do Database Insights. É uma prática recomendada manter esses valores o mais baixos possível.

A saturação da rede pode fazer com que as instruções SELECT sejam executadas lentamente. O Aurora executa operações de E/S no volume do cluster pela rede e envia o resultado da consulta ao cliente por meio da rede. Para monitorar e resolver a saturação da rede, verifique a métrica NetworkThroughput e StorageNetworkThroughput. O throughput total da rede deve ser menor que a largura de banda da rede da sua instância de banco de dados.

Se algum uso de recursos exceder os recursos do seu tipo de instância de banco de dados devido ao workload, atualize a classe da instância de banco de dados.

Identifique impasses e bloqueios

Um impasse ocorre quando duas ou mais transações não podem continuar porque elas se bloqueiam. Para identificar impasses em seu banco de dados, ative o parâmetro innodb_print_all_deadlocks em seus grupos de parâmetros. Para obter mais informações, consulte innodb_print_all_deadlocks no site do MySQL. Em seguida, monitore o mysql-error.log a partir do console do Amazon RDS, da AWS Command Line Interface ou da API.

(Opcional) Para identificar impasses, faça login em uma conta de administrador do MySQL e execute o seguinte comando:

SHOW ENGINE INNODB STATUS\G;

Observação: na saída esperada do MySQL Workbench, verifique a seção Último impasse detectado.

Mesmo que não haja impasse, uma transação longa que mantém bloqueios pode estar bloqueando bloqueios. Para identificar bloqueios contínuos, 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?

Verifique se sua consulta usa um índice

Quando uma consulta não tem um índice ou faz varreduras completas da tabela, a consulta é executada lentamente. Os índices permitem que as instruções SELECT sejam executadas mais rapidamente. Para verificar se sua consulta usa um índice, use a declaração EXPLAIN. Para mais informações, consulte EXPLAIN statement no site do MySQL.

Na saída EXPLAIN, verifique os nomes das tabelas, a chave e o número de linhas que o mecanismo verifica durante a consulta. Se a saída não mostrar chaves em uso, crie um índice nas colunas na cláusula WHERE. Se a tabela tiver a indexação necessária, verifique se a tabela de estatísticas está atualizada. Use a cláusula ANALYZE para atualizar as estatísticas. Para obter mais informações, consulte a tabela INFORMATION_SCHEMA STATISTICS no site do MySQL.

Para identificar instruções SELECT lentas, use o slow_query_log. Para registrar consultas lentas, ative o registro de consultas lentas para seu cluster de banco de dados.

Verifique o HLL

O InnoDB usa o Multi-Version Concurrency Control (MVCC). O MVCC mantém várias cópias do mesmo registro para preservar a consistência da leitura. O HLL é 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 HLL aumenta até que a transação seja concluída ou revertida. Outras transações modificam as tabelas que a transação de longa duração usa. Se seu workload exigir várias transações abertas ou de longa duração, seu banco de dados poderá ter um HLL grande. Além disso, se as threads de limpeza não conseguirem acompanhar as mudanças no banco de dados, você poderá ter um HLL grande. Um alto HLL causa maior uso de recursos, desempenho lento e inconsistente da declaração SELECT e um aumento no armazenamento. Em casos extremos, um alto HLL pode causar uma interrupção no banco de dados.

Para monitorar sua HLL, use a métrica RollbackSegmentHistoryListLength da instância do escritor. Ou execute o seguinte comando:

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 o Aurora compatível com MySQL, o HLL está no nível do cluster. Para verificar o HLL no nível do cluster, conecte-se à sua instância de gravador e execute a seguinte declaração:

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec, oldest_read_view_trx_id, oldest_read_view_lsn from mysql.ro_replica_status;

Observação: é possível usar a declaração anterior para identificar o atraso da réplica entre os nós do leitor e do gravador. Verifique o número de sequência de log (LSN) mais antigo que o banco de dados usa para ler do armazenamento e o ID de transação de visualização de leitura mais antiga da instância de banco de dados (Trx ID). Certifique-se de que uma das instâncias contenha uma visualização de leitura antiga.

Para se conectar a uma instância que contém uma visualização de leitura antiga, execute a seguinte declaração:

SELECT a.trx_id, a.trx_state, a.trx_started,  TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open",    
a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state    
from information_schema.innodb_trx a, information_schema.processlist b    
where a.trx_mysql_thread_id=b.id    
order by trx_started;

Observação: use a declaração anterior para identificar a sessão ou transação que tem o trx_id mais antigo. Para desbloquear a operação de limpeza, determine se é possível encerrar a sessão.

Para resolver o alto nível de HLL, execute as seguintes ações:

• Se as gravações em DML causarem um aumento no HLL, reverta a transação para cancelar a consulta. Esse processo é demorado devido ao número de atualizações que você deve reverter.
• Se um READ causar um aumento no HLL, use mysql.rds_kill_query para cancelar a consulta.
  Observação: entre em contato com o administrador do banco de dados para verificar se é possível cancelar uma consulta.

Para evitar altos níveis de HLL, é uma prática recomendada confirmar os dados em lotes menores. Além disso, não reinicialize o cluster ou a instância de banco de dados. Limpe o HLL quando ele puder acessar os dados da memória no grupo de buffer. Se você reinicializar o banco de dados, o cache de página sobrevivente poderá ser perdido. Se o cache de páginas sobreviventes for perdido, as páginas de dados do volume do cluster deverão ser lidas para limpar o HLL. Esse processo é mais lento do que uma eliminação da memória e resulta em custos adicionais de cobrança de E/S.

Informações relacionadas

Monitorar logs do Amazon Aurora MySQL, Amazon RDS para MySQL e MariaDB com o Amazon CloudWatch

Ajustando o Aurora MySQL