Perl Scripts

mySQL Slow Query Killer script.

Posted in Perl Scripts on February 22nd, 2011 by admin – 1 Comment

You’re probably wondering, why the hell do I need this? In my case our development team releases a patch that has some SQL changes that inevitably slow our mySQL cluster down to a crawl. I was given the task of finding a temporary solution while the developers worked on a fix. The solution would be, we go through the process list and kill queries that have been hung for X. Instead of reinventing the wheel, I came across a solid Perl script from ‘spe’ which achieves this task, I added some additional functionality and updated some existing functions.

This script will simply go through the process list, identify slow running queries and then kill them. The threshold and alert options are configurable.

#!/usr/bin/perl -w
# spe - 10/2006
# Russ Thompson 2010 @ viGeek.net (Added kill count, alert options, adjustable threshold, console and emailing functions)
# If leaving exposed passwords please wrap in SHcrypt

use diagnostics -verbose;
enable  diagnostics;
use DBI;
use MIME::Lite;

my $user = "theuser";
my $password = "LOLOLOL";
my $mysqladmin = "/usr/bin/mysqladmin";
my $sql = "SHOW FULL PROCESSLIST";
my $killedAQuery = 0;
my $dbhost=`/bin/hostname`;

# Enable E-mail notifications
my $enote = "0";

# Enable console output
my $ecout = "1";

# Set enable variables
my $emailalertfrom = "you\@who.com";
my $emailalertto = "you\@who.com";

# Set integer value for kill counter
my $count = 0;
# Set threshold on what defines a slow query (in seconds)
my $definq = 15;

# Set log file
my $file="slow-query.txt";

# Open our log
open FILE, ">$file" or die "unable to open $file $!";

while (1) {
        $db_handle = 0;
        while ($db_handle == 0) {
                $db_handle = DBI->connect("dbi:mysql:database=mysql;hostname=127.0.0.1:port=3306;user=".$user.";password=".$password);
                if ($db_handle == 0) {
                        sleep(1);
                }
        }

        $statement = $db_handle->prepare($sql)
            or die "Couldn't prepare query '$sql': $DBI::errstr\n";

        $statement->execute()
            or die "Couldn't execute query '$sql': $DBI::errstr\n";
        while (($row_ref = $statement->fetchrow_hashref()) && ($killedAQuery == 0))
        {
                if ($row_ref->{Command} eq "Query") {
                        if ($row_ref->{Time} >= $definq) {
                                @args = ($mysqladmin, "-u".$user, "-p".$password, "kill", $row_ref->{Id});
                                $returnCode = system(@args);
                                # Include console output and kill counts.
                                if($ecout == 1){
                                print ("Killing row ID:  $row_ref->{Id}\n");
                                $count+=1;
                                print("Total killed: $count\n");
                                }
                                $emailMessage = "A slow query as been detected (more than $row_ref->{Time} seconds). SQLkiller will try to kill this request.\nThe query is:\n$row_ref->{Info}\n\n";
                        print FILE "A slow query as been detected (more than $row_ref->{Time} seconds). SQLkiller will try to kill this request.\nThe query is:\n$row_ref->{Info}\n\n";                
       
                if ($returnCode != 0) {
                                        $emailMessage .= "Result: The SQL request cannot be killed. This SQL request is probably a fake slow query due to an another SQL request. The problematic
request is the first killed successfully\n";
                                }
                                else {
                                        $emailMessage .= "Result: The SQL request has been killed successfully\n";
                                }
                                # Establish and send e-mail
                                if($enote == 1){
                                my $msg = new MIME::Lite
                                        From    =>$emailalertfrom,
                                        To      =>$emailalertto,
                                        Subject =>'[ SQLkiller ] A query has been killed on. '.$dbhost,
                                        Type    =>'TEXT',
                                        Data    =>$emailMessage;
                                $msg -> send;
                                }
                               
                                $killedAQuery = 1;
                        }
                }
        }
        $statement->finish();
        $db_handle->disconnect();
        if ($killedAQuery == 0) {
                sleep(5);
        }
        else {
                $killedAQuery = 0;
                #sleep(1);
        }
}