Skip to content
MySQL 5.7 will reach its EOL stage in October 2023.

Do you need help upgrading to MySQL 8.0? Do you need to stay on MySQL 5.7 a bit longer? We will support you either way. Learn more
logo
Percona Server for MySQL
Response Time Distribution
Initializing search
    percona/psmysql-docs
    percona/psmysql-docs
    • Home
      • List of features available in Percona Server for MySQL releases
      • The Percona XtraDB Storage Engine
      • Percona Server for MySQL Feature Comparison
      • Changed in Percona Server 5.7
      • Understand version numbers
      • Installing Percona Server for MySQL 5.7
      • Installing Percona Server for MySQL 5.7 on Debian and Ubuntu
      • Installing Percona Server for MySQL 5.7 on Red Hat Enterprise Linux and CentOS
      • Installing _Percona Server for MySQL_ 5.7 from a Binary Tarball
      • Installing _Percona Server for MySQL_ 5.7 from a Source Tarball
      • Compiling Percona Server for MySQL 5.7 from Source
      • Installing Percona Server for MySQL 5.7 from the Git Source Tree
      • Running Percona Server for MySQL 5.7 in a Docker Container
      • Upgrade from earlier versions
      • Performing a Distribution upgrade in-place on a System with installed Percona packages
      • Upgrading using the Percona repositories
      • Upgrading using Standalone Packages
      • Percona Server In-Place Upgrading Guide: From 5.6 to 5.7
      • Post-Installation steps for Percona Server for MySQL 5.7
      • User Statistics
      • Slow Query Log
      • Extended Show Engine InnoDB Status
      • Show Storage Engines
      • Process List
      • Misc. INFORMATION_SCHEMA Tables
      • Thread Based Profiling
      • Metrics for scalability measurement
      • Response Time Distribution
        • Logging the queries in separate READ and WRITE tables
        • Installing the plugins
        • Usage
          • SELECT
          • FLUSH
          • Stored procedures
          • Collect time point
        • Version Specific Information
        • System Variables
          • query_response_time_flush
          • query_response_time_range_base
          • query_response_time_stats
          • query_response_time_session_stats
        • INFORMATION_SCHEMA Tables
          • INFORMATION_SCHEMA.QUERY_RESPONSE_TIME
          • INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ
          • INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE
      • InnoDB Page Fragmentation Counters
      • Using libcoredumper
      • Stacktrace
      • Suppress Warning Messages
      • Improved MEMORY Storage Engine
      • Restricting the number of binlog files
      • Extended mysqldump
      • Extended SELECT INTO OUTFILE/DUMPFILE
      • Per-query variable statement
      • Extended mysqlbinlog
      • Slow Query Log Rotation and Expiration
      • CSV engine mode for standard-compliant quote and comma parsing
      • Support for PROXY protocol
      • Per-session server-id
      • Compressed columns with dictionaries
      • InnoDB Full-Text Search improvements
      • Binlogging and replication improvements
      • Percona Toolkit UDFs
      • Kill Idle Transactions
      • Enforcing Storage Engine
      • Expanded Program Option Modifiers
      • XtraDB changed page tracking
      • Expanded Fast Index Creation
      • Backup Locks
      • Audit Log Plugin
      • Start transaction with consistent snapshot
      • Extended SHOW GRANTS
      • Utility user
      • PS-Admin script
      • Percona MyRocks Introduction
      • Percona MyRocks Installation Guide
      • MyRocks Limitations
      • Differences between Percona MyRocks and Facebook MyRocks
      • MyRocks Server Variables
      • MyRocks status variables
      • Gap locks detection
      • Data Loading
      • Multiple page asynchronous I/O requests
      • Query Cache Enhancements
      • Limiting the Estimation of Records in a Query
      • Improved NUMA support
      • Thread Pool
      • XtraDB Performance Improvements for I/O-Bound Highly-Concurrent Workloads
      • Prefix Index Queries Optimization
      • Too Many Connections Warning
      • Handle Corrupted Tables
      • Improved Buffer Pool Scalability
      • Improved InnoDB I/O Scalability
      • Data at Rest Encryption
      • PAM Authentication Plugin
      • SSL Improvements
      • Data Masking
      • TokuDB Introduction
      • TokuDB Installation
      • Using TokuDB
      • Fast Updates with TokuDB
      • TokuDB files and file types
      • TokuDB file management
      • TokuDB Background ANALYZE TABLE
      • TokuDB Variables
      • TokuDB Status Variables
      • TokuDB Fractal Tree Indexing
      • TokuDB Troubleshooting
      • TokuDB Performance Schema Integration
      • Percona TokuBackup
      • Frequently Asked Questions
      • Removing TokuDB storage engine
      • Release notes index
      • Percona Server for MySQL 5.7.43-47 (2023-08-17)
      • Percona Server for MySQL 5.7.42-46 (2023-06-01)
      • Percona Server for MySQL 5.7.42-45 (2023-05-23)
      • Percona Server for MySQL 5.7.41-44 (2023-03-02)
      • Percona Server for MySQL 5.7.40-43 (2022-11-28)
      • Percona Server for MySQL 5.7.39-42 (2022-08-15)
      • Percona Server for MySQL 5.7.38-41 (2022-06-02)
      • Percona Server for MySQL 5.7.37-40 (2022-03-31)
      • Percona Server for MySQL 5.7.36-39 (2021-12-22)
      • Percona Server for MySQL 5.7.35-38 (2021-08-18)
      • Percona Server for MySQL 5.7.34-37 (2021-05-26)
      • Percona Server for MySQL 5.7.33-36 (2021-03-02)
      • Percona Server for MySQL 5.7.32-35 (2020-11-24)
      • Percona Server for MySQL 5.7.31-34 (2020-08-24)
      • Percona Server for MySQL 5.7.30-33 (2020-05-20)
      • Percona Server for MySQL 5.7.29-32 (2020-02-05)
      • Percona Server for MySQL 5.7.28-31 (2019-11-13)
      • Percona Server for MySQL 5.7.27-30 (2019-08-22)
      • Percona Server for MySQL 5.7.26-29 (2019-05-27)
      • Percona Server for MySQL 5.7.25-28 (2019-02-18)
      • Percona Server for MySQL 5.7.24-27 (2018-12-18)
      • Percona Server 5.7.24-26 (2018-12-04)
      • Percona Server 5.7.23-25 (2018-11-21)
      • Percona Server 5.7.23-24 (2018-11-09)
      • Percona Server 5.7.23-23 (2018-09-12)
      • Percona Server 5.7.22-22 (2018-05-31)
      • Percona Server 5.7.21-21 (2018-04-24)
      • Percona Server 5.7.21-20 (2018-02-19)
      • Percona Server 5.7.20-19 (2018-01-03)
      • Percona Server 5.7.20-18 (2017-12-14)
      • Percona Server 5.7.19-17 (2017-08-31)
      • Percona Server for MySQL 5.7.18-16 (2017-07-28)
      • Percona Server for MySQL 5.7.18-15 (2017-05-26)
      • Percona Server for MySQL 5.7.18-14 (2017-05-12)
      • Percona Server for MySQL 5.7.17-13 (2017-04-05)
      • Percona Server for MySQL 5.7.17-12 (2017-03-24)
      • Percona Server for MySQL 5.7.17-11 (2017-02-03)
      • Percona Server for MySQL 5.7.16-10 (2016-11-28)
      • Percona Server for MySQL 5.7.15-9 (2016-10-21)
      • Percona Server for MySQL 5.7.14-8 (2016-09-21)
      • Percona Server for MySQL 5.7.14-7 (2016-08-23)
      • Percona Server for MySQL 5.7.13-6 (2016-07-16)
      • Percona Server for MySQL 5.7.12-5 (2016-06-06)
      • Percona Server for MySQL 5.7.11-4 (2016-03-15)
      • Percona Server for MySQL 5.7.10-3 (2016-02-23)
      • Percona Server for MySQL 5.7.10-2 (2016-02-05)
      • Percona Server for MySQL 5.7.10-1 (2015-12-14)
      • List of upstream MySQL bugs fixed in Percona Server for MySQL 5.7
      • List of variables introduced in Percona Server 5.7
      • Development of Percona Server for MySQL
      • Trademark policy
      • Index of INFORMATION_SCHEMA Tables
      • Frequently Asked Questions
      • Copyright and licensing information
      • Glossary

    • Logging the queries in separate READ and WRITE tables
    • Installing the plugins
    • Usage
      • SELECT
      • FLUSH
      • Stored procedures
      • Collect time point
    • Version Specific Information
    • System Variables
      • query_response_time_flush
      • query_response_time_range_base
      • query_response_time_stats
      • query_response_time_session_stats
    • INFORMATION_SCHEMA Tables
      • INFORMATION_SCHEMA.QUERY_RESPONSE_TIME
      • INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ
      • INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE

    Response Time Distribution¶

    The slow query log provides exact information about queries that take a long time to execute. Sometimes there are a large number of queries that each take a 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. The query execution time begins afterthe initial locks are acquired. The user can define time intervals that divide the range from 0 to positive infinity into smaller intervals and then collect the number of commands whose execution times fall into each of those intervals.

    Note that in a replication environment, the server will not take into account any queries executed by the replica SQL threads (whether they are slow or not) for the 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:

    +----------------+-------+------------+
    |      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 QUERY |     0 |   0.000000 |
    +----------------+-------+------------+
    

    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
    

    Logging the queries in separate READ and WRITE tables¶

    Percona Server for MySQL is now able to log the queries response times into separate READ and WRITE INFORMATION_SCHEMA tables. The two new tables are named INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ and INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE respectively. The decision on whether a query is a read or a write is based on the type of the command. Thus, for example, an UPDATE ... WHERE <condition> is always logged as a write query even if <condition> is always false and thus no actual writes happen during its execution.

    Following SQL commands will be considered as WRITE queries and will be logged into the INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE table: CREATE_TABLE, CREATE_INDEX, ALTER_TABLE, TRUNCATE, DROP_TABLE, LOAD, CREATE_DB, DROP_DB, ALTER_DB, RENAME_TABLE, DROP_INDEX, CREATE_VIEW, DROP_VIEW, CREATE_TRIGGER, DROP_TRIGGER, CREATE_EVENT, ALTER_EVENT, DROP_EVENT, UPDATE, UPDATE_MULTI, INSERT, INSERT_SELECT, DELETE, DELETE_MULTI, REPLACE, REPLACE_SELECT, CREATE_USER, RENAME_USER, DROP_USER, ALTER_USER, GRANT, REVOKE, REVOKE_ALL, OPTIMIZE, CREATE_FUNCTION, CREATE_PROCEDURE, CREATE_SPFUNCTION, DROP_PROCEDURE, DROP_FUNCTION, ALTER_PROCEDURE, ALTER_FUNCTION, INSTALL_PLUGIN, and UNINSTALL_PLUGIN. Commands not listed here are considered as READ queries and will be logged into the INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ table.

    Installing the plugins¶

    In order to enable this feature you’ll need to install the necessary plugins:

    mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME 'query_response_time.so';
    

    This plugin is used for gathering statistics.

    mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';
    

    This plugin provides the interface (INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) to output gathered statistics.

    mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_READ SONAME 'query_response_time.so';
    

    This plugin provides the interface (INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ) to output gathered statistics.

    mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_WRITE SONAME 'query_response_time.so';
    

    This plugin provides the interface (INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE) to output gathered statistics.

    You can check if plugins are installed correctly by running:

    mysql> SHOW PLUGINS;
    

    The output could be similar to the following:

    ...
    | QUERY_RESPONSE_TIME         | ACTIVE   | INFORMATION SCHEMA | query_response_time.so | GPL     |
    | QUERY_RESPONSE_TIME_AUDIT   | ACTIVE   | AUDIT              | query_response_time.so | GPL     |
    | QUERY_RESPONSE_TIME_READ    | ACTIVE   | INFORMATION SCHEMA | query_response_time.so | GPL     |
    | QUERY_RESPONSE_TIME_WRITE   | ACTIVE   | INFORMATION SCHEMA | query_response_time.so | GPL     |
    +-----------------------------+----------+--------------------+------------------------+---------+
    

    Usage¶

    To start collecting query time metrics, query_response_time_stats should be enabled:

    SET GLOBAL query_response_time_stats = on;
    

    And to make it persistent, add the same to my.cnf:

    [mysqld]
    query_response_time_stats = on
    

    SELECT¶

    You can get the distribution using the query:

    mysql> SELECT * from INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;
    

    The output could be similar to the following:

    time                   count   total
    0.000001               0       0.000000
    0.000010               0       0.000000
    0.000100               1       0.000072
    0.001000               0       0.000000
    0.010000               0       0.000000
    0.100000               0       0.000000
    1.000000               0       0.000000
    10.000000              8       47.268416
    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 QUERY         0       0.000000
    

    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.

    FLUSH¶

    Flushing can be done by setting the query_response_time_flush to ON (or 1):

    mysql> SET GLOBAL query_response_time_flush='ON';
    

    FLUSH does two things:

    • Clears the collected times from the INFORMATION_SCHEMA.QUERY_RESPONSE_TIME, INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ, and INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE tables

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

    Note

    The execution time for the FLUSH query will also be collected.

    Stored procedures¶

    Stored procedure calls count as a single query.

    Collect time point¶

    Time is collected after query execution completes (before clearing data structures).

    Version Specific Information¶

    • Percona Server for MySQL 5.7.10-1: Feature ported from Percona Server for MySQL 5.6

    System Variables¶

    query_response_time_flush¶

    Option Description
    Command-line Yes
    Config file No
    Scope Global
    Dynamic No
    Data type Boolean
    Default OFF
    Range OFF/ON

    Setting this variable to ON will flush the statistics and re-read the query_response_time_range_base.

    query_response_time_range_base¶

    Option Description
    Command-line Yes
    Config file Yes
    Scope Global
    Dynamic Yes
    Data type Numeric
    Default 10
    Range 2-1000

    Sets up the logarithm base for the scale.

    Note

    The variable takes effect only after this command has been executed:

    mysql> SET GLOBAL query_response_time_flush=1;
    

    query_response_time_stats¶

    Option Description
    Command-line Yes
    Config file Yes
    Scope Global
    Dynamic Yes
    Data type Boolean
    Default OFF
    Range ON/OFF

    This global variable enables and disables collection of query times.

    query_response_time_session_stats¶

    Option Description
    Command-line No
    Config file No
    Scope Session
    Dynamic Yes
    Data type Text
    Default GLOBAL
    Range ON/OFF/GLOBAL

    This variable enables and disables collection of query times on session level, thus customizing QRT behavior for individual connections. By default, its value is GLOBAL, which means that its value is taken from the query_response_time_stats variable.

    INFORMATION_SCHEMA Tables¶

    INFORMATION_SCHEMA.QUERY_RESPONSE_TIME¶

    Column Name Description
    ‘VARCHAR TIME’ ‘Interval range in which the query occurred’
    ‘INT(11) COUNT’ ‘Number of queries with execution times that fell into that interval’
    ‘VARCHAR TOTAL’ ‘Total execution time of the queries ‘

    INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_READ¶

    Column Name Description
    ‘VARCHAR TIME’ ‘Interval range in which the query occurred’
    ‘INT(11) COUNT’ ‘Number of queries with execution times that fell into that interval’
    ‘VARCHAR TOTAL’ ‘Total execution time of the queries ‘

    INFORMATION_SCHEMA.QUERY_RESPONSE_TIME_WRITE¶

    Column Name Description
    ‘VARCHAR TIME’ ‘Interval range in which the query occurred’
    ‘INT(11) COUNT’ ‘Number of queries with execution times that fell into that interval’
    ‘VARCHAR TOTAL’ ‘Total execution time of the queries ‘

    Contact Us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2022-09-27
    Percona LLC, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better. Read more about Percona Cookie Policy.