mysqldatabasestored-procedures

MySQL - Kill all sleeping connections

How do I remove all idle connections in MySQL without access to the command line? I need a temporary solution as one of our services isn't closing its connections properly and flooding the database with idle connections.

Solution

  • You can kill multiple connections using a stored procedure such as

    CREATE PROCEDURE kill_all_sleep_connections()
BEGIN
  WHILE (SELECT count(*) as _count from information_schema.processlist where Command = 'Sleep') > 10 DO
    set @c := (SELECT concat('KILL ', id, ';') as c from information_schema.processlist where Command = 'Sleep' limit 1);
    prepare stmt from @c;
    execute stmt;
  END WHILE;
END;

    This will create a string like KILL 4312; for each row matching the WHERE Command = 'Sleep' condition, referencing the id. Then it EXECUTE's the string as a query, ending the connection.

    Use it like this

    call kill_all_sleep_connections()

    You should now have less than 10 idle connections on the database instance. The WHILE is set to < 10 as a service might create connections very quickly so increase/decrease as you see fit.

    You can view all the connections with

    You can kill a single connection by referencing the id in those results using KILL

    A proper solution is to close each connection in each service once it is done querying the database.