Logging the queries in separate READ and WRITE tables¶

Percona Server for MySQL is now able to log the queries response times into separate READ and WRITE INFORMATION_SCHEMA tables. The two new tables are named QUERY_RESPONSE_TIME_READ and QUERY_RESPONSE_TIME_WRITE respectively. The decision on whether a query is a read or a write is based on the type of the command. Thus, for example, an UPDATE ... WHERE <condition> is always logged as a write query even if <condition> is always false and thus no actual writes happen during its execution.

Following SQL commands will be considered as WRITE queries and will be logged into the QUERY_RESPONSE_TIME_WRITE table: CREATE_TABLE, CREATE_INDEX, ALTER_TABLE, TRUNCATE, DROP_TABLE, LOAD, CREATE_DB, DROP_DB, ALTER_DB, RENAME_TABLE, DROP_INDEX, CREATE_VIEW, DROP_VIEW, CREATE_TRIGGER, DROP_TRIGGER, CREATE_EVENT, ALTER_EVENT, DROP_EVENT, UPDATE, UPDATE_MULTI, INSERT, INSERT_SELECT, DELETE, DELETE_MULTI, REPLACE, REPLACE_SELECT, CREATE_USER, RENAME_USER, DROP_USER, ALTER_USER, GRANT, REVOKE, REVOKE_ALL, OPTIMIZE, CREATE_FUNCTION, CREATE_PROCEDURE, CREATE_SPFUNCTION, DROP_PROCEDURE, DROP_FUNCTION, ALTER_PROCEDURE, ALTER_FUNCTION, INSTALL_PLUGIN, and UNINSTALL_PLUGIN. Commands not listed here are considered as READ queries and will be logged into the QUERY_RESPONSE_TIME_READ table.

Installing the plugins¶

In order to enable this feature you’ll need to install the necessary plugins:

mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME 'query_response_time.so';

This plugin is used for gathering statistics.

mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';

This plugin provides the interface (QUERY_RESPONSE_TIME) to output gathered statistics.

mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_READ SONAME 'query_response_time.so';

This plugin provides the interface (QUERY_RESPONSE_TIME_READ) to output gathered statistics.

mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_WRITE SONAME 'query_response_time.so';

This plugin provides the interface (QUERY_RESPONSE_TIME_WRITE) to output gathered statistics.

You can check if plugins are installed correctly by running:

mysql> SHOW PLUGINS;

...
| 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     |
+-----------------------------+----------+--------------------+------------------------+---------+

Usage¶

To start collecting query time metrics, query_response_time_stats should be enabled:

SET GLOBAL query_response_time_stats = on;

And to make it persistent, add the same to my.cnf:

[mysqld]
query_response_time_stats = on

mysql> SELECT * from INFORMATION_SCHEMA.QUERY_RESPONSE_TIME
time                   count   total
0.000001               0       0.000000
0.000010               0       0.000000
0.000100               1       0.000072
0.001000               0       0.000000
0.010000               0       0.000000
0.100000               0       0.000000
1.000000               0       0.000000
10.000000              8       47.268416
100.000000             0       0.000000
1000.000000            0       0.000000
10000.000000           0       0.000000
100000.000000          0       0.000000
1000000.000000         0       0.000000
TOO LONG QUERY         0       0.000000

SELECT c.count, c.time,
(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count,
(SELECT COUNT(*)     FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count,
(SELECT COUNT(*)     FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count
FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0;

mysql> SET GLOBAL query_response_time_flush='ON';

Version Specific Information¶

• 5.7.10-1: Feature ported from Percona Server for MySQL 5.6

System Variables¶

variable query_response_time_flush¶

Command Line:	Yes
Config File:	No
Scope:	Global
Dynamic:	No
Variable Type:	Boolean
Default Value:	OFF
Range:	OFF/ON

Setting this variable to ON will flush the statistics and re-read the query_response_time_range_base.

variable query_response_time_range_base¶

Command Line:	Yes
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Variable Type:	Numeric
Default Value:	10
Range:	2-1000

Sets up the logarithm base for the scale.

NOTE: The variable takes effect only after this command has been executed:

mysql> SET GLOBAL query_response_time_flush=1;

variable query_response_time_stats¶

Command Line:	Yes
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Variable Type:	Boolean
Default Value:	OFF
Range:	ON/OFF

This global variable enables and disables collection of query times.

variable query_response_time_session_stats¶

Command Line:	No
Config File:	No
Scope:	Session
Dynamic:	Yes
Variable Type:	Text
Default Value:	GLOBAL
Range:	ON/OFF/GLOBAL

This variable enables and disables collection of query times on session level, thus customizing QRT behavior for individual connections. By default, its value is GLOBAL, which means that its value is taken from the query_response_time_stats variable.

INFORMATION_SCHEMA Tables¶

table INFORMATION_SCHEMA.QUERY_RESPONSE_TIME¶

Columns:	TIME (VARCHAR) – Interval range in which the query occurred COUNT (INT(11)) – Number of queries with execution times that fell into that interval TOTAL (VARCHAR) – Total execution time of the queries

table INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ¶

Columns:	TIME (VARCHAR) – Interval range in which the query occurred COUNT (INT(11)) – Number of queries with execution times that fell into that interval TOTAL (VARCHAR) – Total execution time of the queries

table INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE¶

Columns:	TIME (VARCHAR) – Interval range in which the query occurred COUNT (INT(11)) – Number of queries with execution times that fell into that interval TOTAL (VARCHAR) – Total execution time of the queries