The MySQL master-slave drift problem in AWS

About one month ago, we met a problem in MySQL master-slave architecture on AWS ec2. The MySQL master runs very fast, but the slave can only get the new data from about two or three hours ago.
We firstly suspect the resources for the master or slave instance are not enough therefore we upgrade the instance type to let them have more CPU cores and memory. But the lag problem still existed.
Only after we set binlog_group_commit_sync_delay=10000, the drift disappeared.
Let’s see the description for binlog_group_commit_sync_delay:

binlog_group_commit_sync_delay Controls how many microseconds the binary log commit waits before synchronizing the binary log file to disk. By default binlog_group_commit_sync_delay is set to 0, meaning that there is no delay. Setting binlog_group_commit_sync_delay to a microsecond delay enables more transactions to be synchronized together to disk at once, reducing the overall time to commit a group of transactions because the larger groups require fewer time units per group.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.