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

9 minuto de leitura
0

Quero descobrir a causa do atraso da réplica ao usar o Amazon Relational Database Service (Amazon RDS) para MySQL.

Breve descrição

O Amazon RDS para MySQL usa replicação assíncrona. Isso significa que, às vezes, a réplica não pode acompanhar a instância de banco de dados primária. Como resultado, pode ocorrer atraso na replicação.

Para monitorar o atraso na replicação, use uma réplica de leitura do Amazon 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 a diferença entre 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 processamento de eventos na instância de banco de dados de réplica.

A replicação do MySQL funciona com três threads: o thread Binlog Dump, IO_THREAD e SQL_THREAD. Para obter mais informações sobre como esses threads funcionam, consulte a documentação do MySQL para Replication threads (Threads de replicação). 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, você pode identificar a causa raiz do atraso.

Resolução

Para identificar qual thread de replicação está atrasado, veja os exemplos a seguir:

1.    Execute o comando SHOW MASTER STATUS na instância de banco de dados primária e analise a saída. A saída é semelhante ao seguinte:

mysql> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.066552|      521 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Observação: na saída deste exemplo, a instância de banco de dados de origem ou primária grava os logs binários no arquivo mysql-bin.066552.

2.    Execute o comando SHOW SLAVE STATUS na instância de banco de dados de réplica e analise a saída. A saída é semelhante aos exemplos a seguir:

Exemplo 1:

mysql> SHOW SLAVE STATUS\G;
*************************** 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 1, Master_Log_File: mysql-bin.066548 indica que a réplica IO_THREAD lê o 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. Essa saída mostra que a réplica IO_THREAD está atrasado em quatro logs binários. Porém, o Relay_Master_Log_File é mysql-bin.066548, o que indica que a réplica SQL_THREAD lê do mesmo arquivo que IO_THREAD. Isso significa que a réplica SQL_THREAD está acompanhando, mas a réplica IO_THREAD está atrasada.

Exemplo 2:

mysql> SHOW SLAVE STATUS\G
*************************** 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 2 mostra que o arquivo de log da instância primária é mysql-bin-changelog.066552. A saída mostra que IO_THREAD está acompanhando 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-changelog.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. A réplica IO_THREAD pode ter um desempenho mais lento devido ao alto uso de largura de banda.

Se a réplica SQL_THREAD for a origem dos atrasos na replicação, as seguintes situações poderão causar um atraso:

  • Consultas de execução prolongada na instância de banco de dados primária
  • Tamanho ou armazenamento insuficientes da classe da instância de banco de dados
  • Consultas paralelas executadas na instância de banco de dados primária
  • Logs binários sincronizados com o disco na instância de banco de dados de réplica
  • Binlog_format na réplica definido como ROW
  • Atraso na criação de réplicas

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

Consultas 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 você iniciar uma alteração na instância primária e ela levar uma hora para ser executada, o atraso será de uma hora. Se a alteração também levar uma hora para ser concluída na réplica, o atraso total será de aproximadamente duas horas no momento da conclusão. Esse é um atraso esperado, mas você pode monitorar o log de consulta lenta na instância primária para minimizar esse atraso. Você também pode identificar instruções de execução prolongada para reduzir o atraso. Em seguida, divida as instruções de execução prolongada em instruções 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 forem menores que a primária, a réplica poderá ser limitada devido à insuficiência de recursos. Isso ocorre porque a réplica não consegue acompanhar as alterações feitas na instância primária. Verifique se o tipo de instância de banco de dados da réplica é 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

Se você executar consultas em paralelo na instância primária, elas serão confirmadas na réplica em uma ordem serial. Isso ocorre porque a replicação do MySQL é de thread único (SQL_THREAD), por padrão. Se um grande volume de gravações na instância de banco de dados de origem ocorrer em paralelo, as gravações na réplica de leitura serão serializadas. As gravações na réplica de leitura usam um único SQL_THREAD para serializar. Isso pode causar 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, MySQL 5.7 e versões superiores. Para obter mais informações sobre replicação em vários threads, consulte a documentação do MySQL sobre Binary logging options and variables (Opções e variáveis de registro em log binário).

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 se for ignorar erros de replicação, porque é difícil identificar quais transações você está ignorando. Isso pode levar a 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

Ativar backups automáticos na réplica pode resultar em 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 esse valor para 0, também desativará a sincronização do log binário com o disco pelo servidor MySQL. Em vez de fazer registro de log no disco, o sistema operacional (SO) ocasionalmente libera os logs binários para o disco.

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

Binlog_format está definido como ROW

O thread SQL executa uma varredura completa da tabela na réplica quando esses dois fatores são verdadeiros:

  • 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 em curto prazo, altere o algoritmo de pesquisa para INDEX_SCAN,HASH_SCAN para reduzir a sobrecarga da verificação completa da tabela. Em longo prazo, é uma prática recomendada adicionar uma chave primária explícita em cada tabela.

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

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 (réplica) e estabelece a replicação entre as duas.

O Amazon RDS leva tempo para criar novas réplicas de leitura. Depois de estabelecer a replicação, há um atraso na duração do tempo necessário para criar um backup da instância de banco de dados primária. Para minimizar esse atraso, crie um backup manual antes de solicitar a criação da réplica. Então, o snapshot do banco de dados é 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 snapshot 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 em EBS), o atraso da réplica pode aumentar inicialmente. Isso ocorre porque o efeito de carregamento lento pode influenciar o desempenho da replicação.

Para ajudar a mitigar os efeitos do carregamento lento em tabelas em sua réplica de leitura recém-criada, você pode realizar operações que envolvam varreduras completas da tabela. Por exemplo, executar uma operação mysqldump na sua réplica de leitura para tabelas ou bancos de dados específicos. Isso permite que o Amazon RDS priorize e baixe todos os dados da tabela de backup do Amazon Simple Storage Service (Amazon S3).

Além disso, considere usar o recurso de aquecimento de cache do InnoDB “sob demanda”. O recurso 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. Isso pode proporcionar ganhos de desempenho ao descartar o estado atual do grupo de buffers da instância de banco de dados primária antes de criar a réplica de leitura. Em seguida, recarregue o grupo 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