Monthly Archives: June 2014

[Solved] MYSQL.. handle Interactive timeout and slow queries

Dear All,

To handle Interactive timeout and slow queries below stored procedures can be used that read the processlist and kill a process if the execution time is longer than 600 seconds or if a process is idle for longer than 200 seconds.

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’)
— add more conditions here
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, 600);
declare pid bigint;
declare c cursor for
SELECT id
FROM information_schema.processlist
WHERE state in (‘executing’)
— add more conditions here
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();

 

 

Thanks

 

Prashant Deshpande


 

Leave a comment

Filed under Uncategorized