Ofer Inbar a écrit : > I'm confused by a bit of the documentation here: > http://dev.mysql.com/doc/refman/5.0/en/show-slave-status.html > > In the section on Seconds_Behind_Master, first it says: > > 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. >
This is generally true and accepted, the important part to read here is "actively running". Which leads to the other paragraph: > ... but later it says: > > 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. > So if the SQL_THREAD isn't executing anything he will report "0" in "Seconds_behinds_master". If you think about it, it make sense because he doesn't have any "master" timestamp to base his calculation on. So the warning applies here, it's not because your SQL_THREAD report 0 seconds behind master, that means he's caught up with the master, it simply means it's caught up with the IO_THREAD. If the io_thread is lagging, there's no current way of knowing it > These two sections seem contradictory to me. If Seconds_Behind_Master > actually works the way it is first defined, then it should reflect any > delays caused by the network as well as delays in the SQL thread. > > Since each event in the binary log is timestamped by the master, we > know when the operation happened on the master. If we compare that > timestamp to the current time, we know how long ago it happened - so > if we look at the timestamp of the most recent event executed by the > slave SQL thread we see when it happened on the master, and can tell > how much time has elapsed since then. > > Two problems with this approach would be: > > 1. If the local clocks on the master and slave aren't in sync, > the timestamp comparison to current time would be off. > > 2. If no writes to the master database have happened in a while, this > would report the slave as "behind" because the most recent operation > on the master has a timestamp from some time ago. > > Both of these lead me to suspect that Seconds_Behind_Master does *not* > actually work the way the first paragraph implies; if so, then the > second paragraph I quoted from the doc could very well be true. > > But if so, what exactly does Seconds_Behind_Master show? > -- Cos > Problem 1 is true but ... you use ntp right? Seconds_Behinds_Master only has second sensitivity anyway... Problem 2 isn't true, because if the SQL_Thread don't have anything to play in it's relay log it will show 0 so you won't think he is behind the master but that leads to problem output in second paragraph that you paste from the manual. WHere if the Network is making it lag, you may think the slave is up-to-date but it's not really! So basically if you want to know if your slave is really up-to-date. You have to make sure, the IO_THREAD isn't constantly transferring and that the SQL_THREAD has done playing everything in the relay log! Maybe having a separate SQL_THREAD_LAG and IO_THREAD_LAG could be something possible (Feature request?) But I don't know if the IO_THREAD parse enough of the event to read timestamp in it. Hope that helps! -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
