Show MySQL process list without sleeping connections

Updated . Posted . Visible to the public.

Usually you don't need to, but when you want to see which queries your MySQL server currently needs to handle (and if there are locks, etc), you could say SHOW PROCESSLIST in a MySQL shell.

Unfortunately, SHOW PROCESSLIST does not allow filtering. When you are on MySQL ≥ 5.1.7, do this instead:

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY id;

That also allows you to only show some values or order differently, like so:

SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC, id;

One more example -- see all non-sleeping queries that take some time, for example at least 2 seconds:

SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' AND time >= 2 ORDER BY time DESC, id;

Hint:

When inspecting the process list in a mysql shell, the vertical layout is often more helpful. Use \G instead of ;

SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' AND time >= 2 ORDER BY time DESC, id \G

There is an alternative to hide all sleeping processes in MYSQL processlist.

Arne Hartherz
Last edit
Thomas Eisenbarth
License
Source code in this card is licensed under the MIT License.
Posted by Arne Hartherz to makandra dev (2012-03-28 14:22)