Primary/Standby Replication Delay Increases Sharply and Then Decreases

Scenario

The following figure shows a Cloud Eye console display indicating that the Real-Time Replication Delay metric of a read replica increased sharply and then fell back down again.

Possible Causes

• This problem is related to how the value of Seconds_Behind_Master is calculated. For details about how to calculate this value, see How Primary/Standby Replication Works.
• The replication delay peak occurred because the I/O thread of the read replica received a new binlog file, but the SQL thread did not start to replay the new binlog file. As a result, the value of last_master_timestamp for calculating Seconds_Behind_Master was the time when the previous binlog transaction was executed on the primary node, which was different from the system time (time (0)) of the read replica. When the SQL thread started to parse the new binlog file, the replication delay immediately decreased.
• This problem occurs occasionally and does not affect your workloads.

The following symptom was found in the binlog files generated during the period when the replication delay increased sharply and then fell back:

The difference between the start time of the first transaction in the new binlog file and the end time of the last transaction in the previous binlog file was the same as the difference between the sudden increase and fallback times.

Solution

No action is required. This happens sometimes but is completely normal.