Read more

Show MySQL process list without sleeping connections

Arne Hartherz
March 28, 2012Software engineer at makandra GmbH

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.

Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

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.

Posted by Arne Hartherz to makandra dev (2012-03-28 16:22)