MARIADB "Response Time" plugin

"Response Time" is a common metric for measuring database performance. In mariadb, you can use the plug-in "Query_response_time" to get statistics about the time interval of the query.

//mounting plug pieces

$ CD ${mysql_base}/lib/plugin
$ ll
-rw-r--r--. 1 root root 403898 Feb 01:15 query_response_time.so

MariaDB [(none)]> Install plugin query_response_time_audit soname ' query_response_time.so ';

MariaDB [(none)]> Install plugin query_response_time soname ' query_response_time.so ';

MariaDB [(None)]> show plugins;

... | Query_response_time_audit | ACTIVE | AUDIT | Query_response_time.so | GPL |

| Query_response_time | ACTIVE | Information SCHEMA | Query_response_time.so | GPL |

+-------------------------------+----------+--------------------+------------------------+---------+

Parameter description

mariadb [(None)]> show variables like ' query_response% ';
+--------------------------------+-------+
| variable_name | Value |
+--------------------------------+-------+
| Query_response_time_flush | OFF |
| query_response_time_range_base | |
| query_response_time_stats | OFF |
+--------------------------------+-------+
3 rows in Set (0.00 sec)

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

if the range base=10, we have the following intervals:
(0 ^-6], (ten ^-6; ^-5], (ten ^-5; ^-4], ...,
(10 ^-1; 10 ^1], (10^1; 10^2] ... (10^7; positive Infinity]

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

//Open statistics

MariaDB [(None)]> set global query_response_time_stats = 1;
Query OK, 0 rows Affected (0.00 sec)

MariaDB [(None)]> set global query_response_time_flush= ' on ';
Query OK, 0 rows Affected (0.00 sec)

MariaDB [information_schema]> Select * from INFORMATION_SCHEMA. Query_response_time;
+----------------+-------+----------------+
| Time | COUNT | Total |
+----------------+-------+----------------+
| 0.000001 | 0 | 0.000000 |
| 0.000010 | 0 | 0.000000 |
| 0.000100 | | 0.002200 |
| 0.001000 | | 0.005615 |
| 0.010000 | 0 | 0.000000 |
| 0.100000 | 0 | 0.000000 |
| 1.000000 | 0 | 0.000000 |
| 10.000000 | 0 | 0.000000 |
| 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 | 0 | TOO LONG |
+----------------+-------+----------------+
rows in Set (0.00 sec)

//Analysis
From the above record:
There are 70 queries executed in 0.000010 seconds <query Execution Time < = 0.000100 seconds in this interval, which takes a total of 0.002200 seconds.
There are 16 queries executed in 0.000100 seconds <query Execution Time < = 0.001000 seconds in this interval, which takes a total of 0.005615 seconds.

MARIADB "Response Time" plugin