One of the most useful MySQL troubleshooting tools is the command SHOW (FULL) PROCESSLIST. However, in many cases, you may find that what is returned is a long list of open threads, the vast majority of which are in the SLEEP state. These threads provide little useful information for troubleshooting purposes, and so we can suppress their output in the following ways described in the resolution below.

This article shows a couple of ways in which you can run a SHOW (FULL) PROCESSLIST to hide sleeping connections.

Firstly, we can use the pager command to pass the output of the SHOW (FULL) PROCESSLIST command to grep, where we can filter out the sleeping threads. We can do this with the following syntax:

pager grep -v Sleep

Note that the command character \P is functionally equivalent to pager, and so either can be used.  Setting this pager changes the output of the SHOW (FULL) PROCESSLIST command from this:

mysql> show full processlist;
+---------+------+-----------------+--------------+---------+------+----------+-----------------------+
| Id      | User | Host            | db           | Command | Time | State    | Info                  |
+---------+------+-----------------+--------------+---------+------+----------+-----------------------+
|      16 | root | localhost       | logappconfig | Sleep   |    0 |          | NULL                  |
|      30 | root | localhost       | logapprtrpt  | Sleep   |   13 |          | NULL                  |
|      38 | root | localhost       | logappconfig | Sleep   |    0 |          | NULL                  |
|     170 | root | localhost:42663 | logapprtrpt  | Sleep   |  176 |          | NULL                  |
|     180 | root | localhost:42665 | logappconfig | Sleep   |   50 |          | NULL                  |
|     183 | root | localhost:42666 | logappconfig | Sleep   |  110 |          | NULL                  |
|     190 | root | localhost:42669 | NULL         | Sleep   |   24 |          | NULL                  |
|     204 | root | localhost       | logappconfig | Sleep   |    1 |          | NULL                  |
|   18347 | root | localhost:44052 | logappconfig | Sleep   |  170 |          | NULL                  |
| 2913083 | root | localhost:47444 | logapplogu   | Sleep   |   52 |          | NULL                  |
| 2913145 | root | localhost:47464 | logapplogu   | Sleep   |  883 |          | NULL                  |
| 2913195 | root | localhost:47474 | logappconfig | Sleep   |  204 |          | NULL                  |
| 2913237 | root | localhost:47479 | logappconfig | Sleep   |   10 |          | NULL                  |
| 2913240 | root | localhost:47481 | logappconfig | Sleep   |   16 |          | NULL                  |
| 2913250 | root | localhost:47484 | logapprtrpt  | Sleep   |  226 |          | NULL                  |
| 2913320 | root | localhost:47514 | logapprtrpt  | Sleep   |  158 |          | NULL                  |
| 2913327 | root | localhost:47517 | logappconfig | Sleep   |   16 |          | NULL                  |
| 2913362 | root | localhost:47519 | logappconfig | Sleep   |   16 |          | NULL                  |
| 2913363 | root | localhost:47520 | logappconfig | Sleep   |   10 |          | NULL                  |
| 2913364 | root | localhost:47521 | logappconfig | Sleep   |   10 |          | NULL                  |
| 4108041 | root | localhost       | logappconfig | Query   |    0 | starting | show full processlist |
+---------+------+-----------------+--------------+---------+------+----------+-----------------------+
21 rows in set (0.00 sec)

to this:

mysql> pager grep -v Sleep
PAGER set to 'grep -v Sleep'
mysql> show full processlist;
+---------+------+-----------------+--------------+---------+------+----------+-----------------------+
| Id      | User | Host            | db           | Command | Time | State    | Info                  |
+---------+------+-----------------+--------------+---------+------+----------+-----------------------+
| 4108041 | root | localhost       | logappconfig | Query   |    0 | starting | show full processlist |
+---------+------+-----------------+--------------+---------+------+----------+-----------------------+
21 rows in set (0.00 sec)

Secondly, we can also get the same data from the information_schema.processlist table.  As we can query this using standard SQL, we can write a query like this:

select * FROM information_schema.processlist WHERE command != 'Sleep' order by id;

This returns the following example output:

mysql> select * FROM information_schema.processlist WHERE command != 'Sleep' order by id;
+---------+------+-----------+--------------+---------+------+-----------+-----------------------------------------------------------------------------------+
| ID      | USER | HOST      | DB           | COMMAND | TIME | STATE     | INFO                                                                              |
+---------+------+-----------+--------------+---------+------+-----------+-----------------------------------------------------------------------------------+
| 4113216 | root | localhost | logappconfig | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY id |
+---------+------+-----------+--------------+---------+------+-----------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As this is a standard SQL query, it is also possible to order the result set using any of the table columns by changing the column name in the ORDER BY clause.