mysqlbackup – Mysql Enterprise Backup

https://scriptingmysql.wordpress.com/category/mysql-enterprise-backup/

Advertisements

Show MySQL process list without sleeping connections

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:

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 >=