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

  1. 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/MySQLMon

    Then 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" recipient@Domain.com -- -r "MySQLMon@Domain.com"
    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.

  2. 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

  1. 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.

Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve this article:
Email Address
Name

new! - Enter your name and email address above and we will post your feedback in the comments on this page!

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Post a Comment

Name:
Email Address:
Phone Number:
Comment:
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

0 Questions & Comments

Post a comment

Back to first comment | top

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!