Автоматическое завершение долгих процессов Mysql

Бывают случаи, когда некоторые запросы Mysql подвисают и выполняются невероятно долго, создавая приличную нагрузку на сервер.

Этот способ помогает автоматически отслеживать и завершать все запросы которые длятся больше заданного количества секунд.

Процедура mysql_kill_query убивает все запросы, за исключением запущенных с правами супер пользователя, время выполнения которых превышает runtime секунд.

Выполняем запрос к базе данных:

DELIMITER //
CREATE PROCEDURE `mysql_kill_query` ( IN runtime TINYINT UNSIGNED )
LANGUAGE SQL
NOT DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE connid INT UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT ID FROM information_schema.PROCESSLIST
WHERE CONCAT ("'", USER, "'@'", SUBSTRING_INDEX(HOST,':',1), "'") IN
( SELECT `USER_PRIVILEGES`.`GRANTEE` AS `GRANTEE` 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`)))
AND COMMAND ='Query'
AND TIME>= runtime;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
OPEN cur1;
REPEAT
FETCH cur1 INTO connid;
IF NOT done THEN
KILL connid;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END//
DELIMITER ;

И затем добавляем ещё один запрос, чтобы создать в планировщике mysql задание которое будет выполнять нашу функции каждые N секунды (в примере это 2 секунды) с максимальным временем запроса в две секунды:

SET GLOBAL event_scheduler=ON;
 CREATE EVENT IF NOT EXISTS `mysql_kill_query`
ON SCHEDULE EVERY 2 SECOND
ON COMPLETION PRESERVE ENABLE
DO CALL mysql_kill_query(2);

Другие статьи по теме:

• Перенос большой базы MySQL, если не достаточно места для дампа
• Быстрый перенос сайта на другой сервер через SSH