MySQL Server - Version 5.1 and later The simplest way to find long running queries are to look at the process list. There are various options how to do this depending on your version and whether you have the Sys Schema installed.
The Sys Schema session View
In MySQL 5.6 or later with the Sys Schema installed, you can use the session view, for example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | 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.
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:
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 7 | 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) |
Hope it worked !!