2

We want to tune the setting binlog_group_commit_sync_delay to increase replication performance. Our slave is currently two days behind. Does changing this setting affect the current backlog or will we see the impact after two days?

Improve this question
3
  • It should but TWO days is a big, big gap. What is the system load on the replica? Setting binlog_group_commit_sync_delay to add a few microseconds before syncing should help but forty eights hours is a big ask. Commented Oct 22, 2020 at 14:03
  • Are you doing things like big-table ALTERs or UPDATEs or DELETEs? Does the Primary have lots of threads actively running most of the time? Commented Oct 22, 2020 at 18:27
  • It went unnoticed for a while hence the delay. The system does a lot of single record updates and single record inserts from ~400 connections simultaneously for most of the day. Load isn't that high on the replica, no IO or CPU bottleneck. Please note that my question is about binlog_group_commit_sync_delay affecting existing backlog and not about fixing my replication issues. Commented Oct 27, 2020 at 9:26

2 Answers 2

Reset to default
1
+50

Existing logs are not affected. MySQL won't read and modify existing logs, it would be incredibly expensive and risky. So, yes, you'll see the benefit of this in 2 days (assuming the lag won't increase or decrease).

To me, it sounds like you are trying random optimisations to reduce the lag. If I'm correct, I recommend to investigate the reasons for slowness before making changes. Once you have an idea to make the slave faster, change a single variable, see if your hypothesis was correct, and if not undo the change.

If solving the problem is urgent, there are some things you can do to make a replica faster, like setting sync_bilog=0 and/or innodb_flush_log_at_trx_commit=0. In most cases you'd see the replica start to catch up. However, note that this is risky: if your replica crashes you will lose data. Depending on how unlucky you are, you may have to import a new backup from the source.

Improve this answer
4
  • 1
    Thanks for your answer! Sadly sync_binlog and innodb_flush_log_at_trx_commit are settings I can't change because it's an Azure managed service (cloud is always better right?). Do you have a source on the formatting of the logs with this setting? Commented Oct 29, 2020 at 12:59
  • The best source is watching the logs yourself with mysqldbinlog: dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html Commented Oct 29, 2020 at 13:12
  • Sadly I can't see the logs either on Azure. I'll have to create my own setup first. Thanks for your input! Commented Nov 2, 2020 at 8:06
  • You can access the binary log from another server, or even from outside of Azure. With mysqlbinlog you can "translate" the binlog into a human-readable format. Commented Nov 2, 2020 at 17:39
0

Please follow link. That would be helpful for you to troubleshoot further for root cause. Could you also post output of below command to check whether binary logs downloaded or have some network latency.

show slave status

Improve this answer
1
  • 1
    The solution was not in this guide. It was fixed by increasing binlog_group_commit_sync_delay and slave_parallel_workers. The second setting can only be changed by Microsoft in Azure Mysql. Only changing binlog_group_commit_sync_delay was not enough. Commented Nov 2, 2020 at 8:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.