Wednesday, July 6, 2011

How to kill long lasting connections in mysql automatically

Although it is possible to use console and kill long lasting connections by hand, or we could set up MYSQL to do the same in config, wouldn't it be nice to have a procedure that will inspect the database for long lasting connections automatically. Well here it is:


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();

Please note that you must have information_schema.processlist table wich was added in MySQL 5.1.7.

Post a Comment