← Back to team overview

maria-developers team mailing list archive

Re: negative TIME in SHOW FULL PROCESSLIST and information_schema.processlist


On Fri, Sep 18, 2009 at 1:55 PM, Kristian Nielsen
<knielsen@xxxxxxxxxxxxxxx> wrote:
> There is a replication FAQ, which says this:
>    http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html
>  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 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.


email: henrik.ingo@xxxxxxxxxxxxx
tel:   +358-40-5697354
www:   www.avoinelama.fi/~hingo
book:  www.openlife.cc

Follow ups