MariaDB starting with 10.0.4

This plugin was first released in MariaDB 10.0.4

The slow query log provides exact information about queries that take a long time to execute. However, sometimes there are a large number of queries that each take a very short amount of time to execute. This feature provides a tool for analyzing that information by counting and displaying the number of queries according to the the length of time they took to execute. The user can define time intervals that divide the range 0 to positive infinity into smaller intervals and then collect the number of commands whose execution times fall into each of those intervals.

The plugin creates a new information_schema table, INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.

This feature is based on Percona's Response Time Distribution.

Each interval is described as:

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

The range_base is some positive number (see Limitations). The interval is defined as the difference between two nearby powers of the range base.

For example, if the range base=10, we have the following intervals:

(0; 10 ^ -6], (10 ^ -6; 10 ^ -5], (10 ^ -5; 10 ^ -4], ..., 
  (10 ^ -1; 10 ^1], (10^1; 10^2]...(10^7; positive infinity]

or

(0; 0.000001], (0.000001; 0.000010], (0.000010; 0.000100], ..., 
  (0.100000; 1.0]; (1.0; 10.0]...(1000000; positive infinity]

For each interval, a count is made of the queries with execution times that fell into that interval.

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:

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

Versions

VersionStatusIntroduced
1.0StableMariaDB 10.1.13
1.0Gamma

Installation

This feature consists of two plugins:

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

Both plugins need to be activated to get meaningful statistics. Using SQL:

INSTALL SONAME 'query_response_time';

or command-line:

--plugin-load=query_response_time

System variables for configuration

The plugin can be configured with the following system variables:

query_response_time_flush

  • Description: Updating this variable flushes the statistics and re-reads query_response_time_range_base.
  • Commandline: None
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

query_response_time_range_base

  • Description: Select base of log for QUERY_RESPONSE_TIME ranges. WARNING: variable change takes affect only after flush.
  • Commandline: --query-response-time-range-base=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10
  • Range: 2 to 1000

query_response_time_range_exec_time_debug

  • Description: Pretend queries take this many microseconds. When 0 (the default) use the actual execution time. Used only for debugging.

query_response_time_stats

  • Description: Enable or disable query response time statistics collecting
  • Commandline: query-response-time-stats[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

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

MariaDB starting with 10.1.1

As an alternative to SELECT, you can also use

SHOW QUERY_RESPONSE_TIME;
MariaDB until 10.1.1

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

FLUSH

Flushing can be done with:

MariaDB starting with 10.1.1
FLUSH QUERY_RESPONSE_TIME;

or with

SET GLOBAL query_response_time_flush=1;
MariaDB until 10.1.1

Unlike in the original patch, FLUSH QUERY_RESPONSE_TIME is not supported. Use

SET GLOBAL query_response_time_flush=1;

It does two things:

Comments

Comments loading...
Loading