Once your Mysql server gets into troubles and you need to identify SQLs with the worst performance, the first thing every server administrator is doing is to open mysql console and start digging in the SHOW PROCESSLIST or SHOW FULL PROCESSLIST.
On servers with many concurent connections to many databases you will see a lot of of rows with commands with text: Sleep.
In time when I'm hunting slow sqls I don't want to be disturbed with those sleeping connections and I want to see just real troublemaking SQLs.
Solution for me on newer Mysql versions is to not use SHOW PROCESSLIST command, but SQL loading data from information schema:
Sometimes I prefer view, when results are output as table (character ; at the end of sql command):
SELECT * FROM information_schema.processlist WHERE command != 'Sleep' and time>1 and user <> 'system user' and user <> 'replicator' order by time;
In case SQLs are too long, I prefer to use different view with \G at the end of command:
SELECT * FROM information_schema.processlist WHERE command != 'Sleep' and time>1 and user <> 'system user' and user <> 'replicator' order by time\G
Comapring to SHOW PROCESSLIST command you have some advantages:
- you can show just rows, where execution time is matching your criteria
- you can filter out connections of some users (e.g. in my example I have filtered out replicator user and system user)
- you can sort results as you need