Anohter way to work with MySQL process list

In an earlier post titled "How to work with a long process list in MySQL", we showed a neat way to work with the process list by using various shell tools. But some of that can also be done using pure SQL.

Since version 5.0 a lot of MySQL meta and runtime information can be accessed by reading from predefined views in a database called INFORMATION_SCHEMA. The information which can be found there includes lists of threads, tables, user created views, triggers, stored procedures and many, many other things. The newer the MySQL version, the more items can found in there.

This post is about the process list, so it will focus on just one of the tables called PROCESSLIST. Its structure is virtually identical to what SHOW PROCESSLIST command returns.

mysql> DESC INFORMATION_SCHEMA.PROCESSLIST;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| ID            | bigint(4)           | NO   |     | 0       |       |
| USER          | varchar(16)         | NO   |     |         |       |
| HOST          | varchar(64)         | NO   |     |         |       |
| DB            | varchar(64)         | YES  |     | NULL    |       |
| COMMAND       | varchar(16)         | NO   |     |         |       |
| TIME          | int(7)              | NO   |     | 0       |       |
| STATE         | varchar(64)         | YES  |     | NULL    |       |
| INFO          | longtext            | YES  |     | NULL    |       |
| TIME_MS       | bigint(21)          | NO   |     | 0       |       |
| ROWS_SENT     | bigint(21) unsigned | NO   |     | 0       |       |
| ROWS_EXAMINED | bigint(21) unsigned | NO   |     | 0       |       |
| ROWS_READ     | bigint(21) unsigned | NO   |     | 0       |       |
+---------------+---------------------+------+-----+---------+-------+

As this is a regular table, it can be queried with a regular SQL. Those queries can use any of the columns for filtering, sorting or even grouping. Anything to get the information that is needed out. For example:

mysql> SELECT ID, USER, STATE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'replication';
+--------+-------------+------------------------------------------------------------------+
| id     | User        | state                                                            |
+--------+-------------+------------------------------------------------------------------+
| 21621  | replication | Master has sent all binlog to slave; waiting for binlog to be up |
+--------+-------------+------------------------------------------------------------------+

Show only database connections that are doing something:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep'

Sort sleeping connections by age:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Sleep' ORDER BY TIME DESC

Using this method is definitely cleaner approach to browsing the process list than by using shell tools. It is also easier for those familiar with MySQL, but not necessarily knowing what awk or grep are. On the other hand, it still has some limitations. For example, it does not allow to paginate through results if they are several screens long. That's why using the both methods together to complement each other, is the best way to go.

Blog