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.
|
1 2 3 4 5 6 7 8 |
master [localhost:46009] {msandbox} ((none)) > set global binlog_group_commit_sync_delay = 3000; Query OK, 0 rows affected (0.00 sec) master [localhost:46009] {msandbox} ((none)) > set global transaction_write_set_extraction=XXHASH64; Query OK, 0 rows affected (0.00 sec) master [localhost:46009] {msandbox} ((none)) > set global binlog_transaction_dependency_tracking=writeset; Query OK, 0 rows affected (0.00 sec) |
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:
|
1 2 3 4 5 |
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_transactions%'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'transaction'; |
E crie a view:
|
1 2 3 4 5 6 7 |
use sys; CREATE VIEW mts_summary_trx AS select performance_schema.events_transactions_summary_by_thread_by_event_name.THREAD_ID AS THREAD_ID, performance_schema.events_transactions_summary_by_thread_by_event_name.COUNT_STAR AS COUNT_STAR from performance_schema.events_transactions_summary_by_thread_by_event_name where performance_schema.events_transactions_summary_by_thread_by_event_name.THREAD_ID in (select performance_schema.replication_applier_status_by_worker.THREAD_ID from performance_schema.replication_applier_status_by_worker); |
Por fim, você precisa executar a query para avaliar o paralelismo:
|
1 2 |
SELECT SUM(count_star) FROM mts_summary_trx INTO @total; SELECT 100*(COUNT_STAR/@total) AS PCT_USAGE FROM mts_summary_trx; |
Agora, vamos ver um exemplo e analisar os resultados:
|
1 2 3 4 5 6 7 8 9 |
+-----------+ | PCT_USAGE | +-----------+ | 41.5845 | | 31.4046 | | 13.0119 | | 11.0909 | | 2.9081 | +-----------+ |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
slave1 [localhost:46010] {msandbox} (sys) > set global sync_binlog = 0; Query OK, 0 rows affected (0.00 sec) slave1 [localhost:46010] {msandbox} (sys) > set global innodb_flush_log_at_trx_commit=0; Query OK, 0 rows affected (0.00 sec) slave1 [localhost:46010] {msandbox} (sys) > set global slave_parallel_type = LOGICAL_CLOCK ; Query OK, 0 rows affected (0.00 sec) slave1 [localhost:46010] {msandbox} (sys) > set global slave_parallel_workers = 4; Query OK, 0 rows affected (0.00 sec) |
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!