Por que minha instância de banco de dados MySQL está mostrando um número elevado de sessões ativas aguardando eventos de espera SYNCH no Performance Insights?

Breve descrição

O Performance Insights está ativado em qualquer um dos seguintes serviços:

• Amazon Relational Database Service (Amazon RDS) para MySQL.
• Amazon RDS para MariaDB.
• Amazon Aurora Edição compatível com MySQL.

Se você vir eventos de espera SYNCH do MySQL no Performance Insights, significa que um grande número de sessões no banco de dados está tentando acessar os mesmos objetos protegidos ou as mesmas estruturas de memória. Objetos protegidos no MySQL incluem os seguintes:

• Arquivo de log binário ativo em uma instância de origem de log binário - contém um mutex que permite que somente uma sessão o leia ou grave de cada vez.
• Dicionário de dados - para gravações que geralmente são causadas por instruções de linguagem de controle de dados (DCL) ou de linguagem de definição de dados (DDL).
• Índice de hash adaptativo - contém um mutex que permite que apenas uma sessão o leia ou grave de cada vez.
• Cache de tabela aberta - somente uma sessão pode adicionar ou remover uma tabela do cache.
• Cada bloco de banco de dados dentro do Grupo de buffer do InnoDB - somente uma sessão pode modificar o conteúdo de um bloco na memória de cada vez.

Resolução

Certifique-se de que a instância de banco de dados tenha atributos de CPU suficientes para lidar com a workload

Se você tiver um grande número de sessões aguardando eventos SYNCH, isso causará alto uso da CPU. Se o uso atingir 100%, o número de sessões em espera aumentará. Ao solucionar problemas, aumente o tamanho da sua instância de banco de dados para garantir que haja CPU suficiente para processar a workload extra.

Como esses eventos geralmente duram pouco, a métrica de utilização da CPU do Amazon CloudWatch pode não mostrar o pico de uso corretamente. A melhor maneira de verificar isso é usar os contadores de CPU de um segundo no Enhanced Monitoring do RDS. Esses contadores são mais específicos e detalhados.

Aumentar a matriz de espera de mutex/bloqueio do MySQL

O MySQL usa uma estrutura de dados interna para coordenar threads. Essa matriz tem um tamanho de um por padrão. Isso é adequado para máquinas com uma única CPU, mas pode causar problemas em máquinas com várias CPUs. Se a sua workload tiver muitos threads em espera, aumente o tamanho da matriz. Defina o parâmetro MYSQL innodb_sync_array_size para a quantidade de CPUs (ou maior, até 1024).

Observação: o parâmetro innodb_sync_array_size somente é aplicável na inicialização do banco de dados.

Reduzir a simultaneidade

Em geral, o paralelismo ajuda a melhorar a throughput. Porém, quando muitas sessões tentam realizar atividades iguais ou semelhantes, elas precisam acessar os mesmos objetos protegidos. Quanto maior o número de sessões, mais CPU você utiliza enquanto espera.

Divulgue essas atividades ao longo do tempo ou programe-as em série. Você também pode agrupar várias operações em uma única instrução, como inserções de várias linhas.

Examinar eventos de espera específicos

Use os exemplos a seguir para solucionar seu evento de espera específico. Para obter mais informações sobre eventos de espera do Aurora MySQL, consulte Ajustar o Aurora MySQL com eventos de espera.

• synch/rwlock/innodb/dict sys RW lock, OU
  synch/rwlock/innodb/dict_operation_lock - Indica que muitas DCLs ou DDLs simultâneos são acionadas ao mesmo tempo. Reduza a dependência da aplicação por DDLs durante suas atividades regulares.
• synch/cond/sql/MDL_context::COND_wait_status - Indica um alto número de SQLs (incluindo seleções) tentando acessar uma tabela que uma DCL ou DDL está modificando. Evite executar instruções DDL em tabelas de alto tráfego durante a atividade regular da aplicação.
• synch/mutex/sql/LOCK_open OU
  synch/mutex/sql/LOCK_table_cache - Indica que o número de tabelas que estão sendo abertas pelas suas sessões excede o tamanho do cache de definição de tabelas ou do cache de abertura de tabelas. Aumente o tamanho desses caches.
• synch/mutex/sql/LOG - Seu banco de dados pode estar executando muitas instruções, e os métodos de log atuais não oferecem suporte para isso. Se você usar o método de saída TABLE, tente usar FILE em vez disso. Se você usar o log geral, use a auditoria avançada do Amazon Aurora em vez disso. Se você usar 0 ou menos de 1 para o parâmetro long_query_time, tente aumentar esse número.
• synch/mutex/innodb/buf_pool_mutex OU synch/mutex/innodb/aurora_lock_thread_slot_futex OU synch/rwlock/innodb/index_tree_rw_lock - Há um grande número de DMLs semelhantes acessando o mesmo objeto de banco de dados ao mesmo tempo. Use instruções de várias linhas e use o particionamento para distribuir a workload em diferentes objetos do banco de dados.
• synch/mutex/innodb/aurora_lock_thread_slot_futex - Ocorre quando uma sessão bloqueou uma linha para uma atualização e, em seguida, outra sessão tenta atualizar a mesma linha. Sua ação depende dos outros eventos de espera exibidos. Encontre e responda às instruções SQL responsáveis por esse evento de espera ou encontre e responda à sessão de bloqueio.
• synch/cond/sql/MYSQL_BIN_LOG::COND_done OU
  synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit OU
  synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log - Você ativou logs binários e pode haver um dos seguintes:

                         - uma alta throughput de confirmações.

                         - um grande número de transações em confirmação.

                         - réplicas lendo binlogs.

                         - uma combinação dos anteriores.

Considere atualizar o banco de dados para uma versão principal compatível com 5.7 ou superior. Além disso, use instruções de várias linhas ou agrupe várias instruções em uma única transação. No Amazon Aurora, use bancos de dados globais em vez da replicação de logs binários ou use os parâmetros aurora_binlog.

---

Informações relacionadas

Usar o Performance Insights do Amazon RDS

Trabalhar com grupos de parâmetros de banco de dados

Eventos do Aurora MySQL