MySQL: Autokill медленных запросов
В силу различных обстоятельсв иногда возникает необходимость в том, что-бы прервать выполнение слишком долго работающего запросы к СУБД.
Так вот что-бы не делать этого в 3 часа ночи, лучше бы это дело автоматизировать. В MySQL для этого есть неплохая штука под названием Events. Воспользуемся ею, создадим табличку в которую будем складывать долго выполняющиеся запросы:
CREATE TABLE `bad_query_list` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`tid` bigint(20) NOT NULL,
`time` int(11) NOT NULL,
`host` varchar(512) NOT NULL,
`user` varchar(255) NOT NULL,
`command` varchar(255) NOT NULL,
`state` varchar(255) NOT NULL,
`query` varchar(4000) NOT NULL,
PRIMARY KEY (`id`),
KEY `date` (`date`),
KEY `tid` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4122 DEFAULT CHARSET=utf8Затем создадим процедуру, которая будет убивать запросы выполняющиеся от пользователей без права SUPER больше определённого интервала времени (код quick and dirty):
CREATE DEFINER = `root`
@`127.0.0.1`
PROCEDURE `proc_kill_by_runtime`(IN `runtime` tinyint unsigned)
begin
declare done int default 0;
declare connid int unsigned;
declare ips varchar(2000);
declare str1 varchar(255);
declare cur1 cursor for
select replace(substring_index(`user_privileges`.`grantee`, '%', 1), "'", "") as `userhost`
from `information_schema`.`user_privileges`
where not(`user_privileges`.`grantee` in
(select grantee from `information_schema`.`user_privileges`
where `user_privileges`.`privilege_type` = 'SUPER'
group by `user_privileges`.`grantee`))
group by userhost;
declare cur2 cursor for
select id from information_schema.processlist
where concat(user, '@', substring_index(host,':',1)) regexp ips
and command = 'Query'
and time >= runtime;
declare continue handler for sqlstate '02000' set done = 1;
open cur1;
repeat
fetch cur1 into str1;
if not done then
if str1 = '' then
set ips = concat_ws("|", ips, "%");
else
set ips = concat_ws("|", ips, str1);
end if;
end if;
until done end repeat;
close cur1;
set done = 0;
open cur2;
repeat
fetch cur2 into connid;
if not done then
insert into `dbstats`.`bad_query_list` (`date`, `tid`, `time`, `host`, `user`, `command`, `state`, `query`)
select now(), `id`, `time`, `host`, `user`, `command`, `state`, `info`
from information_schema.processlist
where `id` = connid;
kill connid;
end if;
until done end repeat;
close cur2;
endТеперь создадим событие для планировщика MySQL:
CREATE EVENT `run_proc_kill_by_runtime`
ON SCHEDULE EVERY 2 SECOND STARTS '2011-10-13 18:50:55'
ON COMPLETION PRESERVE ENABLE
DO call proc_kill_by_runtime(100)После этих действий все запросы, которые работают более 99 секунд будут складываться в табличку dbstats.bad_query_list и прибиваться.
Стоит добавить, что для безопасного отката транзакции лучше бы настроить переменные InnoDB:
innodb_rollback_on_timeout = 1
innodb_lock_wait_timeout = 50
Have a fun and good nights!
blog comments powered by Disqus