Home / server / Questions / 11357
Accepted
deadprogrammer
Asked: 2009-05-23 08:56:55 +0800 CST

Bulk or mass killing misbehaving MySQL queries

  • 772

How do you kill a mass of MySQL queries? Here is a good approach:

mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/tmp/a.txt';
mysql> source /tmp/a.txt;

Any others besides clicking them to death in MySQL Administrator GUI?

mysql bulk-action

4 Answers

  1. Best Answer

    To crib the best comments from Percona's take on this:

    Comment 4: Robert Wultsch

    I prefer the following as it will kill them in a multi threaded manner… (sometimes killing a single query can take a while)

    for i in $(mysql -uroot -pPASS -e ’show processlist’ | grep ’search_term’ | awk ‘{print $1}’); do

    mysql -uroot -pPASS -e “kill $i” &

    done

    Comment 8: Shlomi Noach

    An INFORMATION_SCHEMA.PROCESSLIST stored procedure which is a bit verbose to copy.

    Comment 16: Bryan

    If information_schema.processlist doesn’t exist on your version of MySQL, this works in a linux script:

    #!/bin/bash

for each in `mysqladmin -u root -prootpwd processlist | awk 
‘{print $2, $4, $8}’ | grep $dbname | grep $dbuser | awk ‘{print $1}’`;

do mysqladmin -u root -prootpwd kill $each;

done

    Comment 21: Andrew Watson

    I do this:

    mysqladmin proc | grep Sleep | sort -r -n -k6 | awk {’print $1; ‘} | xargs mysqladmin kill

    or something to that effect…

    As Dan C mentions in his answer to this question, pruning SELECTS is significantly safer than killing write commands mid-flow, as you may lose data integrity and/or foreign keys.

    • 6

  2. An alternative solution is to use an approach that Digg describe, which is to automatically prune any SELECT queries which take longer than an allotted period of time to complete. Generally speaking you only want to prune SELECT queries because they are read-only and shouldn't affect the data integrity of your application.

    Two such utilities that you can use to automate this are dbmon.pl and mkill which is part of a package called mtop.

    • 2

  3. I used it to kill all sleeping queries on MySQL 5.5 database server:

    mysql -e "show full processlist;" -ss | grep Sleep | awk '{print "KILL "$1";"}' | mysql
    • 0

  4. The easiest is to kill them from the shell, e.g.

    mysqladmin proc | grep Sleep | awk '{print $2}' | xargs -L1 mysqladmin kill

    If there is still a problem, check the details by the following query:

    mysql -e "SHOW engine innodb status\G"

    From MySQL, try the following code based on the post by @Shlomi, you can create a stored procedure using server cursor, for example:

    DELIMITER $$

DROP PROCEDURE IF EXISTS test.kill_user_queries$$
CREATE PROCEDURE test.kill_user_queries (kill_user_name VARCHAR(16) CHARSET utf8)
SQL SECURITY INVOKER
BEGIN
DECLARE query_id INT;
DECLARE iteration_complete INT DEFAULT 0;
DECLARE select_cursor CURSOR FOR SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user=kill_user_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET iteration_complete=1;

OPEN select_cursor;
cursor_loop: LOOP
FETCH select_cursor INTO query_id;
IF iteration_complete THEN
LEAVE cursor_loop;
END IF;
KILL QUERY query_id;
END LOOP;
CLOSE select_cursor;

END$$

DELIMITER ;

    There is also an old script called mypgrep.py by Google.

    • 0