Via Giuseppe Maxia, nos llega un procedimiento almacenado que elimina de la cola de procesos automáticamente cualquier consulta que supere N segundos en ejecutarse o que se tire más de N segundos «idle». Requiere MySQL 5.1.
[mysql]
USE test;
DROP procedure IF EXISTS purge_slow_queries;
DROP procedure IF EXISTS purge_idle_connections;
DROP event IF EXISTS auto_purge_slow_queries;
DROP event IF EXISTS auto_purge_idle_connections;
delimiter //
CREATE procedure purge_idle_connections()
deterministic
begin
declare done BOOLEAN DEFAULT false;
declare max_time int DEFAULT coalesce(@max_kill_time, 200);
declare pid bigint;
declare c cursor FOR
SELECT id
FROM information_schema.processlist
WHERE command IN (‘Sleep’)
AND time > max_time;
declare continue handler FOR NOT found
SET done = true;
open c;
SET @q_kill = ‘KILL ?’;
prepare q_kill FROM @q_kill;
PURGELOOP: loop
fetch c INTO pid;
IF done then
leave PURGELOOP;
end IF;
SET @pid = pid;
execute q_kill USING @pid;
end loop;
deallocate prepare q_kill;
end//
CREATE procedure purge_slow_queries()
deterministic
begin
declare done BOOLEAN DEFAULT false;
declare max_time int DEFAULT coalesce(@max_kill_time, 200);
declare pid bigint;
declare c cursor FOR
SELECT id
FROM information_schema.processlist
WHERE state IN (‘executing’)
AND time > max_time;
declare continue handler FOR NOT found
SET done = true;
open c;
SET @q_kill = ‘KILL ?’;
prepare q_kill FROM @q_kill;
PURGELOOP: loop
fetch c INTO pid;
IF done then
leave PURGELOOP;
end IF;
SET @pid = pid;
execute q_kill USING @pid;
end loop;
deallocate prepare q_kill;
end//
delimiter ;
CREATE event auto_purge_idle_connections
ON schedule every 10 second
do call purge_idle_connections();
CREATE event auto_purge_slow_queries
ON schedule every 10 second
do call purge_slow_queries();
[/mysql]