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