Como solucionar o problema de atraso elevado da réplica no Amazon RDS para MySQL?

9 minuto de leitura
0

Desejo descobrir por que há um atraso da réplica ao usar o Amazon Relational Database Service (Amazon RDS) para MySQL.

Breve descrição

Como o Amazon RDS para MySQL usa replicação assíncrona, às vezes a réplica não pode progredir com a instância de banco de dados primária e causa um atraso na replicação.

Para monitorar o atraso na replicação, use uma réplica de leitura do RDS para MySQL com replicação baseada na posição do arquivo de log binário.

No Amazon CloudWatch, verifique a métrica ReplicaLag para o Amazon RDS. A métrica ReplicaLag relata o valor do campo Seconds_Behind_Master do comando SHOW SLAVE STATUS.

O campo Seconds_Behind_Master mostra o timestamp atual na instância de banco de dados de réplica. Ele também mostra o timestamp original registrado na instância de banco de dados primária para o evento que processa na instância de banco de dados de réplica.

A replicação do MySQL usa 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 Threads de replicação no site do MySQL. Se houver um atraso na replicação, identifique se a réplica IO_THREAD ou a réplica SQL_THREAD causam o atraso. Em seguida, é possível identificar a causa raiz do atraso.

Resolução

Identifique o thread de replicação que está atrasando

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.

Execute o comando SHOW SLAVE 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

Observação: no exemplo de saída anterior, o Master_Log_File: mysql-bin.066548 mostra que a réplica IO_THREAD é 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 causa grandes atrasos na replicação, porque IO_THREAD só 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.

Se a réplica SQL_THREAD estiver causando atrasos na replicação, use as seguintes etapas de solução de problemas 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 aproximadamente 2 horas.

Para minimizar o atraso, é possível monitorar o log lento de consultas na instância primária. Você também pode reduzir extratos de longa duração para extratos ou transações menores.

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

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

Para resolver 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 de banco de dados primária. Para que a replicação funcione com eficiência, cada réplica de leitura requer a mesma quantidade de recursos de computação e armazenamento que a instância de banco de dados de origem. Para obter mais informações, consulte Classes de instância de banco de dados.

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, quando você executa consultas em paralelo na instância primária, as consultas sã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 usarão uma única SQL_THREAD para serializar. Pode ocorrer um atraso entre a instância de banco de dados de origem e a réplica de leitura.

A replicação em vários threads (paralela) está disponível para MySQL 5.6 e versões superiores. Para obter mais informações sobre replicação em vários threads, consulte Opções e variáveis de log binário no site do MySQL.

A replicação em vários threads pode causar lacunas na replicação. Por exemplo, a replicação em vários threads não é uma prática recomendada ao ignorar os erros de replicação, porque é difícil identificar as transações que você ignora. 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. Se você alterar o valor para 0, também desativará a capacidade do servidor MySQL de sincronizar o log binário com o disco. Em vez disso, o sistema operacional ocasionalmente libera os logs binários para o disco.

Para 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

O thread SQL executa uma verificação completa da tabela quando está se replicando nos seguintes cenários:

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

Isso ocorre porque o valor padrão do parâmetro slave_rows_search_algorithms é 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 de réplicas

O Amazon RDS faz 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 restaura o snapshot para criar uma nova instância de banco de dados e estabelece a replicação entre as duas.

Depois que você estabelece a replicação, ocorre um atraso quando o Amazon RDS está criando um backup da instância de banco de dados primária. Para minimizar o atraso, crie um backup manual antes de chamar a criação da réplica. Então, o snapshot do banco de dados será, portanto, um backup incremental.

Quando você restaura uma réplica de leitura a partir de um snapshot, a réplica não espera 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 pode aumentar inicialmente porque o carregamento lento pode afetar o desempenho da replicação.

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

Você também pode usar o atributo de aquecimento de cache sob demanda do InnoDB. O atributo de aquecimento de cache do InnoDB salva o estado do grupo de buffers no disco em um arquivo chamado ib_buffer_pool no diretório de dados do InnoDB. O desempenho melhora porque o Amazon RDS descarta o estado atual do pool de buffers da instância de banco de dados primária antes de você criar a réplica de leitura. É possível, em seguida, recarregar o pool de buffers depois de criar uma réplica de leitura.

Informações relacionadas

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

Trabalhar com réplicas de leitura do MySQL