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

Breve descrição

Os eventos de espera SYNCH do MySQL no Insights de Performance 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 no Amazon Aurora compatível com MySQL:

• O arquivo de log binário ativo em uma instância de origem de binlog 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 gravação de instruções de linguagem de controle de dados (data control language, DCL) ou de linguagem de definição de dados (data definition language, 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 CPUUtilization 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 no Monitoramento aprimorado no console do Aurora e do RDS.

Aumente 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 ele 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 startup do banco de dados.

Reduza 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 declaração, como inserções de várias linhas.

Solucione seu problema com base nos eventos de espera

Realize 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 tabelas abertas. Para solucionar esse problema, aumente o tamanho dos caches. Para obter mais informações, consulte 10.4.3.1 How MySQL opens and closes tables (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 com os quais os métodos de registro atuais não são compatíveis. 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 (data manipulation language, 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 registro em 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 binlogs. 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 do Aurora compatível com MySQL, consulte Ajustar o Aurora MySQL com eventos de espera e Eventos de espera do Aurora MySQL.

É uma prática recomendada fazer upgrade do 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

Monitorar a carga de banco de dados com o Performance Insights no Amazon RDS

Grupos de parâmetros para Amazon RDS