mySQL Slow Query Killer script.
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);
}
}
# 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);
}
}
This is awsome script
Thanks for sharing Russ !