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 email@example.com * 7-23 * * 1-6 /usr/local/bin/longmysqlqueries.sh -t 600 firstname.lastname@example.org
# Evenings, every 20 minutes, 11pm-7am, mon-sat */20 23,0-7 * * 1-6 /usr/local/bin/longmysqlqueries.sh email@example.com */20 23,0-7 * * 1-6 /usr/local/bin/longmysqlqueries.sh -t 600 firstname.lastname@example.org
Long MySql query monitor (6.6 KiB, 509 hits)