Automatic kill/timeout slow queries in MySQL

Question

Is there some configuration that can be done at MySQL side to automatically kill or timeout queries that are extremely slow, say 100 seconds.

Answer 1

You can list all your MySQL queries by the following command:

$ mysqladmin processlist

so you can run some script which will parse that list and it'll kill the specific query.

In example, you can run some script in any language via cron to periodically check for the long queries, e.g.:

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
  $process_id=$row["Id"];
  if ($row["Time"] > 200 ) {
    $sql="KILL $process_id";
    mysql_query($sql);
  }
}

Another example:

mysql> select concat('KILL ',id,';') from information_schema.processlist
where user='root' and time > 200 into outfile '/tmp/a.txt';

mysql> source /tmp/a.txt;

Related:

• How do I kill all the processes in Mysql "show processlist"?

Read more:

• http://www.mysqlperformanceblog.com/