2

I have one master and 7 slaves. During high load on my master, I see replication lag and it keeps fluctuating between 0 and n (where n keeps increasing with time and I have seen n grow more than 1 hour). Fluctuations happen in a matter of seconds i.e. sec:1 - Lag:0s, sec:2 - Lag:2000s, sec:3 - Lag:0s, sec:4 - Lag:2002s,

  1. When seconds_behind_master is 0; show slave status\G says: "Slave has read all relay log; waiting for the slave I/O thread to update it".
  2. When seconds_behind_master is n; show slave status\G says: either "Reading event from the relay log" or "System Lock". On Master "show processlist" tells the replication thread has status "Sending binlog event to slave" always.

With the above points, I have figured that my SQL thread is not lagging and it's the IO thread which is the culprit. I read that network slowness can cause this issue, but network is not a bottleneck, as I have verified the bandwidth used between master and slaves is only 50%. When I turned on slave_compress_protocol, network usage went down but I was still seeing the replication lag grow in a fluctuating fashion.

I want to know what can be other causes apart from network which can cause this issue. I have gone through: https://www.percona.com/blog/2013/09/16/possible-reasons-when-mysql-replication-lag-is-flapping-between-0-and-xxxxx/ and couldn't attribute my lag to any of the points mentioned in the post.

Also, when the load on master stops, replication lag stops fluctuating and starts decreasing steadily from n and finally catches up.

Thanks.

Edit:

Can it happen that due to heavy load on master (% CPU utilisation is hitting 100%), IO thread is waiting intermittently to read from the binlogs)?

5
  • Do you have only one master? Or does one of your slaves is replicating from another slave? Commented May 9, 2018 at 12:50
  • It's a star topology. One Master and all slaves replicating from it.
    – Whomps
    Commented May 11, 2018 at 9:12
  • Don't know if this could affect, but just trying to discard things... Does all your servers have the same hour? Commented May 11, 2018 at 15:32
  • You can also run: show master status\G on the master and show slave status\G on the slave, checking the master_log_file and master log pos. This way you can see if there are too many transactions waiting to be applied on the slave or is just one big transaction that takes long to complete.
    – kriegu
    Commented May 11, 2018 at 15:41
  • Is the slave being accessed by some other service? Commented Apr 8, 2019 at 7:06

3 Answers 3

1

Once we had faced a similar kind of issue. It was found that a set of data pipelines were periodically reading data from the slave. But the indexes on the slave were not optimized for the reading behavior of the data pipeline. Changing the indexes on the slave did the trick for us.

0

Seconds_behind_master bouncing between 0 and some 'large' value?

I have seen that scenario for 16 years. I have never found the cause or cure. The problem usually goes away after a day or two.

Bottom line: Ignore it.

0

This is quite a rare problem, and the root cause is somewhat elusive. For whatever reason, io_thread fetches binary logs in a way that is way slower than other methods, including how mysqlbinlog utility does it, even though both use the MySQL network protocol to fetch it.

There was no easy workaround for this until recently, but there now exists a tool called st-sideload-relay, co-developed with a colleague of mine. It does exactly what the name suggests - it works in a similar way to executing fast point-in-time recovery, by fetching binlogs from upstream itself, and then sideloads them into local relay logs. Under conditions similar to what you were experiencing it can boost replication speed by orders of magnitude (see lag graph).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.