QUERY_RESPONSE_TIME plugin

Lo slow query log fornisce informazioni precise sulle query che impiegano troppo tempo. Però a volte si ha un elevato numero di query, ognuna della quali impiega un tempo molto breve. Questa funzionalità consiste in uno strumento per analizzare queste informazioni contando e visualizzando il numero di query secondo la quantità di tempo impiegata per l'esecuzione. L'utente può definire gli intervalli di tempo che dividono la gamma da 0 a infinito positivo in intervalli più piccoli, e poi raccoglie il numero dei comandi il cui tempo di esecuzione cade in ognuno di questi intervalli.

Questa funzionalità è basata su Response Time Distribution, di Percona.

Ogni intervallo è descritto come:

(intervallo_base ^ n; intervallo_base ^ (n+1)]

L'intervallo_base è un numero positivo (vedi Limitazioni). L'intervallo è definito come la differenza tra due potenze dell'intervallo base.

Per esempio, se intervallo_base=10, si hanno i seguenti intervalli:

(0; 10 ^ -6], (10 ^ -6; 10 ^ -5], (10 ^ -5; 10 ^ -4], ..., (10 ^ -1; 10 ^1], (10^1; 10^2]...(10^7; infinito positivo]

oppure

(0; 0.000001], (0.000001; 0.000010], (0.000010; 0.000100], ..., (0.100000; 1.0]; (1.0; 10.0]...(1000000; infinito positivo]

Per ognuno degli intervalli, vengono contate le query il cui tempo di esecuzione ricade nell'intervallo dato.

You can select the range of the intervals by changing the range base. For example, for base range=2 we have the following intervals:

(0; 2 ^ -19], (2 ^ -19; 2 ^ -18], (2 ^ -18; 2 ^ -17], ..., (2 ^ -1; 2 ^1], (2 ^ 1; 2 ^ 2]...(2 ^ 25; positive infinity]

or

(0; 0.000001], (0.000001, 0.000003], ..., (0.25; 0.5], (0.5; 2], (2; 4]...(8388608; positive infinity]

Small numbers look strange (i.e., don’t look like powers of 2), because we lose precision on division when the ranges are calculated at runtime. In the resulting table, you look at the high boundary of the range.

For example, you may see:

MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;
+----------------+-------+----------------+
| TIME           | COUNT | TOTAL          |
+----------------+-------+----------------+
|       0.000001 |     0 |       0.000000 |
|       0.000010 |    17 |       0.000094 |
|       0.000100 |  4301         0.236555 |
|       0.001000 |  1499 |       0.824450 |
|       0.010000 | 14851 |      81.680502 |
|       0.100000 |  8066 |     443.635693 |
|       1.000000 |     0 |       0.000000 |
|      10.000000 |     0 |       0.000000 |
|     100.000000 |     1 |      55.937094 |
|    1000.000000 |     0 |       0.000000 |
|   10000.000000 |     0 |       0.000000 |
|  100000.000000 |     0 |       0.000000 |
| 1000000.000000 |     0 |       0.000000 |
| TOO LONG       |     0 | TOO LONG       |
+----------------+-------+----------------+

This means there were:

* 17 queries with 0.000001 < query execution time < = 0.000010 seconds; total execution time of the 17 queries = 0.000094 seconds

* 4301 queries with 0.000010 < query execution time < = 0.000100 seconds; total execution time of the 4301 queries = 0.236555 seconds

* 1499 queries with 0.000100 < query execution time < = 0.001000 seconds; total execution time of the 1499 queries = 0.824450 seconds

* 14851 queries with 0.001000 < query execution time < = 0.010000 seconds; total execution time of the 14851 queries = 81.680502 seconds

* 8066 queries with 0.010000 < query execution time < = 0.100000 seconds; total execution time of the 8066 queries = 443.635693 seconds

* 1 query with 10.000000 < query execution time < = 100.0000 seconds; total execution time of the 1 query = 55.937094 seconds

Installation

This feature consists of two plugins:

• QUERY_RESPONSE_TIME - INFORMATION_SCHEMA plugin, exposes statistic.
• QUERY_RESPONSE_TIME_AUDIT - audit plugin, collects statistic.

Both plugins need to be activate to get meaningful statistic. Using SQL interface:

INSTALL SONAME 'query_response_time';

or command-line:

--plugin-load=query_response_time

Configuration

The plugin can be configured with the following system variables:

Usage

SELECT

You can get the distribution using the query:

SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;

You can write a complex query like:

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;

Note: If query_response_time_stats is ON, the execution times for these two SELECT queries will also be collected.

SHOW

Unlike in the original patch, SHOW QUERY_RESPONSE_TIME is not supported.

FLUSH

Unlike in original patch, FLUSH QUERY_RESPONSE_TIME is not supported. Flushing can be done with:

SET GLOBAL query_response_time_flush=1;

It does two things:

• Clears the collected times from INFORMATION_SCHEMA.QUERY_RESPONSE_TIME table
• Reads the value of query_response_time_range_base and uses it to set the range base for the table