Monday, October 06, 2008

Using the event scheduler to purge the process list


hack

Two of the most common tasks for database administrators are cleaning the process list from unresponsive queries and remove idle connections that are filling the connection pool.
Both tasks are related to poor usage of the database. In a perfect world, users would only run queries designed, tested, and benchmarked by the DBA or the project manager, and the application servers would never allocate more connections than planned.
But users are human, and an unpredictable amount of unplanned events can happen everywhere. When I was consulting, the above cases were quite common.
Before MySQL 5.1, the only method to clean up the process list was by hand, or using a cron job to do it from time to time.
MySQL 5.1 introduces the event scheduler, and now you can run scheduled stored routines without need of external applications.
I created two stored procedures that read the processlist and kill a process if the execution time is longer than 200 seconds or if a process is idle for longer than 200 seconds. The script with the stored routines and the associated events is in MySQL Forge.
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, 200);
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();
Notice that you can disable an idle connection by setting the variable interactive_timeout, but this method allows you to be more precise. For example you can state that only idle connections to a given database should be killed, or only the ones from a given user.

UPDATE It looks like there is prior art in the same department. My colleague Matthew Montgomery beat me to it loooong time ago.

2 comments:

Sheeri K. Cabral said...

Why purge idle connections every 10 seconds instead of changing the threshold for the idle timout?

Giuseppe Maxia said...

Sheeri,
This system is independent from the idle timeout. 10 seconds is just an example, to show that you can choose whatever suits your needs. Connections can start at any instant, so at any given time you can have a connection flood. With this setting, you can have a maximum of 10 second flooding.
YMMW