• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL: How To Find Queries Taking Longer Than N Seconds

by admin

Question: How to find queries taking more than a given amount of time to complete?

The simplest way to find long-running queries is to look at the process list. There are various options for how to do this depending on your version and whether you have the Sys Schema installed. The examples in this post will all look for queries that have taken longer than 10 seconds. Change the duration as required.

The Sys Schema session View

In MySQL 5.6 or later with the Sys Schema installed, you can use the session view, for example:

mysql> SELECT * FROM sys.session WHERE command = 'Query' AND time > 10\G
*************************** 1. row ***************************
                thd_id: 36
               conn_id: 11
                  user: root@localhost
                    db: db1
               command: Query
                 state: copy to tmp table
                  time: 244
     current_statement: ALTER TABLE MegaCity ENGINE=InnoDB
     statement_latency: 4.06 m
              progress: 52.73
          lock_latency: 21.55 ms
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 296.29 KiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: NULL
             trx_state: NULL
        trx_autocommit: NULL
                   pid: 11884
          program_name: mysql
1 row in set (0.08 sec)

The session view is the same as the sys.processlist view with the difference that the session view excludes background threads. An advantage of the session view over the methods described below is that it will include memory usage and progress information if available. For example in the above output, the query is estimated to have completed 52.73% of the work and is currently using 296.29 KiB of memory.

Both the progress estimation and the memory usage are not exact. Particularly the memory usage depends on the enabled Performance Schema instruments and the coverage of the instruments in the code. The progress information can be used to help determine whether the query should be killed or allowed to complete.

The Performance Schema threads table

In MySQL 5.6 and later it is recommended to use the performance_schema.threads table over SHOW PROCESSLIST or the information_schema.PROCESSLIST as using the threads table has less impact on the running queries. The Sys Schema views processlist and session discussed above are built on top of the threads table. To get long running queries, you can for example use:

mysql> SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_COMMAND = 'Query' AND PROCESSLIST_TIME > 10;
+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------+-------------------------------------------------+
| PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE         | PROCESSLIST_INFO                                |
+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------+-------------------------------------------------+
|             11 | root             | localhost        | db1            | Query               |               49 | Searching rows for update | UPDATE MegaCity SET Population = Population + 1 |
+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------+-------------------------------------------------+
1 row in set (0.00 sec)

The Information Schema PROCESSLIST table

In MySQL 5.1 and 5.5, the above solutions cannot be used. Instead, use the PROCESSLIST table in the Information Schema. Querying the PROCESSLIST table is equivalent to executing SHOW PROCESSLIST with the difference that you can specify a WHERE clause.

The Information Schema PROCESSLIST table (and SHOW PROCESSLIST) requires a mutex that can affect server performance. For this reason, it is recommended to use one of the above Performance Schema based methods in MySQL 5.6 and later and to avoid querying the PROCESSLIST table or SHOW PROCESSLIST frequently.

For example:

mysql> SELECT * FROM information_schema.PROCESSLIST WHERE Command = 'Query' AND TIME > 10;
+----+------+-----------+------+---------+------+---------------------------+-------------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE                     | INFO                                            |
+----+------+-----------+------+---------+------+---------------------------+-------------------------------------------------+
| 11 | root | localhost | db1  | Query   |   24 | Searching rows for update | UPDATE MegaCity SET Population = Population + 1 |
+----+------+-----------+------+---------+------+---------------------------+-------------------------------------------------+
1 row in set (0.00 sec)

Filed Under: mysql

Some more articles you might also be interested in …

  1. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
  2. How to use mysqlsh to execute addInstance in silent mode
  3. how to capture the SQL statements of a binary log and edit them before restoring the data (Point-in-time Recovery for MySQL)
  4. How to obtain MySQL metadata (metadata access methods)
  5. How to install and configure MySQL sys schema
  6. What’s a good process to find and eliminate slow queries in MySQL
  7. Which Character Set Should Be Used To Store Emojis in MySQL Database
  8. How to Perform a Rolling Restart of MySQL NDB Cluster
  9. How to Restart a MySQL Cluster without downtime
  10. Configure MySQL Router to Auto Restart of Failure using systemd

You May Also Like

Primary Sidebar

Recent Posts

  • “aws workmail” Command Examples
  • aws-vault Command Examples (A vault for securely storing and accessing AWS credentials in development environments)
  • “aws sts” Command Examples
  • “aws sqs” Command Examples

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright