QUERY_RESPONSE_TIME_READ & QUERY_RESPONSE_TIME_WRITE broken if accessed through non-uppercase name

[Roman Vynar]

PS 5.6 and 5.7 has READ/WRITE split for query response time distribution unlike 5.5 has just summaries.

https://www.percona.com/doc/percona-server/5.6/diagnostics/response_time_distribution.html

However, it appears the data in all 3 tables are identical :(

mysql> SELECT t1.TIME, t1.COUNT, t2.COUNT, t3.COUNT, t1.TOTAL, t2.TOTAL, t3.TOTAL FROM information_schema.query_response_time t1 join information_schema.query_response_time_read t2 on t1.TIME=t2.TIME join information_schema.query_response_time_write t3 on t2.TIME=t3.TIME;
+----------------+-------+-------+-------+----------------+----------------+----------------+
| TIME | COUNT | COUNT | COUNT | TOTAL | TOTAL | TOTAL |
+----------------+-------+-------+-------+----------------+----------------+----------------+
| 0.000001 | 47 | 47 | 47 | 0.000000 | 0.000000 | 0.000000 |
| 0.000010 | 59 | 59 | 59 | 0.000326 | 0.000326 | 0.000326 |
| 0.000100 | 1614 | 1614 | 1614 | 0.057620 | 0.057620 | 0.057620 |
| 0.001000 | 358 | 358 | 358 | 0.075372 | 0.075372 | 0.075372 |
| 0.010000 | 33 | 33 | 33 | 0.097808 | 0.097808 | 0.097808 |
| 0.100000 | 2 | 2 | 2 | 0.025178 | 0.025178 | 0.025178 |
| 1.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 10.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 100.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 1000.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 10000.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 100000.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 1000000.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| TOO LONG | 0 | 0 | 0 | TOO LONG | TOO LONG | TOO LONG |
+----------------+-------+-------+-------+----------------+----------------+----------------+
14 rows in set (0.00 sec)

Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

[Laurynas Biveinis (laurynas-biveinis)]

Cannot reproduce so far - please provide your plugins' installation procedure and workload?

[Roman Vynar (roman-vynar)]

No workload, a simple test. The plugins were installed as normal according to the docs.

Now 5.7 test:

MySQL [(none)]> select @@version, @@version_comment;
+-----------+-------------------------------------------------------+
| @@version | @@version_comment |
+-----------+-------------------------------------------------------+
| 5.7.13-6 | Percona Server (GPL), Release '6', Revision 'e3d58bb' |
+-----------+-------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> select @@query_response_time_stats;
+-----------------------------+
| @@query_response_time_stats |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> SELECT t1.TIME, t1.COUNT, t2.COUNT, t3.COUNT, t1.TOTAL, t2.TOTAL, t3.TOTAL FROM information_schema.query_response_time t1 join information_schema.query_response_time_read t2 on t1.TIME=t2.TIME join information_schema.query_response_time_write t3 on t2.TIME=t3.TIME;
+----------------+-------+-------+-------+----------------+----------------+----------------+
| TIME | COUNT | COUNT | COUNT | TOTAL | TOTAL | TOTAL |
+----------------+-------+-------+-------+----------------+----------------+----------------+
| 0.000001 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 0.000010 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 0.000100 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 0.001000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 0.010000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 0.100000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 1.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 10.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 100.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 1000.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 10000.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 100000.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| 1000000.000000 | 0 | 0 | 0 | 0.000000 | 0.000000 | 0.000000 |
| TOO LONG | 0 | 0 | 0 | TOO LONG | TOO LONG | TOO LONG |
+----------------+-------+-------+-------+----------------+----------------+----------------+
14 rows in set (0.03 sec)

MySQL [(none)]> set global query_response_time_stats=1;
Query OK, 0 rows affected (0.03 sec)

MySQL [(none)]> select * from mysql.db limit 1;
+-----------+-----+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+-----------------...

[Roman Vynar (roman-vynar)]

MySQL [(none)]> show plugins;
+-----------------------------+----------+--------------------+------------------------+---------+
| Name | Status | Type | Library | License |
+-----------------------------+----------+--------------------+------------------------+---------+
...
| QUERY_RESPONSE_TIME | ACTIVE | INFORMATION SCHEMA | query_response_time.so | GPL |
| QUERY_RESPONSE_TIME_AUDIT | ACTIVE | AUDIT | query_response_time.so | GPL |
| QUERY_RESPONSE_TIME_READ | ACTIVE | INFORMATION SCHEMA | query_response_time.so | GPL |
| QUERY_RESPONSE_TIME_WRITE | ACTIVE | INFORMATION SCHEMA | query_response_time.so | GPL |
+-----------------------------+----------+--------------------+------------------------+---------+
52 rows in set (0.00 sec)

[Laurynas Biveinis (laurynas-biveinis)]

The workaround is to query I_S tables using upper case names

[Laurynas Biveinis (laurynas-biveinis)]

    https://github.com/percona/percona-server/pull/1072
    https://github.com/percona/percona-server/pull/1073

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1701