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.

You'll need to be careful with that, as if you set something like that to operate globally there's always the risk of killing a reindex on a large table which in turn could cause corruption. What you can do is add in a timeout into connection strings of applications interfacing with the MySQL server — Simon at The Access Group, Feb 22 '12 at 14:43

score 3 · Answer 1 · edited May 23 '17 at 12:03

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;

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

Read more:

http://www.mysqlperformanceblog.com/

score 0 · Answer 2 · answered Dec 13 '12 at 09:18

0

You should checkout the command pt-kill of percona toolkit

answered Dec 13 '12 at 09:18

Gew

85
3

score 0 · Answer 3 · answered Feb 22 '12 at 14:58

Starting with MySQL 5.1 you can create a stored procedure to query the information_schmea.PROCESSLIST table for all queries that match your criteria ("long running time" in your case). Upon querying processlist table, you can simply iterate over a cursor to kill the running query processes that exceeds your timeout criteria.

Take a look at the following example: Procedure to find and terminate all non-SUPER and "system account" queries running longer than N seconds

http://forge.mysql.com/tools/tool.php?id=106

Automatic kill/timeout slow queries in MySQL

3 Answers3