Como soluciono um atraso elevado na réplica de log binário com o Amazon RDS para MySQL e o Aurora MySQL?

Breve descrição

Como o Amazon RDS para MySQL utiliza replicação assíncrona, às vezes a réplica fica atrasada em relação à instância de banco de dados primária. Para monitorar um atraso na replicação, utilize uma réplica de leitura do Amazon RDS para MySQL com replicação baseada na posição do arquivo de log binário.

Para verificar a métrica ReplicaLag para o Amazon RDS, use o Amazon CloudWatch. A métrica ReplicaLag relatará o valor do campo Seconds_Behind_Master do comando SHOW SLAVE STATUS.

No Aurora, a métrica AuroraBinlogReplicaLag mede o atraso da réplica entre clusters de banco de dados Aurora que usam logs binários.

Observação: nas versões 8.0.22 e posteriores do MySQL, o comando SHOW SLAVE STATUS foi substituído pelo comando SHOW REPLICA STATUS. Para obter mais informações, consulte SHOW SLAVE   REPLICA STATUS statement (Declaração SHOW SLAVE   REPLICA STATUS) no site do MySQL.

O campo Seconds_Behind_Master exibirá o atraso atual em segundos em que a instância de banco de dados de réplica está da instância de origem. O timestamp original registrado na instância de banco de dados primária também será exibido para o evento processado na réplica da instância de banco de dados.

A replicação do MySQL utilizará o despejo de log binário, o receptor de E/S de replicação e os threads de executor do SQL de replicação. Para obter mais informações sobre como os threads funcionam, consulte Replication threads (Threads de replicação) no site do MySQL. Se houver um atraso na replicação, é recomendado verificar se a réplica IO_THREAD ou a SQL_THREAD estão causando o atraso. Em seguida, é possível identificar a causa raiz do atraso.

Resolução

Identifique o thread de replicação que está causando o atraso

Conclua as etapas a seguir:

1. A fim de identificar onde a instância de banco de dados, seja primária ou de origem, grava os logs binários, execute o comando SHOW MASTER STATUS na instância de banco de dados primária:

   mysql> SHOW MASTER STATUS;

   Exemplo de saída:

   +----------------------------+----------+--------------+------------------+-------------------+
   | File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +----------------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.066552           |      521 |              |                  |                   |
   +----------------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

   Observação: na saída do exemplo anterior, a instância de banco de dados de origem ou primária grava os logs binários no arquivo mysql-bin.066552.
2. Para encontrar seu status de saída, execute o comando SHOW SLAVE STATUS ou o SHOW REPLICA STATUS na instância de banco de dados de réplica:

   mysql> SHOW SLAVE STATUS\G;

Exemplo de saída 1:

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066548
Read_Master_Log_Pos: 10050480
Relay_Master_Log_File: mysql-bin.066548
Exec_Master_Log_Pos: 10050300
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

No exemplo de saída anterior, o Master_Log_File: mysql-bin.066548 mostra que a réplica IO_THREAD está sendo lida a partir do arquivo de log binário mysql-bin.066548. A instância de banco de dados primária grava os logs binários no arquivo mysql-bin.066552. A réplica IO_THREAD está quatro binlogs atrás. No entanto, como o Relay_Master_Log_File é mysql-bin.066548, a réplica SQL_THREAD é lida a partir do mesmo arquivo que IO_THREAD. A réplica SQL_THREAD mantém a velocidade, mas a réplica IO_THREAD fica lenta.

Exemplo de saída 2:

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066552
Read_Master_Log_Pos: 430
Relay_Master_Log_File: mysql-bin.066530
Exec_Master_Log_Pos: 50360
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

O exemplo de saída anterior mostra que o arquivo de log da instância primária é mysql-bin.066552. O IO_THREAD mantém a velocidade com a instância de banco de dados primária. Na saída da réplica, o thread do SQL executa o Relay_Master_Log_File: mysql-bin.066530. Como resultado, SQL_THREAD tem um atraso de 22 logs binários.

Normalmente, IO_THREAD não causará grandes atrasos na replicação, isso porque o IO_THREAD apenas lê os logs binários da instância primária ou de origem. Porém, a conectividade e a latência da rede podem afetar a velocidade das leituras entre os servidores. O alto uso da largura de banda pode fazer com que a réplica IO_THREAD funcione mais lentamente.

Caso a réplica SQL_THREAD estiver causando atrasos na replicação, siga os seguintes passos para resolver o problema.

Consultas de gravação de execução prolongada na instância primária

Consultas de gravação de execução prolongada na instância de banco de dados primária que levam o mesmo tempo para serem executadas na instância de banco de dados de réplica podem aumentar seconds_behind_master. Por exemplo, se uma alteração na instância primária levar 1 hora para ser executada, o atraso será de 1 hora. Se a alteração também levar 1 hora para ser concluída na réplica, o atraso total será de 2 horas.

Para minimizar o atraso, monitore o registro lento de consultas na instância primária e identifique as consultas que precisam ser otimizadas. Também é possível reduzir instruções de longa duração para instruções ou transações menores.

Para solucionar problemas de desempenho relacionados ao log binário, analise os eventos de espera encontrados no Insights de Performance para obter mais informações. Também é possível ajustar os níveis de isolamento.

Tamanho ou armazenamento insuficientes da classe da instância de banco de dados

Se a configuração de armazenamento da classe da instância de banco de dados da réplica for menor que a da instância primária, a réplica poderá ser limitada devido à insuficiência de recursos. A réplica não conseguirá manter o número de alterações na instância primária.

Para evitar esse problema, certifique-se de que o tipo de instância de banco de dados da réplica seja igual ou superior ao da instância primária. A fim de que a replicação funcione com eficiência, cada réplica de leitura deverá ter a mesma quantidade de recursos de computação e armazenamento que a instância de banco de dados de origem.

Consultas paralelas executadas na instância de banco de dados primária

Por padrão, a replicação do MySQL é de thread único. Então, ao executar consultas em paralelo na instância primária, elas serão confirmadas na réplica em ordem serial. Quando um grande volume de gravações na instância de origem ocorrer em paralelo, as gravações na réplica de leitura utilizarão uma única SQL_THREAD para serializar. E com isso, poderá ocorrer um atraso entre a instância de banco de dados de origem e a réplica de leitura.

Com as versões 8.0.27 e posteriores do MySQL, o valor padrão para replica_parallel_workers é 4. Esse valor significa que as réplicas têm vários threads por padrão. Da mesma forma, para o Aurora MySQL versão 3.04 e posterior, a replicação tem vários threads por padrão, com o valor de replica_parallel_workers definido como 4. É possível modificar esse parâmetro em seu grupo de parâmetros personalizados.

Para obter mais informações sobre replicação com vários threads, consulte Binary logging options and variables (Opções e variáveis de registro em log binário) no site do MySQL.

A replicação com vários threads pode causar lacunas na replicação. Por exemplo, é difícil identificar transações que você pula. Quando você usa a replicação com vários threads, não é uma prática recomendada pular os erros de replicação. Podem ocorrer lacunas na consistência de dados entre as instâncias de banco de dados primária e de réplica.

Logs binários sincronizados com o disco na instância de banco de dados de réplica

Quando você ativa os backups automáticos na réplica, ocorre uma sobrecarga para sincronizar os logs binários com o disco na réplica. O valor padrão do parâmetro sync_binlog é definido como 1. Caso altere o valor para 0, o servidor MySQL não poderá sincronizar o log binário com o disco. Em vez disso, o sistema operacional ocasionalmente liberará os logs binários para o disco.

A fim de reduzir a sobrecarga de desempenho necessária para sincronizar os logs binários com o disco em cada confirmação, desative a sincronização do log binário. Porém, se houver uma falha de energia ou o sistema operacional travar, algumas confirmações podem não ser sincronizadas com os logs binários. A assincronização pode afetar os recursos de restauração para um ponto específico no tempo (PITR). Para obter mais informações, consulte sync_binlog no site do MySQL.

Binlog_format está definido como ROW

Quando a replicação ocorrer nos seguintes cenários, o thread SQL executará uma verificação completa da tabela:

• Quando binlog_format na instância de banco de dados primária estiver definido como ROW.
• A tabela de origem não possui uma chave primária.

Isso ocorre devido ao valor padrão do parâmetro slave_rows_search_algorithms que é TABLE_SCAN,INDEX_SCAN.

Para resolver esse problema temporariamente, altere o algoritmo de pesquisa para INDEX_SCAN,HASH_SCAN para reduzir a sobrecarga da verificação completa da tabela. Para uma solução mais permanente, é uma prática recomendada adicionar uma chave primária explícita a cada tabela.

Para obter mais informações sobre o parâmetro slave-rows-search-algorithms, consulte slave_rows_search_algorithms no site do MySQL.

Atraso na criação da réplica (aplicável ao RDS MySQL)

O Amazon RDS fará um snapshot do banco de dados para criar uma réplica de leitura de uma instância primária do MySQL. Em seguida, o Amazon RDS restaurará o snapshot para criar uma nova instância de banco de dados e estabelecerá a replicação entre as duas.

Após o Amazon RDS estabelecer a replicação, ocorrerá um atraso quando o próprio Amazon RDS criar um backup da instância de banco de dados primária. Para minimizar este atraso, crie um backup manual antes de chamar a criação da réplica. E então, o snapshot do banco de dados se tornará um backup incremental.

Quando restaurar uma réplica de leitura a partir de um snapshot, a réplica não esperará que todos os dados sejam transferidos da origem. A instância de banco de dados de réplica está disponível para realizar as operações do banco de dados. As cargas de instantâneo existentes do Amazon Elastic Block Store (Amazon EBS) criam um novo volume em segundo plano.

Observação: para réplicas do Amazon RDS para MySQL (volumes baseados no Amazon EBS), o atraso da réplica talvez aumente inicialmente devido ao carregamento lento que pode afetar o desempenho da replicação.

A fim de reduzir os efeitos do carregamento lento nas tabelas da sua nova réplica de leitura, é possível realizar operações que utilizam escaneamentos de tabela completa. Por exemplo, execute mysqldump em sua réplica de leitura de tabelas ou bancos de dados específicos a fim de priorizar todos os dados de tabelas de backup do Amazon Simple Storage Service (Amazon S3).

Também é possível utilizar o atributo de aquecimento de cache sob demanda do InnoDB para o RDS MySQL. O atributo de aquecimento de cache do InnoDB salvará o estado do grupo de buffers no disco em um arquivo chamado ib_buffer_pool que se encontra no diretório de dados do InnoDB. Como o Amazon RDS despeja o estado atual do grupo de buffers da instância de banco de dados primária antes de você criar a réplica de leitura, o desempenho é aprimorado com menos carga. É possível recarregar o grupo de buffers depois de criar uma réplica de leitura logo em seguida.

Efeitos do atraso na réplica

Um grande atraso na réplica na instância ou no cluster da réplica pode causar o acúmulo de logs binários na origem. Para evitar possíveis problemas, monitore e gerencie os tamanhos dos arquivos de log binário e o uso do espaço em disco. Para o RDS, é possível monitorar a métrica BinLogDiskUsage na instância de banco de dados de origem.

Configure o período ideal de retenção de logs binários para equilibrar os recursos de recuperação para um ponto no tempo com o uso do armazenamento em uma instância MySQL do RDS e no Aurora. Além disso, para aproveitar os logs binários para replicação e recuperação, entenda as convenções de nomenclatura de arquivos de log binário e o comportamento de alternância. Para obter uma lista dos logs disponíveis, execute o comando SHOW BINARY LOGS.

Informações relacionadas

Trabalhar com a replicação do MySQL no Amazon RDS

Trabalhar com réplicas de leitura MySQL

Otimizar a replicação de logs binários para Aurora MySQL