Incorrect max_duration for long running trx's when --default-time-zone is used

Bug #1634312 reported by Jericho Rivera on 2016-10-18
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Monitoring Plugins
Undecided
Unassigned

Bug Description

When the instance is using --default-time-zone= option (eg --default-time-zone="+00:00"), the computation for max_duration in pmp-check-mysql-innodb is inaccurate since trx.started is set with a different TZ from unix_timestamp().

Pmp-check-mysql-innodb uses this query to compute max_duration:
https://github.com/percona/percona-monitoring-plugins/blob/master/nagios/bin/pmp-check-mysql-innodb#L106
SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(t.trx_started), p.id, CONCAT(p.user, '@', p.host) FROM INFORMATION_SCHEMA.INNODB_TRX AS t JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = t.trx_mysql_thread_id ORDER BY t.trx_started LIMIT 1;

Sample output if default timezone is "+00:00"
mysql> SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(t.trx_started), p.id, CONCAT(p.user, '@', p.host) FROM INFORMATION_SCHEMA.INNODB_TRX AS t JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = t.trx_mysql_thread_id ORDER BY t.trx_started LIMIT 1; show processlist;
+--------------------------------------------------+----+-----------------------------+
| UNIX_TIMESTAMP() - UNIX_TIMESTAMP(t.trx_started) | id | CONCAT(p.user, '@', p.host) |
+--------------------------------------------------+----+-----------------------------+
| 25233 | 4 | root@localhost |
+--------------------------------------------------+----+-----------------------------+
1 row in set (0.01 sec)

+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
| 4 | root | localhost | test | Sleep | 33 | | NULL | 0 | 0 |
| 13 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
2 rows in set (0.00 sec)

Changed in percona-monitoring-plugins:
status: New → Confirmed
Jericho Rivera (jericho-rivera) wrote :

Hacky workaround is to use convert_tz like below:

mysql> SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(convert_tz(t.trx_started,'+00:00','+07:00')), p.id, CONCAT(p.user, '@', p.host) FROM INFORMATION_SCHEMA.INNODB_TRX AS t JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = t.trx_mysql_thread_id ORDER BY t.trx_started LIMIT 1; show processlist;
+--------------------------------------------------------------------------------+----+-----------------------------+
| UNIX_TIMESTAMP() - UNIX_TIMESTAMP(convert_tz(t.trx_started,'+00:00','+07:00')) | id | CONCAT(p.user, '@', p.host) |
+--------------------------------------------------------------------------------+----+-----------------------------+
| 36 | 4 | root@localhost |
+--------------------------------------------------------------------------------+----+-----------------------------+
1 row in set (0.01 sec)

+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
| 4 | root | localhost | test | Sleep | 36 | | NULL | 0 | 0 |
| 13 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
2 rows in set (0.00 sec)

However, this would not scale since one will have to edit the tool for each monitor server and edit the values during daylight savings time.

Btw, for the initial test:
timedatectl status
Local time: Mon 2016-10-17 17:18:52 PDT

mysql> select @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| +00:00 |
+--------------------+
1 row in set (0.00 sec)

Changing the system's timezone to match the mysql instance's TZ is a workaround, but in case this was not possible then adding a functionality would be good as well.

Roman Vynar (roman-vynar) wrote :

From the home page:

THE PROJECT IS MOVED TO GITHUB https://github.com/percona/percona-monitoring-plugins
For bug reporting and contribution please use GitHub.

Jericho, is it possible you will copy the report to Github?

Jericho Rivera (jericho-rivera) wrote :

Hi Roman,

I didn't notice that :)

Here's the github issue - https://github.com/percona/percona-monitoring-plugins/issues/26

Roman Vynar (roman-vynar) wrote :

Thanks!

Then closing this one here.

Changed in percona-monitoring-plugins:
status: Confirmed → Won't Fix
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.