Catching slow queries with mysql client

There might be times when you just need to check all currently running queries so to catch any possible slow ones. Then, instead of enabling the mysql slow log it’s easier to check these with the mysql client using the SHOW FULL PROCESSLIST statement. You could also use the shorter form, SHOW PROCESSLIST, but keep in mind that then you are going to get only the first 100 characters of the listed statements.
Following is example usage:

mysql> SHOW FULL PROCESSLIST\G

and the result should be something like:

*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: <code>SHOW FULL PROCESSLIST</code>
*************************** 2. row ***************************
     Id: 3
   User: site
   Host: localhost
     db: sitedb
Command: Query
   Time: 56
  State: Writing to net
   Info: select * from big_table, another_big_table
2 rows in set (0.08 sec)

as you can see the output is quite straightforward so won’t explain all of the fields, but the 3 you are most interested in are:

  • Time – the query execution time in seconds up to the moment
  • State – the current query state
  • Info – the actual running query

So taking into account the running time of given query you can easily determine whether it’s slow or not (the one with id 3 in the example above is definitely slow) and then identify the exact query by taking it from the Info field.

Tip: if you are running Linux/Unix based OS and have dozens or hundreds of simultaneous queries, then for reviewing easier all you can set ‘less’ as the mysql client pager:


mysql> pager less

so you’ll be able to view the output using the less shell utility (and following same logic can set whatever viewer editor you like, e.g. you can use ‘vim -‘ for vim).