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:15query_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 | 10 |

| Query_response_time_stats | OFF |

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

3 Rows in Set (0.00 sec)

Query_response_time_flush

description:updatingthis variable flushes the statistics and re-readsquery_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 forquery_response_time ranges. Warning:variable change takes affect only afterflush.
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 followingintervals:
(0; ^-6], (ten ^-6; ^-5], (ten ^-5; 10^-4], ...,
(10 ^-1; 10 ^1], (10^1; 10^2] ... (10^7;positive Infinity]

Query_response_time_stats
description:enable or disable query ResponseTime 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 * Frominformation_schema. Query_response_time;

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

| Time | COUNT | Total |

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

|     0.000001 |       0 | 0.000000 |

|     0.000010 |       0 | 0.000000 |

|    0.000100 |       70 | 0.002200 |

|    0.001000 |       16 | 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 executiontime < = 0.000100 seconds in this interval, and the total time spent is 0.002200 seconds.
There are 16 queries executed in 0.000100 seconds <query executiontime < = 0.001000 seconds in this interval, and the total time spent is 0.005615 seconds.

MARIADB "Response Time" plugin