MySQL one-liner for monitoring long queries on the console

README.md

MOTIVATION

The usual advice for monitoring MySQL is to run SHOW PROCESSLIST. However, that's not ordered or filtered, and the entire text of queries is shown. For long queries, e.g. batch-type queries identifying specific IDs, can be very long and end up flooding your terminal, making the output troublesome to comprehend.

Fortunately we can query the state as if it were a regular table. The query here does that. It ignores sleeping processes (common if clients are using connection pools), filters slow processes (those began over 5 seconds), and truncates the query to your defined length.

MySQL monitoring

+---------+--------+------------+-------------------+---------+------+----------+------------------------------------------------------------------------------------------------------------------------------+
| ID      | USER   | HOST       | DB                | COMMAND | TIME | STATE    | QUERY                                                                                                                        |
+---------+--------+------------+-------------------+---------+------+----------+------------------------------------------------------------------------------------------------------------------------------+
| 2786271 | appy   | borg:89700 | global_app_center | Query   |   12 | updating | SELECT `posts` FROM `blog` WHERE `authors`.`id` IN ( 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, 1597, 2..   |                                     |
+---------+--------+------------+-------------------+---------+------+----------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

query.sql

set @width=160; select ID, USER, HOST, DB, COMMAND, TIME, STATE, concat(substr(INFO,1,@width),IF(LENGTH(INFO) > @width, ' ...', '')) as QUERY from INFORMATION_SCHEMA.PROCESSLIST where time > 5 and COMMAND <> 'Sleep';