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.

[MySQL Health Check]
About Maciej Dobrzanski

A MySQL consultant with the primary focus on systems, databases and application stacks performance and scalability. Expert on open source technologies such as Linux, BSD, Apache, nginx, MySQL, and many more. @linkedin

Comments

  1. I cannot agree on “For example, it does not allow to paginate through results if they are several screens long”.
    It depends on what client you use. Many GUI clients have pagination or pagination option.

    • Peter,

      Of course. This post was related to my earlier post that specifically mentioned working with the command line client. Perhaps I should have mentioned that more explicitly here.

  2. Maciej, loving your juicy tidbits of mysql tips and info :)

    More than one way to use mysql command line client, I just use a SSH client which can set a very larger scrollback buffer size i.e. secureCRT at 128,000 lines long and be able to view all the output :)

    i.e.

    mysql -e “SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != ‘Sleep'”

    or just dump the output into a file from the above command :)

  3. Rachid says:

    You can use “pager less” or “pager more” … in mysql client to paginate through results :)

Speak Your Mind

*