1

Is it possible to monitor mysql like:

  • if some queries run more then 300 seconds - it will be warning
  • if some queries run more then 500 seconds - it will be critical

I tried:

/usr/lib/nagios/plugins/check_mysql_health --hostname localhost --username icinga --password XXX --mode slow-queries --warning 300

But this only shows the rate slow_queries/per second. How can I monitor mysql as I describe?

Thank you for your help.

Br,

3

2 Answers 2

1

Check mysql scripts by percona. There's some very good utilities: http://www.percona.com/software/percona-toolkit http://www.percona.com/software/percona-monitoring-plugins

0

Using Nagios to monitor MySQL service availability is OK but certainly not slow queries.

I use a simple bash script which run every x seconds in crontab and scan processlist to capture queries running for more than 180 seconds. Hope it fits into your case aswell:

    #!/bin/bash


Notify=me@company.com 

count=0

# capture all running queries
echo "<TABLE BORDER=1><TR><TH>Queries running more than 180 seconds</TH></TR></TABLE>" > /tmp/long_running_queries.htm
mysql -uroot -ppassword -s -e "SELECT  now(), ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM  information_schema.PROCESSLIST WHERE HOST not in ('%','localhost') AND COMMAND not in ('Sleep','Binlog Dump') AND TIME > 120;" > /tmp/all_running_queries.txt
echo "<TABLE BORDER=1>" >> /tmp/long_running_queries.htm

# store the output
while IFS= read -r ROW
do
    count=$(($count + 1))
    echo "<TR>" >> /tmp/long_running_queries.htm
    echo "$ROW"  >> /tmp/long_running_queries.htm
    echo "</TR>" >> /tmp/long_running_queries.htm
done < /tmp/all_running_queries.txt 

# if there are more than 2 long running queries then send the output from while loop above into mail
# else pruge the output
if (("$count" > "2"));
then
    echo "</TABLE>" >> /tmp/long_running_queries.htm
    Subject="$count SQL queries running for more than 180 Seconds"
    Body="Some text"
    echo $Body | mutt -a /tmp/long_running_queries.htm -s "$Subject" $Notify
else
    echo "" > /tmp/long_running_queries.htm
fi

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.