Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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,

share|improve this question
    
    
eng.wealthfront.com/2010/11/… –  quanta Nov 6 '12 at 16:19
    
I never worked with this plugin. But make sure you enabled MySQL slow query log. –  mwasif Dec 20 '12 at 6:51
add comment

3 Answers

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

share|improve this answer
add comment

http://stackoverflow.com/questions/2056840/mysql-query-analyzer-free-solutions

You can try MONyog's sniffer feature which can send alerts in similar cases mentioned by you.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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