information_schema.processlist TIME is unbelievable huge

[James Wang]

Hi All,

My PXC details:

Server version: 5.6.24-72.2-56-log Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11

Query: SELECT time,db,command,info from information_schema.processlist WHERE TIME>2 AND COMMAND NOT IN('Sleep', 'Binlog Dump') AND ID IS NOT NULL;

I very rarely see any result from above query.  However, if I do, TIME is unbelievable large, e.g. 2523580 (SECONDS)  NONE of my queries take that long!!!!

Any one else had the same experience please?

Thanks a lot in advance

James

[Roel Van de Paar]

James, do you use SET TIMESTAMP=x ?

[James Wang]

Not in my script.

And select @@TIMESTAMP;

+-------------------+

| @@TIMESTAMP       |

+-------------------+

| 1462781314.362612 |

+-------------------+

At time this post.

[Roel Van de Paar]

Rightio. Maybe best to see then if you can create a reproduce script. If so, please log a bug at launchpad.
If you can reproduce in Percona Server, https://bugs.launchpad.net/percona-server/+filebug/+login

If you can reproduce in Percona XtraDB Cluster, https://bugs.launchpad.net/percona-xtradb-cluster/+filebug

Thanks

[James Wang]

ID is always the same though query is different:  1074193452

That may be a clue - looks queries share the same connection and TIME is connection time rather than query execution time?

[Roel Van de Paar]

I wonder if this is related to an issue that I am debugging at the moment. I see in upstream (MS 5.6.30) a - what seems very sporadic but highly frequent - bug where - even with a single SQL thread - the duration of a particular transaction is excessively large. I first saw this in fb-mysql, see the - now surpassed - bug report here; https://github.com/facebook/mysql-5.6/issues/244 (please do not comment on this report to avoid confusion there). 

In summary; duration is/looks to be real (in effective time), but should not be (in query size)

When did you start seeing this?

[James Wang]

Only recently and also only on ONE of our >100 MySQL servers.

Looks to me that some of our scripts use connection pool and the connection is re-used.

Any how, there is room to improve - I need more accurate query execution time.  Looks TIME_MS is also not so accurate either.

Thanks for your help, Roel.