關(guān)于Raplication之大惑

helbreathszw

We say in Seconds_Behind_Master that we have "caught up". Note that
          for example if network link is broken but I/O slave thread hasn't
          noticed it (slave_net_timeout not elapsed), then we'll say "caught
          up" whereas we're not really caught up. Fixing that would require
          internally cutting timeout in smaller pieces in network read, no
          thanks. Another example: SQL has caught up on I/O, now I/O has read
          a new event and is queuing it; the false "0" will exist until SQL
          finishes executing the new event; it will be look abnormal only if
          the events have old timestamps (then you get "many", 0, "many").
          Transient phases like this can't really be fixed.

helbreathszw

問題應(yīng)該就是網(wǎng)絡(luò)包的延遲，你們測測網(wǎng)速是否穩(wěn)定。

weichongli

原帖由 helbreathszw 于 2007-5-11 17:35 發(fā)表
Apparently on some systems tmp can be <0. Here are possible reasons
        related to MySQL:
        - the master is itself a slave of another master whose time is ahead.
        - somebody  ...

master本身不是其它master的slave;
應(yīng)用程序一個多月一直沒有變化，而這個現(xiàn)象是這一周突然出現(xiàn)的。
看起來這并不能解釋為什么SecondsBehindMaster會突然從0變成167……
不過還是謝謝，還有其它的原因沒？

showsa

Seconds_Behind_Master

This field is an indication of how “l(fā)ate” the slave is:

    *

      When the slave SQL thread is actively running (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 goes 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.

weichongli

原帖由 helbreathszw 于 2007-5-11 17:54 發(fā)表
問題應(yīng)該就是網(wǎng)絡(luò)包的延遲，你們測測網(wǎng)速是否穩(wěn)定。

如何檢查？

我用ping命令的結(jié)果：

685 packets transmitted, 685 received, 0% packet loss, time 688471ms
rtt min/avg/max/mdev = 0.063/0.187/0.494/0.053 ms

這個工具結(jié)果準(zhǔn)確不？

weichongli

原帖由 showsa 于 2007-5-11 18:20 發(fā)表
In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

這句話好正點，又多了解一點點～呵呵。

weichongli

基于以上幾位達人提供的信息：

得出結(jié)論：某種原因?qū)е耂LAVE I/O線程抽風(fēng)兒，于是乎Seconds_Behind_Master值就在0和167之間閃來閃去的。

不知道是否正確？

現(xiàn)在網(wǎng)絡(luò)看起來是正常的，那么，可能導(dǎo)致SLAVE I/O線程抽風(fēng)兒的因素是哪些？MASTER& SLAVE的負(fù)載？還是MASTER的I/O線程抽風(fēng)兒？

歡迎繼續(xù)指教

yejr

lz還沒理解replication的真正工作原理啊
那個behind的時間只是計算sql線程的執(zhí)行點和sql線程復(fù)制點的差距，實際情況中slave比master應(yīng)該延遲更大才對

weichongli

確實，我對這個東西確實不了解：）