Reduce Replication Lag | Percona Community

Community website
1

Replication Lag is just a fact of life with async-replication. We can’t stop lag, but we can help to reduce it. Many times the Seconds_Behind_Source can be very deciving, I have seen it go from 1 hour behind to 0 lag in the blink of an eye. There are many factors that can add to replica lag. Some of these are:

This is a companion discussion topic for the original entry at https://percona.community/blog/2022/06/01/speed-up-replication-lag/
2

Comment by Taylor Chu:

binlog_transaction_dependency_tracking = writeset should be setting on primary, not sure why this blog mentioned setting on the replica. binlog_group_commit_sync_delay helps commit on the replica as binlog enabled by default on MySQL 8.0 . If the replica disabling the binlog, it also useless.

Setting binlog_group_commit_sync_delay can increase the number of parallel committing transactions on any server that has (or might have after a failover) a replica.
https://dev.mysql.com/doc/r…

binlog_transaction_dependency_tracking specifies how the source mysqld generates the dependency information that it writes in the binary log to help replicas determine which transactions can be executed in parallel.
https://dev.mysql.com/doc/r…

Link to original comment:

4

Comment by @Wayne_Leutwyler:

Thank you for your comments. I had it in my notes to address this since in this post I am using 1 replica and 1 primary. In several other cases I do writeset on replicas. These cases include where I have replicas designed to be HA/DR fail over nodes, being managed by Orchestrator for automated failover.

5

Hi!

Have a question about this setting on primary:

set global replica_parallel_workers = 4;

If I have 4 and more replicas(each has 4 replication threads) - which value of global replica_parallel_workers should be set on primary? 4х replica count?

1 Like
6

replica_parallel_workers is needed on replica host. It enables multithreading for SQL thread for replica. It is not needed on primary.

1 Like