Eliminar las consultas lentas de nuestra base de datos MySQL
Miércoles 8 de Octubre del 2008 - MySQL
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();
Términos relacionados: max time, time int, nuestra base, time max, maxia, connecti, cursor, giuseppe, schema, queries
