Sunday, 15 February 2015

Little Shell Trick to Inspect What Applications Are Doing to Your Database

Whenever you have to inspect what queries a given program is running against your database without support from your Developers, you can try querying the PROCESSLIST table in the INFORMATION_SCHEMA really fast:

  #!/bin/bash  
 monitor_queries_by () {  
   local some_user=$1  
   while sleep 0.1  
   do  
     echo "SELECT DB, INFO FROM PROCESSLIST \
           WHERE USER = '${some_user}'      \
             AND INFO IS NOT NULL;"
   done | mysql -BNsD information_schema  
 }  
  

Define this function on your shell and call it with the username want to monitor. You should be able to see every query your application is running. If you're missing queries, you're probably not querying fast enough. Reduce the value in the sleep call even further.

Please note this is the second worst thing you can do to your production database, just after enabling the General Query Log. Use with extreme caution.