LP #1552428: QUERY_RESPONSE_TIME_READ & QUERY_RESPONSE_TIME_WRITE broken if accessed through non-uppercase name
Description
Environment
Smart Checklist
Activity
lpjirasync January 22, 2018 at 1:04 PM
**Comment from Launchpad by: Laurynas Biveinis on: 27-09-2016 10:13:32
https://github.com/percona/percona-server/pull/1072
https://github.com/percona/percona-server/pull/1073
lpjirasync January 22, 2018 at 1:04 PM
**Comment from Launchpad by: Laurynas Biveinis on: 27-09-2016 06:10:54
The workaround is to query I_S tables using upper case names
lpjirasync January 22, 2018 at 1:04 PM
**Comment from Launchpad by: Roman Vynar on: 26-09-2016 07:34:48
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)
lpjirasync January 22, 2018 at 1:04 PM
**Comment from Launchpad by: Roman Vynar on: 26-09-2016 07:32:25
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;
---------------+-----------------------------------+-------------------------------------+--------------------------------------+----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------
Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
---------------+-----------------------------------+-------------------------------------+--------------------------------------+----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------
localhost | sys | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y |
---------------+-----------------------------------+-------------------------------------+--------------------------------------+----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------
1 row in set (0.00 sec)
MySQL [(none)]> delete from sbtest.sbtest;
Query OK, 1000 rows affected (0.12 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 | 3 | 3 | 3 | 0.000007 | 0.000007 | 0.000007 |
0.000100 | 19 | 19 | 19 | 0.000624 | 0.000624 | 0.000624 |
0.001000 | 11 | 11 | 11 | 0.002885 | 0.002885 | 0.002885 |
0.010000 | 2 | 2 | 2 | 0.003839 | 0.003839 | 0.003839 |
0.100000 | 1 | 1 | 1 | 0.038572 | 0.038572 | 0.038572 |
1.000000 | 1 | 1 | 1 | 0.118068 | 0.118068 | 0.118068 |
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)
lpjirasync January 22, 2018 at 1:04 PM
**Comment from Launchpad by: Laurynas Biveinis on: 26-09-2016 07:19:20
Cannot reproduce so far - please provide your plugins' installation procedure and workload?
Details
Assignee
UnassignedUnassignedReporter
lpjirasynclpjirasync(Deactivated)Priority
High
Details
Details
Assignee
Reporter
Priority
HighSmart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Smart Checklist
**Reported in Launchpad by Roman Vynar last update 27-09-2016 10:13:46
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