Seconds_Behind_Master 解析

pkman110

通过show slave status查看到的Seconds_Behind_Master，从字面上来看，他是slave落后master的秒数，一般情况下，也确实这样，我们可以通过Seconds_Behind_Master数字查看slave是否落后于master，但是在一些环境中，他确会让我们产生幻觉。
在http://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html中，对Seconds_Behind_Master的有一句话阐述如下：

In essence, this field measures the time difference in   seconds between the slave SQL thread and the slave I/O             thread.   

        
很清晰的表明，该值是SQL thread  I/O  thread.之间的差值。
当在很快的网络连接情况下，I/O thread. 能很快的从master的binlog中同步sql到slave的relay-log里，这样，这个值就能基本确定的slave落后master的秒数
当网络环境特别糟糕的情况下，这个值确会让我们产生幻觉，I/O thread同步很慢，每次同步过来，SQL thread就能立即执行，这样，我们看到的Seconds_Behind_Master是0，而真正的，slave已经落后master很多很多。这时业务部门的同志们就会抱怨slave与master数据不对，而你看到的Seconds_Behind_Master确为0，你就会非常的郁闷了。
其实这个时候，我们看下master，与slave就能很好的确定这期间的原因。

mysql> show master status\G
*************************** 1. row ***************************
  File: ******-bin.001291
Position: 896711460
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.69.6.163
Master_User: replica
Master_Port: 3801
Connect_Retry: 60
Master_Log_File: *****-bin.001211
Read_Master_Log_Pos: 278633662
Relay_Log_File: *****-relay-bin.002323
Relay_Log_Pos: 161735853
Relay_Master_Log_File: *******-bin.001211
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 278633662
Relay_Log_Space: 161735853
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

很明显，slave已经落后master 好多了。


万能的maatkit 已经有脚本，及时监控Seconds_Behind_Master了，有时间仔细研究http://www.maatkit.org/doc/mk-heartbeat.html

 

 

 

参考：官方的解释

Seconds_Behind_Master

This field is an indication of how “late” the slave is:

• When the slave SQL thread is actively processing updates, this field is the number of seconds that have elapsed since the timestamp of the most recent event on the master executed by that thread.

• When the SQL thread has caught up to the slave I/O thread and is idle waiting for more events from the I/O thread, this field is zero.

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

If the network connection between master and slave is fast, the slave I/O thread is very close to the master, so this field is a good approximation of how late the slave SQL thread is compared to the master. If the network is slow, this is not a good approximation; the slave SQL thread may quite often be caught up with the slow-reading slave I/O thread, so Seconds_Behind_Master often shows a value of 0, even if the I/O thread is late compared to the master. In other words, this column is useful only for fast networks.

This time difference computation works even though the master and slave do not have identical clocks (the clock difference is computed when the slave I/O thread starts, and assumed to remain constant from then on).Seconds_Behind_Master is NULL (“unknown”) if the slave SQL thread is not running, or if the slave I/O thread is not running or not connected to master. For example, if the slave I/O thread is running but is not connected to the master and is sleeping for the number of seconds set by the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement (default 60) before attempting to reconnect, the value is NULL. This is because the slave cannot know what the master is doing, and so cannot say reliably how late it is.

The value of this field is based on the timestamps stored in events, which are preserved through replication. This means that if a master M1 is itself a slave of M0, any event from M1's binary log that originates from M0's binary log has M0's timestamp for that event. This enables MySQL to replicate TIMESTAMP successfully. However, the problem for Seconds_Behind_Master is that if M1 also receives direct updates from clients, theSeconds_Behind_Master value randomly fluctuates because sometimes the last event from M1 originates from M0 and sometimes is the result of a direct update on M1.