Melhorando a performance da replicação do MySQL

Este post é o meu primeiro após o blog ter um novo design! Yayyy! Espero que vocês gostem e lembre-se: o feedback é sempre bem-vindo!

Uma das perguntas mais comuns que recebo é como melhorar o desempenho da replicação do MySQL. Ou como corrigir o atraso (seconds_behind_master) de uma replica (nomenclatura antiga: slave) comparado ao source (nomenclatura antiga de master).

Então, vamos começar com algumas propostas e considerar suas vantagens e desvantagens.

Parâmetros do Source

binlog_transaction_dependency_tracking: O valor padrão é COMMIT_ORDER e significa que as informações de dependência são geradas a partir dos timestamps do COMMIT no source. Alterar este parâmetro para WRITESET permite ao MySQL paralelizar tuplas que não estão atualizando o mesmo valor de PRIMARY KEY.

Se você estiver executando o MySQL 5.7, antes de configurar o binlog_transaction_dependency_tracking, precisará definir o parâmetro transaction_write_set_extraction.

Desvantagens: NENHUMA

binlog_group_commit_sync_delay: controla quantos microssegundos o COMMIT do binary log espera antes de sincronizar com o arquivo do binary log no disco. Em outras palavras, aumentar esse parâmetro permite ao MySQL gravar mais transações no mesmo grupo de confirmação, aumentando o nível de paralelismo.

Desvantagens: aumentar muito esse valor tornará a escrita da aplicação mais lenta.

Configurações do Source recomendadas

Não se esqueça de adicionar as configurações ao seu arquivo my.cnf para não perder a configuração após reiniciar.

Parâmetros do Replica

sync_binlog: o servidor MySQL sincroniza o seu log binário em disco (usando fsync ()) antes do COMMIT de cada transação. Essa opção é a opção mais segura, mas traz uma queda significativa no desempenho.

Desvantagens: se a Replica falhar, seu log binário(binary log) poderá perder algumas transações.

innodb_flush_log_at_trx_commit: Este é provavelmente o parâmetro mais conhecido pelos DBAs do MySQL. Definir innodb_flush_log_at_trx_commit como 0 ou 2 desativa a a restrição ao ACID. O valor 2 instrui o InnoDB a gravar no REDO log após o COMMIT da transação, mas não força o fsync. Em vez disso, ele executa fsyncs a cada segundo. Dessa forma, você pode perder 1 segundo de transações, caso o sistema operacional falhe. Em geral, um hardware moderno que suporta até 1 milhão de inserções por segundo, então esse número pode ser bastante grande. A versão 0 é ainda pior: você pode perder até 1 segundo de transações, mesmo se o mysqld travar, não apenas o sistema operacional.

Desvantagens: Se o MySQL ou o sistema operacional travar, os dados podem ser perdidos.

slave_parallel_type: as transações que fazem parte do mesmo grupo de COMMIT do binary log no SOURCE são aplicados(possivelmente) em paralelo em uma REPLICA. (Daí a importância de definir binlog_group_commit_sync_delay no SOURCE). A opção padrão (DATABASE) apenas paraleliza transações que vêm de bancos diferentes.

Desvantagens: NENHUMA

slave_parallel_workers: Precisamos fazer algumas contas para calcular esse número. Isso ocorre porque depende do workload que a REPLICA está manipulando. Para estimar o potencial de paralelismo, execute o seguinte no REPLICA:

E crie a view:

Por fim, você precisa executar a query para avaliar o paralelismo:

Agora, vamos ver um exemplo e analisar os resultados:

A partir do resultado acima, podemos observar que duas threads estão executando 72% da replicação. Além disso, uma delas replica apenas 2,9%. Portanto, se analisarmos os números, é interessante definir o valor do slave_parallel_workers como 4.

Desvantagens: definir um valor alto pode causar alto uso da CPU e ser ineficaz; por outro lado, definir um valor baixo pode limitar o desempenho da replicação.

Configurações recomendadas do REPLICA

Não se esqueça de adicionar as configurações ao seu arquivo my.cnf para não perder as configurações após reiniciar.

Considerações finais

Eu recomendo ter o GTID ativado para alguns problemas conhecidos. Eu recomendo a leitura deste post do blog:

https://www.percona.com/blog/2015/01/29/multi-threaded-replication-with-mysql-5-6-use-gtids/

Como você pode observar, a maioria das decisões envolve um equilíbrio entre o desempenho e o risco de perder alguns dados na REPLICA. Alguns DBAs para evitar isso criam várias REPLICAs, enquanto outros assumem o risco e, em caso de falha, recriam a REPLICA a partir do SOURCE novamente.

Espero que tenha ajudado! Até logo!

Leave a Reply