Como soluciono um problema de execução lenta de uma declaração SELECT 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 do Aurora compatível com o 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 sua lista de histórico (history list length, HLL) do InnoDB.

Resolução

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.

Use o CloudWatch Database Insights para verificar por que uma declaração SELECT está 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 está 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 métricas do Amazon CloudWatch 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 verificar 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 do 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. A 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 processa as consultas dos volumes subjacentes.

As buscas em disco no armazenamento local também podem fazer com que a declaração SELECT seja executada lentamente. O Aurora compatível com 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 no 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, faça upgrade da classe da instância de banco de dados.

Identifique deadlocks e bloqueios

Um deadlock ocorre quando duas ou mais transações não podem continuar porque elas se bloqueiam. Para identificar deadlocks 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 Aurora e do RDS, da AWS Command Line Interface ou da API.

(Opcional) Para identificar deadlocks, 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 deadlock detectado.

Mesmo que não haja deadlock, uma transação longa que mantém bloqueios pode estar bloqueando. Para identificar bloqueios existentes, 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 obter mais informações, consulte EXPLAIN statement (declaração EXPLAIN) no site do MySQL.

Na saída de 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 The INFORMATION_SCHEMA STATISTICS table (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 em seu cluster de banco de dados.

Verifique o HLL

O InnoDB usa o Controle de concorrência multiversã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 alto. Além disso, se as threads de limpeza não conseguirem acompanhar as mudanças no banco de dados, é possível ter um HLL alto. Um HLL alto causa maior uso de recursos, desempenho lento e inconsistente da declaração SELECT e um aumento no armazenamento. Em casos extremos, um HLL alto pode causar uma interrupção no banco de dados.

Para monitorar seu HLL, use a métrica RollbackSegmentHistoryListLength da instância do gravador. 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

No 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 (log sequence number, 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, verifique se é possível encerrar a sessão.

Para resolver o HLL alto, realize 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 seu banco de dados para verificar se é possível cancelar uma consulta.

Para evitar HLL alto, é 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 persistente pode ser perdido. Se o cache de página persistente for perdido, as páginas de dados do volume do cluster devem 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

Monitor Amazon Aurora MySQL, Amazon RDS for MySQL and MariaDB logs with Amazon CloudWatch (Monitorar logs do Amazon Aurora MySQL, Amazon RDS para MySQL e MariaDB com o Amazon CloudWatch)

Ajustar o Aurora MySQL