Bug #76348 Make Seconds_behind_master more accurate!
Submitted: 17 Mar 2015 6:57
Reporter: Fangxin Flou (OCA) Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:All OS:Any
Assigned to: CPU Architecture:Any
Tags: binlog

[17 Mar 2015 6:57] Fangxin Flou
Description:
Currently the logic to calc the Seconds_behind_master is as following.

current Seconds_Behind_Master
long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp)
                                - mi->clock_diff_with_master);

How ever if the server is very idle,  the value will become very large, which really combuse us, some monitor agent may relay on this value.

How to repeat:
n/a

Suggested fix:
We could record a time stamp of the queue_event call, such as:

      /* XXX: 'synced' should be updated by queue_event to indicate
         whether event has been synced to disk */
      bool synced= 0;
      if (queue_event(mi, event_buf, event_len))
      {
        mi->report(ERROR_LEVEL, ER_SLAVE_RELAY_LOG_WRITE_FAILURE,
                   ER(ER_SLAVE_RELAY_LOG_WRITE_FAILURE),
                   "could not queue event from master");
        goto err;
      }

      /* if any event queued update the time */
      mi->last_master_timestamp = time(0);

      if (RUN_HOOK(binlog_relay_io, after_queue_event,
                   (thd, mi, event_buf, event_len, synced)))
      {

Where  mi->last_master_timestamp is a newly variables to hold the last call time of queue_event, then we change the seconds_behind_master logic as following in function show_slave_status

        /*
        long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp)
                                - mi->clock_diff_with_master);
        */
        long time_diff= ((long)(mi->last_master_timestamp - mi->rli->last_master_timestamp)
                                - mi->clock_diff_with_master);

I think this logic will be better current.