Posted about 7 years ago. Visible to the public. Linked content.

Show MySQL process list without sleeping connections

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:

Copy
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:

Copy
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:

Copy
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 ;
Copy
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.

By refactoring problematic code and creating automated tests, makandra can vastly improve the maintainability of your Rails application.

Owner of this card:

Avatar
Arne Hartherz
Last edit:
over 3 years ago
by Thomas Eisenbarth
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more