Query Response Time Plugin

The query_response_time plugin creates the QUERY_RESPONSE_TIME table in the INFORMATION_SCHEMA database. The plugin also adds the SHOW QUERY_RESPONSE_TIME and FLUSH QUERY_RESPONSE_TIME statements.

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 length of time they took to execute.

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

Installing the Plugin

Although the plugin's shared library is distributed with MariaDB by default, the plugins are not actually installed by MariaDB by default. There are two methods that can be used to install the plugins with MariaDB.

The first method can be used to install the plugin library without restarting the server. You can install the plugins dynamically by executing or :

The second method can be used to tell the server to load the plugin library when it starts up. The plugins can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an :

Note that in both cases you have to activate data collection by changing the setting to ON, it is OFF by default even when the plugin library is loaded.

You can change the setting at runtime with

or in the options file after the plugin has been loaded:

Uninstalling the Plugin

You can uninstall the plugin dynamically by executing or :

If you installed the plugin by providing the or the options in a relevant server in an , then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.

Response Time Distribution

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.

Each interval is described as:

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

If the range base equals 10, we have the following intervals:

or

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:

or

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:

This means there were:

Using the Plugin

Using the Information Schema Table

You can get the distribution by querying the table in the database:

You can also write more complex queries:

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

Using the SHOW Statement

As an alternative to the table in the database, you can also use the statement:

Flushing Plugin Data

Flushing the plugin data does two things:

• Clears the collected times from the table in the database.

• Reads the value of and uses it to set the range base for the table.

Plugin data can be flushed with the statement:

Setting the system variable has the same effect:

System Variables

query_response_time_flush

• Description: Updating this variable flushes the statistics and re-reads .

• Command line: None

• Scope: Global

• Dynamic: Yes

query_response_time_range_base

• Description: Select base of log for QUERY_RESPONSE_TIME ranges. WARNING: variable change takes affect only after flush.

• Command line: --query-response-time-range-base=#

• Scope: Global

• Dynamic: Yes

query_response_time_exec_time_debug

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

  • This system variable is only available when the plugin is a .

• Scope: Global

• Dynamic: Yes

query_response_time_session_stats

• Description: Controls query response time statistics collection for the current session: ON - enable, OFF - disable, GLOBAL (default) - use value.

• Command line: query-response-time-session-stats=val]

• Scope: Global, Session

query_response_time_stats

• Description: Enable or disable query response time statistics collecting.

• Command line: query-response-time-stats[={0|1}]

• Scope: Global

• Dynamic: Yes

Options

query_response_time

• Description: Controls how the server should treat the plugin when the server starts up.

  • Valid values are:

    • OFF - Disables the plugin without removing it from the table.

query_response_time_audit

• Description: Controls how the server should treat the plugin when the server starts up.

  • Valid values are:

    • OFF - Disables the plugin without removing it from the table.

_{This page is licensed: CC BY-SA / Gnu FDL}