Understanding How MySQL SHOW PROCESSLIST Calculates the Time Column and Why Negative Values Appear

Problem source – A user observed negative Time values in the Worker thread of MySQL MTS and asked for an explanation.

About SHOW PROCESSLIST Time – The information displayed by SHOW PROCESSLIST is populated by the function mysqld_list_processes . The Time column is derived from the difference between the current server time ( now ) and the thread's start time ( thd_info->start_time ).

Percona:
    time_t now = my_time(0);
    protocol->store_long((thd_info->start_time > now) ? 0 : (longlong)(now - thd_info->start_time));
Official:
    time_t now = my_time(0);
    protocol->store_long((longlong)(now - thd_info->start_time));

Percona optimizes the output by converting negative results to zero, while the official version may show negative numbers.

Calculation method and testing – The start time is set by the overloaded function THD::set_time , which has three variants:

inline void set_time() { /* sets start_time based on current time */ }
inline void set_time(const struct timeval *t) { /* uses provided timestamp */ }
void set_time(QUERY_START_TIME_INFO *time_info) { /* uses info from query event */ }

These overloads affect how thd_info->start_time and thd_info->start_utime are initialized.

Scenarios affecting Time

Command execution – When a command starts, overload 1 is called without arguments, setting start_time to the current time. The Time value becomes now - thd_info->start_time .

Slave SQL/Worker threads – For replication events, overload 3 is used; the start time comes from the event header timestamp (the original master command time). If the slave's clock is behind the master, the calculation can yield negative values (Percona shows zero).

Manual timestamp – Setting SET TIMESTAMP=... invokes overload 2, fixing both start_time and start_utime . Subsequent commands will not update the start time, so Time equals now - set_timestamp , which can be unusually large or negative.

Idle sessions – While a session is idle, now keeps increasing but thd_info->start_time stays unchanged, causing Time to grow continuously until the next command.

Extension

When log_slave_updates is enabled, the slave records events from the master. The event timestamp is taken from the master, not the local slave clock. The Query Event exetime is calculated as:

ulonglong micro_end_time = my_micro_time();
my_micro_time_to_timeval(micro_end_time, &end_time);
exec_time = end_time.tv_sec - thd_arg->start_time.tv_sec;

Thus, exetime reflects the difference between the master’s command start time and the time when the event finishes on the master (or slave, if clocks differ), which can also become abnormal if the slave’s clock is far ahead.

Conclusion – The Time column is a useful metric but can be misleading when clock skew, replication lag, or manual timestamp changes occur. Understanding the underlying calculation helps diagnose unexpected values. For deeper insights, refer to the author's series "Deep Understanding of MySQL Master‑Slave Principles".