Seconds_Behind_Master alternates between 0 and a number x I have a slave server with mariadb 10.3.10 on debian 9. For several days the server has been working correctly, Seconds_Behind_Master was always kept at 0 indicating that it was properly synchronized. Since yesterday Seconds_Behind_Master has started to alternate between 0 and numbers greater than 1000. I run show slave status and see:
  Seconds_Behind_Master: 3893, then I run it again and Seconds_Behind_Master: 0 then Seconds_Behind_Master: 3895 and it has kept growing and decreasing. Can someone help me solve this problem?
Seconds_behind_master doesn't exactly measure the replication lag. It measures the difference between the timestamp of the last binlog event fetched by the IO thread, versus the timestamp of the last binlog event executed by the SQL thread.
This can become misleading for example if the master has more binlogs that the replica hasn't fetched yet. If the network between the master and replica is too slow for the rate of binlogs, the replica might check the timestamp difference and conclude incorrectly that it is caught up. Then the IO thread fetches another batch of binlogs, and suddenly the timestamp difference is great again, and it looks like the replica is not caught up.
In my environment, when we see this happen, it often affects the cross-country replication link when the network is degraded.
It can also happen if the master is writing a lot of bulky content to the binlogs in ROW-based binlog format. A good example is a database that has records with large BLOB/TEXT content, but it makes rapid updates to another column of the same row. By default, ROW-based binlogs write the entire row to the binlog, even columns you did not change. So a simple update to a status column may result in many megabytes being written to the binlog, and causes a delay in transfer over the cross-country link.
The replica downloads binlog events as fast as it can, but it naturally flip-flops between &quot;I'm all caught up&quot; versus &quot;I'm way behind&quot; if bulky binlog events keep arriving.
Solutions: set binlog_row_image=MINIMAL on the master. This causes ROW-based binlogs to store only the columns that have changed.
If you want to get a more accurate measure of replication lag, use a heartbeat tool like pt-heartbeat.