Description:
Over time, my processlist looks like Time values go haywire after a day:
mysql> show full processlist;select now(6);
+----+------+-----------+------+---------+------+-------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+-----------------------+
| 15 | root | localhost | test | Sleep | 2 | | NULL |
| 16 | root | localhost | test | Query | 2 | NULL | do 1 |
| 17 | root | localhost | test | Query | 2 | NULL | do 1 |
| 18 | root | localhost | test | Query | 2 | Sending to client | do 1 |
| 19 | root | localhost | test | Query | 2 | NULL | do 1 |
| 20 | root | localhost | test | Query | 2 | NULL | do 1 |
| 21 | root | localhost | test | Query | 2 | init | do 1 |
| 22 | root | localhost | test | Query | 2 | NULL | do 1 |
| 23 | root | localhost | test | Query | 2 | init | do 1 |
| 30 | root | localhost | NULL | Query | 2 | starting | show full processlist |
+----+------+-----------+------+---------+------+-------------------+-----------------------+
10 rows in set (0.00 sec)
+----------------------------+
| now(6) |
+----------------------------+
| 2016-09-16 08:25:12.314022 |
+----------------------------+
1 row in set (0.00 sec)
mysql> show full processlist;select now(6);
+----+------+-----------+------+---------+------+-------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+-----------------------+
| 15 | root | localhost | test | Sleep | 2 | | NULL |
| 16 | root | localhost | test | Query | 2 | Opening tables | do 1 |
| 17 | root | localhost | test | Query | 2 | Sending to client | do 1 |
| 18 | root | localhost | test | Query | 2 | freeing items | do 1 |
| 19 | root | localhost | test | Query | 2 | Opening tables | do 1 |
| 20 | root | localhost | test | Query | 2 | freeing items | do 1 |
| 21 | root | localhost | test | Query | 2 | NULL | NULL |
| 22 | root | localhost | test | Query | 2 | NULL | do 1 |
| 23 | root | localhost | test | Query | 2 | Sending to client | do 1 |
| 30 | root | localhost | NULL | Query | 2 | starting | show full processlist |
+----+------+-----------+------+---------+------+-------------------+-----------------------+
10 rows in set (0.00 sec)
+----------------------------+
| now(6) |
+----------------------------+
| 2016-09-16 08:25:13.481108 |
+----------------------------+
1 row in set (0.00 sec)
mysql> show full processlist;select now(6);
+----+------+-----------+------+---------+------+-------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+-----------------------+
| 15 | root | localhost | test | Sleep | 3 | | NULL |
| 16 | root | localhost | test | Query | 3 | Sending to client | do 1 |
| 17 | root | localhost | test | Query | 3 | freeing items | do 1 |
| 18 | root | localhost | test | Query | 3 | Sending to client | do 1 |
| 19 | root | localhost | test | Query | 3 | freeing items | do 1 |
| 20 | root | localhost | test | Query | 3 | Sending to client | do 1 |
| 21 | root | localhost | test | Query | 3 | Sending to client | do 1 |
| 22 | root | localhost | test | Query | 3 | query end | do 1 |
| 23 | root | localhost | test | Query | 3 | NULL | do 1 |
| 30 | root | localhost | NULL | Query | 3 | starting | show full processlist |
+----+------+-----------+------+---------+------+-------------------+-----------------------+
10 rows in set (0.00 sec)
+----------------------------+
| now(6) |
+----------------------------+
| 2016-09-16 08:25:13.922521 |
+----------------------------+
1 row in set (0.00 sec)
The Time value is wrong. The queries are really quick, as we can see:
mysql> show global status like '%questions%';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| Questions | 48177388170 |
+---------------+-------------+
1 row in set (0.00 sec)
mysql> show global status like '%uptime%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| Uptime | 148775 |
| Uptime_since_flush_status | 148775 |
+---------------------------+--------+
2 rows in set (0.00 sec)
Threads: 10 Questions: 934717420 Slow queries: 0 Opens: 408 Flush tables: 2 Open tables: 386 Queries per second avg: 323840.414
How to repeat:
use something like mysqlslap to run "do 1;" in few threads for a few days.
On day 1, show processlist will show queries with 0 or 1 Time.
On day 2, show processlist will show queries with 1 or 2 Time.
On day 3, show processlist will show queries with 2 or 3 Time.
etc.
