Por que minha consulta SELECT está sendo executada lentamente no meu cluster de banco de dados MySQL do Amazon Aurora?

Breve descrição

Há vários motivos pelos quais as consultas SELECT podem ser executadas lentamente em seu cluster de banco de dados compatível com Aurora MySQL:

• Os recursos de sistema do Amazon Relational Database Service (Amazon RDS) estão sendo utilizados em excesso. Isso pode acontecer devido à alta CPU, pouca memória ou a uma workload que excede o que seu tipo de instância de banco de dados pode suportar.
• O banco de dados está bloqueado, e os eventos de espera resultantes estão fazendo com que as consultas SELECT tenham uma performance insatisfatória.
• A consulta SELECT está fazendo varreduras de tabelas completas em tabelas grandes, ou a consulta não tem os índices necessários.
• O tamanho da lista de histórico do InnoDB (HLL) cresceu consideravelmente devido a transações de longa duração.

Resolução

Monitorar seus atributos de sistema do Amazon RDS usando métricas

A prática recomendada é sempre monitorar a utilização da CPU e a memória livre no seu cluster do Amazon Aurora. Embora picos ocasionais de CPU sejam normais, uma CPU consistentemente alta por longos períodos de tempo pode fazer com que suas consultas SELECT sejam executadas lentamente. Use as seguintes ferramentas para determinar como e onde você está usando sua CPU:

1.    As métricas do Amazon CloudWatch são a maneira mais fácil de monitorar a utilização da CPU. Para mais informações sobre quais métricas estão disponíveis para o Aurora, consulte Métricas do CloudWatch para o Aurora.

2.    O monitoramento aprimorado fornece uma visão detalhada das métricas no nível do sistema operacional com menor granularidade. A análise detalhada mostra como os processos estão usando sua CPU.

3.    O Performance Insights determina com precisão sua carga de banco de dados. Ative o Performance Insights para sua instância de banco de dados e, em seguida, verifique se sua carga está excedendo o máximo de vCPU. Você também pode monitorar suas consultas de carga e SQLs por esperas e identificar os usuários que causam o máximo de esperas.

Consultas SELECT também podem ser executadas lentamente devido a buscas de disco. Para minimizar a E/S do disco, o mecanismo de banco de dados tenta armazenar em cache o bloco lido do disco. Isso significa que, da próxima vez que o banco de dados precisar do mesmo bloco de dados, esse bloco será obtido da memória e não do disco.

Use essas métricas para verificar se você está servindo uma consulta específica do disco ou da memória:

• VolumeReadsIOPS: essa métrica é o número de operações de leitura de [disco] em nível de volume faturadas. A prática recomendada é garantir que esse valor seja o mais baixo possível.
• BufferCacheHitRatio: essa métrica é a porcentagem de solicitações que são atendidas pelo cache do buffer. A prática recomendada é garantir que esse valor seja o mais alto possível. Se BufferCacheHitRatio cair e sua instrução SELECT ficar lenta, significa que você está processando a consulta de volumes subjacentes.

Outro atributo importante para identificar instruções SELECT lentas é o log de consultas lentas. Ative logs de consultas lentas no seu cluster de banco de dados para registrar essas consultas e tomar medidas mais tarde. Para a versão compatível com o MySQL 5.6, use o My SQL Performance Schema para monitorar a performance da consulta continuamente.

Identificar impasses e eventos de espera

O Amazon RDS bloqueia os dados no seu banco de dados para que somente uma sessão de usuário possa gravar ou atualizar uma linha a qualquer momento. Qualquer outra transação que exija essa linha é mantida em espera. Em um bloqueio compartilhado, as transações de gravação/atualização são mantidas em espera enquanto as transações de leitura fazem a leitura dos dados. Se uma consulta estiver aguardando para acessar uma linha que está bloqueada por outra consulta, isso poderá causar um impasse.

Para identificar bloqueios no seu banco de dados, habilite o parâmetro innodb_print_all_deadlocks nos seus grupos de parâmetros. Em seguida, monitore mysql-error.log a partir do console/CLI/API do RDS.

Ou faça login no MySQL com uma conta de administrador e execute este comando para identificar impasses na saída do comando na seção Último impasse detectado:

mysql> SHOW ENGINE INNODB STATUS\G;

Verificar se a sua consulta está usando um índice

Se uma consulta não tiver um índice ou fizer verificações completas de tabelas, ela será executada com maior lentidão. Índices ajudam a acelerar consultas SELECT.

Para verificar se a sua consulta está usando um índice, use a consulta EXPLAIN. Essa é uma ferramenta útil para solucionar consultas lentas. Na saída EXPLAIN, verifique os nomes de tabelas, a chave usada e o número de linhas verificadas durante a consulta. 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. Garantir que as estatísticas sejam precisas significa que o otimizador de consultas usa os índices mais seletivos com a cardinalidade correta. Isso melhora a performance da consulta.

Verificar o tamanho da lista do histórico (HLL)

O InnoDB usa um conceito chamado controle de simultaneidade de várias versões (MVCC). O MVCC mantém várias cópias do mesmo log para preservar a consistência da leitura. Isso significa que, quando você confirma uma transação, o InnoDB limpa as cópias mais antigas. Porém, quando uma transação não é confirmada por um longo período devido ao crescimento dos segmentos de ações desfazer, o tamanho da lista do histórico (HLL) aumenta. O tamanho da lista do histórico do InnoDB representa o número de alterações não liberadas.

Se sua workload exigir várias transações abertas ou de longa duração, você poderá esperar ver uma alta HLL no banco de dados.

Observação: transações de longa execução não são a única causa dos picos de HLL. Mesmo que os threads de limpeza não consigam acompanhar as alterações no banco de dados, a HLL pode permanecer alta.

Se você não monitorar o tamanho da HLL, a performance regridirá com o tempo. O aumento do tamanho da HLL também pode causar maior consumo de atributos, performance mais lenta e inconsistente de instruções SELECT e aumento no armazenamento. Em casos extremos, isso pode levar a uma interrupção do banco de dados.

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

SHOW ENGINE INNODB STATUS;

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 MySQL, devido à natureza dos volumes do armazenamento compartilhado, o tamanho da lista do histórico está no nível do cluster e não no nível da instância individual. Conecte-se ao seu gravador e execute a seguinte consulta:

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;

Essa consulta ajuda você a entender o atraso da réplica entre os nós de leitor e o nó de gravador. Ele também detalha o LSN mais antigo usado pela instância de banco de dados para ler do armazenamento e o ID de TRX da exibição de leitura mais antigo da instância de banco de dados. Use essas informações para verificar se um dos leitores está mantendo uma exibição de leitura antiga (em comparação com o status do InnoDB do mecanismo no gravador).

Observação: a partir do Aurora MySQL 1.19 e 2.06, você pode monitorar a HLL usando a métrica RollbackSegmentHistoryListLength no CloudWatch. Ou, em versões mais antigas, use trx_rseg_history_len para verificar a HLL usando o seguinte comando:

select NAME AS RollbackSegmentHistoryListLength,
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

Se o Performance Insights estiver ativado para suas instâncias do Aurora MySQL, você poderá marcar RollbackSegmentHistoryListLength. Navegue até o gravador Performance Insight e faça o seguinte:

1.    Selecione Gerenciar métricas e depois Métricas de banco de dados.

2.    Selecione a métrica trx_rseg_history_len e depois Atualizar gráfico.

Use os seguintes métodos para resolver problemas com o crescimento da HLL:

• Se a DML (gravações) causar o crescimento da HLL: Cancelar ou encerrar essa instrução envolve uma reversão da transação interrompida. Isso demora um tempo significativo, pois todas as atualizações feitas até esse momento estão sendo revertidas.
• Se uma instrução READ causar o crescimento da HLL: Encerre a consulta usando mysql.rds_kill_query.
• Dependendo de quanto tempo a consulta estiver em execução, trabalhe com seu DBA para verificar se você pode encerrá-la consulta usando o procedimento armazenado.

É uma prática recomendada evitar o crescimento monitorando a HLL com o uso desses métodos e evitar transações abertas ou de longa execução no banco de dados. Além disso, é uma prática recomendada confirmar os dados em lotes menores.

Importante: não reinicialize o cluster ou a instância de banco de dados. É mais eficiente limpar a HLL quando ela pode acessar os dados na memória no grupo de buffer. Se você reinicializar o banco de dados, o cache de páginas sobreviventes poderá ser perdido. Quando isso acontece, as páginas de dados do volume do cluster devem ser lidas para limpar a HLL. O processo é mais lento do que quando feito na memória e gera 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