On Fri, Sep 18, 2009 at 1:55 PM, Kristian Nielsen <knielsen@knielsen-hq.org> wrote:
There is a replication FAQ, which says this:
http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html
16.3.4.3: How do I know how late a slave is compared to the master?
You can read the Seconds_Behind_Master column in SHOW SLAVE STATUS. See Section 16.4.1,“Replication Implementation Details”.
In the Time column in the output of SHOW PROCESSLIST, the number of seconds displayed for the slave SQL thread is the number of seconds between the timestamp of the last replicated event and the real time of the slave machine. You can use this to determine the date of the last replicated event.
However, this does not take into account the possibility of time skew between master and slave. The actual value displayed in SHOW PROCESSLIST is
<current time> - <time of original event> + <time skew>
To me, this is just too complex to be really useful.
On the other hand, Seconds_Behind_Master _does_ take into account the time skew, so it seems a much more useful metric to use to me. Frankly, to me this last part of the documentation sounds to me like trying to spin a bug as a feature.
FWIW, 12 months ago I could still spot negative values, well really high values as I think it reports an unsigned int) in Seconds_Behind_Master. Never manually, but indeed with a script that was busy selecting the value. (The script would break when the value suddenly became "really high value".) This was after such behavior had been "fixed" at least 2 times. (Can't find the bug number now.) A bit off topic, but maybe relevant as background info.
Now, the microsec_process patch adds a TIME_MS column, which works like TIME is documented, except with microsecond precision. This leaves three options to choose between:
1. Let TIME_MS also depend on @@TIMESTAMP. This is particularly ugly, as TIMESTAMP has seconds granularity, so rounding effects will be "interesting". I really don't like this idea.
2. Let TIME_MS be independent of @@TIMESTAMP; it will just be the time since last state change, with microseconds resolution. But keep currently implemented semantics of TIME. This means that TIME and TIME_MS will be unrelated for threads that change @@TIMESTAMP (like the slave thread). This will be confusing.
3. Change implementation of TIME so that both TIME and TIME_MS are independent of @@TIMESTAMP.
Of these, I tend to prefer option 3.
Reason is that the semantics is much cleaner and simple to understand.
The functionality to see how much slave is behind is in any case better obtained using Seconds_Behind_Master.
However, it does involve a change in behaviour, so I want to ask opinions if anyone thinks the change in behaviour would cause too much breakage?
I'm the wrong person to answer, but seems to me we should at this stage prefer being bug-for-bug compatible and changing semantics between MariaDB and MySQL may turn out to be more of a problem than being helpful. (If we could agree with the MySQL team to adopt the fix in a future version, it would be different.) Btw, Monty is back home but still not on email / IRC. Call him if you want his opinion. henrik -- email: henrik.ingo@avoinelama.fi tel: +358-40-5697354 www: www.avoinelama.fi/~hingo book: www.openlife.cc