← Back to team overview

maria-developers team mailing list archive

negative TIME in SHOW FULL PROCESSLIST and information_schema.processlist

 

Hi,

While merging the Percona microsec_process patch (the 5.1 port of the patch is
called acc-pslist), I encountered a problem related to Bug#22047:

    http://bugs.mysql.com/bug.php?id=22047

The issue is with the exact behaviour of the TIME column of SHOW FULL
PROCESSLIST and information_schema.processlist.

The documentation says
(http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html):

    Time
        The time in seconds that the thread has been in its current state.

However, the TIME column is actually computed as

    <current local time> - @@TIMESTAMP

This means that the value will differ from the documented value for threads
that modify @@TIMESTAMP. In particular, it will differ for the replication
thread.

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.

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?

If so, I prefer to implement option 2. If not, I will go ahead with option 3.

Thanks,

 - Kristian.



Follow ups