Using the event scheduler to purge the process list

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.