How to monitor mysql slow log and send mail to alert?

Question

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.

Answer 1

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.

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