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

Breve descrição

Os eventos de espera do MySQL SYNCH no Performance Insights ocorrem quando várias sessões de banco de dados acessam os mesmos objetos protegidos ou estruturas de memória. Os seguintes objetos estão protegidos no Amazon Relational Database Service (Amazon RDS) para MySQL, no Amazon RDS para MariaDB e na edição compatível com o Amazon Aurora MySQL:

• O arquivo de log binário ativo em uma instância de origem de log binário que contém um mutex que permite que somente uma sessão o leia ou grave de cada vez.
• O dicionário de dados protegido contra criação de instruções de linguagem de controle de dados (DCL) ou de linguagem de definição de dados (DDL).
• O índice de hash adaptativo que contém um mutex que permite que apenas uma sessão o leia ou grave de cada vez.
• O cache de tabela aberto que permite que somente uma sessão adicione ou remova uma tabela do cache.
• Cada bloco de banco de dados dentro do grupo de buffer do InnoDB em que 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 recursos de CPU suficientes para gerenciar o workload

Um grande número de sessões que aguardam eventos SYNCH causa um alto uso da CPU. Com 100% de uso, o número de sessões em espera aumenta. Para solucionar esse problema, aumente o tamanho da sua instância de banco de dados para que haja CPU suficiente para processar o workload extra.

Como os eventos SYNCH normalmente não duram muito, a métrica de utilização da CPU do Amazon CloudWatch pode não mostrar corretamente o pico de uso. Essa métrica tem uma granularidade de apenas 60 segundos. Para verificar o pico de uso, use contadores de granularidade de um segundo em Monitoramento avançado no console do Amazon RDS.

Aumentar o mutex do MySQL ou bloqueie a matriz de espera

O MySQL usa uma estrutura de dados interna para coordenar threads com um tamanho de matriz padrão de 1. Essa configuração funciona para máquinas com uma única CPU, mas pode causar problemas em máquinas com várias CPUs. Se o seu workload tiver muitos threads em espera, aumente o tamanho da matriz. Modifique o parâmetro innodb_sync_array_size do MySQL para que seja igual ou maior que o número de CPUs. Para obter mais informações, consulte innodb_sync_array_size no site do MySQL.

Observação: o MySQL usa o parâmetro innodb_sync_array_size somente na inicialização do banco de dados.

Reduzir a simultaneidade

Normalmente, o paralelismo melhora o throughput. Porém, quando muitas sessões realizam atividades iguais ou semelhantes, elas precisam acessar os mesmos objetos protegidos. Quanto maior a quantidade de sessões, mais CPU você usará quando estiver esperando.

Para resolver esse problema, distribua as atividades ao longo do tempo ou programe-as em série. Também é possível agrupar várias operações em uma única instrução, como inserções de várias linhas.

Solucionar seu problema com base nos eventos de espera

Execute as seguintes ações de solução de problemas com base no evento de espera que você recebe.

synch/rwlock/innodb/dict sys RW lock ou synch/rwlock/innodb/dict_operation_lock

Esses eventos ocorrem quando você invoca um grande número de DCLs ou DDLs simultâneas ao mesmo tempo. Para solucionar esses problemas, reduza a dependência da aplicação em DDLs durante a atividade regular da aplicação.

synch/cond/sql/MDL_context::COND_wait_status

Esse evento ocorre quando um grande número de SQLs, incluindo seleções, acessa uma tabela que uma DCL ou DDL está modificando. Para solucionar esse problema, não execute instruções DDL em tabelas de alto tráfego durante atividades regulares da aplicação.

synch/mutex/sql/LOCK_open ou synch/mutex/sql/LOCK_table_cache

Esses eventos ocorrem quando o número de tabelas que suas sessões abrem excede o tamanho do cache de definição de tabela ou do cache de abertura de tabelas. Para solucionar esse problema, aumente o tamanho dos caches. Para obter mais informações, consulte 10.4.3.1 Como o MySQL abre e fecha tabelas no site do MySQL.

synch/mutex/sql/LOG

Esse evento ocorre quando seu banco de dados executa um grande número de instruções que os métodos de registro atuais não podem suportar. Se você usar o método de saída TABLE, então use FILE em vez disso. Se você usar o log geral, então use a Auditoria avançada no Aurora. Se você definir o parâmetro long_query_time como 0 ou um valor menor que 1, aumente o valor.

synch/mutex/innodb/buf_pool_mutex, synch/mutex/innodb/aurora_lock_thread_slot_futex ou synch/rwlock/innodb/index_tree_rw_lock

Esses eventos ocorrem quando um grande número de instruções semelhantes de linguagem de manipulação de dados (DML) acessam o mesmo objeto do banco de dados ao mesmo tempo. Para resolver esse problema, use instruções e partições de várias linhas para distribuir o workload em diferentes objetos do banco de dados.

synch/mutex/innodb/aurora_lock_thread_slot_futex

Esse evento ocorre quando uma sessão bloqueia uma linha para uma atualização e, em seguida, outra sessão tenta atualizar a linha. Solucione esse problema com base nos outros eventos de espera que você recebe. 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, synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit, ou synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log

Esses eventos ocorrem quando você ativa o log binário e há um grande volume de throughput de confirmação, transações de confirmação ou réplicas que estão lendo logs binários. Para solucionar esse problema, use instruções de várias linhas ou agrupe várias instruções em uma única transação.

Para obter mais informações sobre eventos de espera compatíveis com o Aurora MySQL, consulte Ajustar o Aurora MySQL com eventos de espera e Eventos de espera do Aurora MySQL.

É uma prática recomendada atualizar o banco de dados para uma versão principal compatível com 8.0 ou posterior. Quando possível, use instruções de várias linhas ou agrupe várias instruções em uma única transação. No Aurora, use Banco de dados global do Aurora em vez da replicação de logs binários ou use os parâmetros aurora_binlog.

Informações relacionadas

Monitoramento da carga do banco de dados com os Insights de Performance do Amazon RDS

Grupos de parâmetros para o Amazon RDS