2011-10-18

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



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

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

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


Создадим процедуру которая будет убивать запросы выполняющиеся от пользователей без права SUPER и работающие большее задаваемого интервала времени:
  1. CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc_kill_by_runtime`(IN `runtime` tinyint unsigned)
  2.    declare done int default 0;
  3.    declare connid int unsigned;
  4.    declare ips varchar(2000);
  5.    declare str1 varchar(255);
  6.    declare cur1 cursor for
  7.     select replace(substring_index(`user_privileges`.`grantee`, '%', 1), "'", "") as `userhost`
  8.     from `information_schema`.`user_privileges`
  9.     where not(`user_privileges`.`grantee` in
  10.     (select grantee from `information_schema`.`user_privileges`
  11.      where `user_privileges`.`privilege_type` = 'SUPER'
  12.      group by `user_privileges`.`grantee`))
  13.     group by userhost;
  14.    declare cur2 cursor for
  15.     select id from information_schema.processlist
  16.     where concat(user, '@', substring_index(host,':',1)) regexp ips
  17.     and command ='Query'
  18.     and time >= runtime;
  19.    declare continue handler for sqlstate '02000' set done = 1;

  20.    open cur1;
  21.    repeat
  22.       fetch cur1 into str1;
  23.       if not done then
  24.     if str1 = '' then
  25.       set ips =  concat_ws("|", ips, "%");
  26.     else
  27.       set ips = concat_ws("|", ips, str1);
  28.     end if;
  29.       end if;
  30.    until done end repeat;
  31.    close cur1;
  32.    
  33.    set done = 0;

  34.    open cur2;
  35.    repeat
  36.       fetch cur2 into connid;
  37.       if not done then
  38.      insert into `dbstats`.`bad_query_list` (`date`, `tid`, `time`, `host`, `user`, `command`, `state`, `query`)
  39.      select now(), `id`, `time`,`host`,`user`,`command`,`state`,`info`
  40.      from information_schema.processlist
  41.      where `id`=connid;
  42.      kill connid;
  43.       end if;
  44.    until done end repeat;
  45.    close cur2;

Создадим событие для планировщика 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. Спокойных вам ночей коллеги.

Комментариев нет:

"In order to ask a question you must already know most of the answer"
/Robert Sheckley/