Monitor long MySql queries

By jbayer - Last updated: Tuesday, December 13, 2011 - Save & Share - Leave a Comment

At work we have a very large mysql database, over 250 gig in size, and it is extremely busy.  We need to know when a query is taking too long;  the reason could be a code bug, or just a very long operation.  Regardless, these long queries have the possibility to hang the server.

The attached script monitors the database, and if a query is found that is taking too long, sends out an email.  The only change you need to make is to enter the user and password to access the database, about line 87-88.

Additionally, the script, while intended to run once a minute from cron, can do multiple checks of the database during a single run.  This gives the ability to monitor the database faster than once a minute.

 longmysqlqueries.sh [ -t timelimit ] [-e emailAddress] [ -h host ] [ -p port ] [ -d ] [ -i interval ] [ -s
 sleep ]
      timelimit           Minimum time to consider a query as "long-running" for purposes of sending an email
      emailAddress        Email addresses to send messages to, can be repeated for multiple
                          addresses.
      host                Mysql host
      port                Mysql port on the host
      interval            How long to sleep after sending a message; this
                          is in addition to the sleep value
      sleep               How long to sleep between checks

Here are some example crontab entries:

# Daily, every minute, 7am-11pm, mon-sat
* 7-23 * * 1-6  /usr/local/bin/longmysqlqueries.sh root@localhost.localdomain
* 7-23 * * 1-6  /usr/local/bin/longmysqlqueries.sh -t 600 devops@company.com
# Evenings, every 20 minutes, 11pm-7am, mon-sat
*/20 23,0-7 * * 1-6     /usr/local/bin/longmysqlqueries.sh root@localhost.localdomain
*/20 23,0-7 * * 1-6     /usr/local/bin/longmysqlqueries.sh -t 600 devops@company.com

  Long MySql query monitor (6.6 KiB, 540 hits)

Posted in Database • • Top Of Page
719 views

Write a comment

asd