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

I have enabled mysql slow query log on Ubuntu server. I prefer to get the email alert with the slow sql when any slow query appeared so I can optimize the sql. I need a lightweight solution.

share|improve this question
    
What version of MySQL ??? –  RolandoMySQLDBA Jun 29 '11 at 2:59
1  
maatkit.org/doc/mk-query-digest.html –  twehad Jun 29 '11 at 3:50
    
yep, if you use mysql you really need to get familiar with maatkit –  goo Jun 29 '11 at 17:44

2 Answers 2

I often use the mytop, top like query monitor for MySQL it is a Perl script with some hack you can add a functionality of send alert notification.

share|improve this answer

If you can afford to sit around waiting for a slow query to occur then take your time working on it - lucky you!

This is not how I go about monitoring / tuning my systems. I get the system to log all queries, then use a modified version of this script to strip out the literals from the WHERE clause, then prioritize based on the sum of the times for the anonymized queries (the code I use also calculates average, stddev and max)

But a fairly simple solution to achieve what you are asking for (run frmo cron) would be:

#!/bin/bash

CTL_FILE='/var/lock/slowchk'
LOG_FILE='/var/log/mysql_slow_query.log'

if [ ! -f ${CTL_FILE} ]; then
      touch ${CTL_FILE}
fi

if [ ${LOG_FILE} -nt ${CTL_FILE} ]; then
   touch ${CTL_FILE}
   tail -200 ${LOG_FILE} | mail -s 'Slow Queries' user@example.com
else
   touch ${CTL_FILE}
fi

Note that queries only appear in the log after they've completed - if you've got some real nasties in there, you might want to parse the output of 'mysqladmin processlist' to find stuff which is currently running for more than a threshold with a view to killing it.

share|improve this answer

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.