Autokill медленных запросов в MySQL (InnoDB)

Порой возникает необходимость в том что-бы прибить долго выполняющиеся запросы к СУБД, что-бы не делать этого в 3 часа ночи, лучше бы это дело автоматизировать.

Для этого в MySQL есть неплохая штука под названием Events, воспользуемся ею. Создадим табличку в которую будем складывать слишком долго выполняющиеся запросы:

1. CREATE TABLE `bad_query_list` (
2.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
3.   `date` datetime NOT NULL,
4.   `tid` bigint(20) NOT NULL,
5.   `time` int(11) NOT NULL,
6.   `host` varchar(512) NOT NULL,
7.   `user` varchar(255) NOT NULL,
8.   `command` varchar(255) NOT NULL,
9.   `state` varchar(255) NOT NULL,
10.   `query` varchar(4000) NOT NULL,
11.   PRIMARY KEY (`id`),
12.   KEY `date` (`date`),
13.   KEY `tid` (`tid`)
14. ) ENGINE=InnoDB AUTO_INCREMENT=4122 DEFAULT CHARSET=utf8

Создадим процедуру которая будет убивать запросы выполняющиеся от пользователей без права SUPER и работающие большее задаваемого интервала времени:

1. CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc_kill_by_runtime`(IN `runtime` tinyint unsigned)
2. begin
3.    declare done int default 0;
4.    declare connid int unsigned;
5.    declare ips varchar(2000);
6.    declare str1 varchar(255);
7.    declare cur1 cursor for
8.     select replace(substring_index(`user_privileges`.`grantee`, '%', 1), "'", "") as `userhost`
9.     from `information_schema`.`user_privileges`
10.     where not(`user_privileges`.`grantee` in
11.     (select grantee from `information_schema`.`user_privileges`
12.      where `user_privileges`.`privilege_type` = 'SUPER'
13.      group by `user_privileges`.`grantee`))
14.     group by userhost;
15.    declare cur2 cursor for
16.     select id from information_schema.processlist
17.     where concat(user, '@', substring_index(host,':',1)) regexp ips
18.     and command ='Query'
19.     and time >= runtime;
20.    declare continue handler for sqlstate '02000' set done = 1;
21. 
    
22.    open cur1;
23.    repeat
24.       fetch cur1 into str1;
25.       if not done then
26.     if str1 = '' then
27.       set ips =  concat_ws("|", ips, "%");
28.     else
29.       set ips = concat_ws("|", ips, str1);
30.     end if;
31.       end if;
32.    until done end repeat;
33.    close cur1;
34.    
35.    set done = 0;
36. 
    
37.    open cur2;
38.    repeat
39.       fetch cur2 into connid;
40.       if not done then
41.      insert into `dbstats`.`bad_query_list` (`date`, `tid`, `time`, `host`, `user`, `command`, `state`, `query`)
42.      select now(), `id`, `time`,`host`,`user`,`command`,`state`,`info`
43.      from information_schema.processlist
44.      where `id`=connid;
45.      kill connid;
46.       end if;
47.    until done end repeat;
48.    close cur2;
49. end

Создадим событие для планировщика MySQL:

1. CREATE EVENT `run_proc_kill_by_runtime`
2. ON SCHEDULE EVERY 2 SECOND STARTS '2011-10-13 18:50:55'
3. ON COMPLETION PRESERVE ENABLE
4. DO call proc_kill_by_runtime(100)

Теперь все запросы, которые работают более 99 секунд  будут складываться в табличку `dbstats`.`bad_query_list` и прибиваться.

Стоит добавить, что для безопасного отката транзакции лучше бы настроить переменные InnoDB:
innodb_rollback_on_timeout = 1
innodb_lock_wait_timeout = 50

P.S. Спокойных вам ночей коллеги.