slow_query_killer.php

<?php

$config = array(
	'db_host' => 'localhost',
	'db_user' => 'root',
	'db_pass' => '',

	'system_name' => `hostname`,
	'send_mail' => TRUE,					// TRUE to send e-mails, FALSE to operate silently
	'mail_to' => 'root@localhost',	// comma-separated list of e-mail addresses
	'mail_from' => 'root@localhost',

	'query_length_report' => 90, // report when a query is running longer than this amount of secs
	'query_length_kill' => 120,	// kill query when it is running longer than this amount of secs
);


$mycnf = strtok(file_get_contents("/root/.my.cnf"), "\n");
$matches = array();
while ($mycnf && !preg_match('/password=(.*)/',$mycnf, $matches)) {
	$mycnf = strtok("\n");
}
if ($mycnf) {
	$config['db_pass'] = $matches[1];
} else {
	die("I need a password in ~/.my.cnf to work!");
}


$pass_query_commands = array(
	'sleep',
	'binlog dump',
);

$bad_queries = array();
$killed_queries = array();

$db = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass']);

$res = mysqli_query($db, "SHOW FULL PROCESSLIST");

// scan through list of processes, scanning for locked queries
while ($row = mysqli_fetch_array($res)) {
	// this operation is being locked by another query
	if (in_array(strtolower($row['Command']), $pass_query_commands)) {
		continue;
	}
	
	if($row['State'] == 'Locked')
		continue;

    $query_length = $row['Time'];

	if($query_length > $config['query_length_report'] && stripos(ltrim($row['Info']), 'SELECT') === 0 && !strstr($row['Info'], '40001 SQL_NO_CACHE'))
	{
		$killed = false;
		if($query_length > $config['query_length_kill'])
		{
			$cmd = sprintf("KILL QUERY %d;", $row['Id']);
			mysqli_query($db, $cmd);
			$killed = true;
		}
		$bad_queries[] = array(	'id' => $row['Id'],
								'db' => $row['db'],
								'query' => $row['Info'], 
								'state' => $row['State'], 
								'length' => $query_length,
								'killed' => $killed);
	}
}

mysqli_free_result($res);

mysqli_close($db);

if(sizeof($bad_queries) > 0)
{
	$mail_string = sprintf("\n%s has the following slow queries (%d):\n", $config['system_name'], $config['query_length_report']);

    foreach($bad_queries as $query)
	{
		$mail_string .= sprintf("\nid: %d\ndb: %s\nlength: %d\nkilled: %s\nstate: %s\nquery: %s\n", $query['id'], $query['db'], $query['length'], $query['killed'] ? "YES" : "no", $query['state'], $query['query']);
	}

	$mail_headers = 'From: root@localhost' . "\r\n" .
		'Reply-To: root@localhost' . "\r\n" .
		'X-Mailer: PHP/' . phpversion();

	$mail_subject = sprintf("%s slow queries", $config['system_name']);

	mail($config['mail_to'], $mail_subject, $mail_string, $mail_headers);

	
}