Handy One-Liner to Check for Long MySQL Queries

When my Nagios server alerts me that a MySQL server is running slow, I like to check for long-running queries.

If you have a very busy MySQL server with tons of lines showing up when you hit mysqladmin processlist, it can be hard to find the queries that are taking the longest.

This one-liner is my go-to command for that:

mysql -e "SHOW FULL PROCESSLIST" -s -N \
| awk -F '\t' 'NR>1 && $5 != "Sleep" && $5 != "Binlog Dump" {print $0}' \
| sort -k6,6nr \
| awk 'BEGIN {print "Id\tUser\tHost\tDb\tCommand\tTime\tState\tInfo"} {print $0}'

This one-liner will show you all queries that are currently running on a MySQL server, sorted by the longest running queries first. It will also show you the user, host, database, command, state, and query info.

You should have your username and password stored in ~/.my.cnf so you don’t have to enter it every time you run this command.