In this article we’ll discuss how you can configure a simple bash script to check on your MySQL query activity to ensure that long queries are not hanging on your server. This script will also be able to e-mail you a notice when it has encountered a long running query.
In most cases a MySQL database query should be able to execute within a few seconds, if for some reason it takes longer than that, there could be a possibility of a long running MySQL query backing up that database queue and leading to your server slowing down, and possibly becoming unstable.
For more information on types of MySQL usage problems you can read about excessive MySQL activity.
This script can only be setup on a VPS or dedicated server hosting plan that you have root access to.
Create MySQL query monitoring script
- Depending on the complexity of your database, how many websites you run, and how much traffic you get, the max amount of time that you’d like to allow a query to run could be different. I would recommend starting off with something conservative around 120 seconds or 2 minutes, and if you’re logging in to your server after being alerted and it seems to still be running stable, you could probably increase the trigger level to something higher.Start editing a new file for the bash MySQL query alert script, in this case I’ll be using the vim text-editor and making a new file called MySQLMon in my user’s home directory with the following command:
vim /home/userna1/MySQLMonThen you’ll want to hit i to enter Insert mode once vim is loaded up and enter in the following code:
#!/bin/bash trigger=120 activeQcount=`mysql -e "show full processlist;" |
egrep -v "leechprotect|root|Time" | awk -v T=$trigger '{ if ( $6 > T ) print $0}' |
wc -l` if [ $activeQcount -gt 0 ] then echo
"=====================================================================================" >
/tmp/MySQLMon echo "= MySQLMon has found a new query running for longer than 120 seconds (2 mins)"
>> /tmp/MySQLMon echo
"=====================================================================================" >>
/tmp/MySQLMon date >> /tmp/MySQLMon echo "" >> /tmp/MySQLMon mysql -e "show full processlist;"
>> /tmp/MySQLMon echo
"=====================================================================================">>
/tmp/MySQLMon cat /tmp/MySQLMon | awk -v T=$trigger '{ if ( $6 > T ) print $0}' |
mail -s"Caught query running longer than $trigger seconds" [email protected] -- -r
"[email protected]" fi
This code can look a bit overwhelming at first, but broken down it’s really quite simple. First we are declaring a trigger variable and setting that to 120 seconds. Then we are setting an activeQcount variable to basically hold any queries that are longer than our trigger value.We then use a bash if statement to check if our activeQcount value is above zero, showing that there are queries running longer than our set trigger value. Next we simply start echo’ing text into a placeholder file called /tmp/MySQLMon, then finally we cat that placeholder file which just reads it out and then we pipe it | to the mail function followed by a subject we’d like to use, the recipient’s address, then you can enter in — -r followed by the email address you’d like the message to come from.
- When you receive an e-mail alert it will look much like this example:
======================================================================================
MySQLMon has found a new query running for longer than 120 seconds (2 mins)
=====================================================================================
Tue Dec 4 15:07:42 EST 2012 40901 userna1_phpb1 localhost userna1_phpb1 Query 342
Sending data
INSERT INTO users (userID, name, base64_decode)
=====================================================================================
Setup cron job to run MySQLMon script
- Now you should have your MySQL query monitoring bash script setup, next you’ll want to create a cron job to run this task. If you’re unframiliar with cron jobs you can read about how to run a cron job.You can use the cPanel drop-down for every 5 minutes, which should make the final cron job looking like:
*/5 * * * * bash /home/userna1/MySQLMon
You should now have successfully setup a bash script to monitor your MySQL queries to catch any long running ones, and to alert you via e-mail when it catches any. You should also know how to setup a cron job to run that script on a set interval so that it will constantly run without further manual intervention from yourself.
