MySQL: How to add where clause to processlist

Posted August 15, 2011 * Comments(3)

MySQL’s show full processlist is a great way to monitor and diagnose what is going on with the database server at certain moment.

However often it is needed to have an aggregated view or evaluate only specific type of users and\or queries.

The solution turned out to be pretty simple since show full processlist is just an alias for a regular query to processlist table within information_schema database.

So the following queries are equivalent:

SHOW FULL PROCESSLIST;
 
SELECT * FROM information_schema.processlist;

As result it is possible to present the data from process list any convenient way. There are some basic examples below.

-- display number of connections for each user
SELECT `USER`, COUNT(*) FROM information_schema.processlist
GROUP BY `USER`;
 
-- display number of connections for each host
SELECT `HOST`, COUNT(*) FROM information_schema.processlist
GROUP BY `HOST`;
 
-- display root user activity
SELECT * FROM information_schema.processlist
WHERE `USER` = 'root';
 
-- display processes associated with SELECT queries
SELECT * FROM information_schema.processlist
WHERE `INFO` LIKE 'SELECT %';
 
-- display average query time for each database
SELECT `DB`, AVG(`TIME`) FROM information_schema.processlist
GROUP BY `DB`;

3 Comments so far

  1. MAD on December 27th, 2013

    What version?

  2. Alex on December 31st, 2013

    Hi there,
    I believe it is available since MySQL 5.
    Hope this helps!

  3. Frank on December 9th, 2015

    SELECT `HOST`, COUNT(*) FROM information_schema.processlist
    GROUP BY `HOST`;

    It does not work, host value has port part.

Leave a reply