We are having problems with users running queries/views in Drupal that occasionally cause our site to freeze. The freeze occurs because the query causes the number of database connections to go up to 400+ and basically anytime the site goes over 100 database connections the site slows down terribly and just doesn't respond.

We are running Amazon RDS using MySQL Red Hat Linux

We have a large enough EC2 on the front end app server, and a large enough RDS.

The way we are fixing this issue now is to find the offending query, and kill it. Once the query is killed...our database connections drop to around 20 which is the normal amount you see when monitoring the site statistics.

Is there a way to stop the offending query and kill it before it runs too long and consumes the connections? I am trying to automate the killing of the bad query before it happens, or at least realize after 30 seconds its a bad query and kill it.

  • 3
    Killing the query via an automated process seems like entirely the wrong approach... whether your RDS instance is in fact underpowered, causing the initial pileup... or there's something wrong with the logic in your application, it seems like finding the actual problem with the query would be the thing to do... Jun 5, 2013 at 19:54
  • You can use MONyog- MySQL Monitor which has PROCESSLIST based sniffer helps in Notifying and KILLING of long running queries. It works well with Amazon RDS too. Jun 6, 2013 at 4:41
  • Not being a MySql / Linux guy - how can you have 100+ connection from one website? I do asp.net only, and any of my pages only opens ONE connection at a time - so that would mean processing 100+ pages at the same time (actually more as a page only has a connection open while needing it). I would look into your approach of handling the connections - that is seriously inefficient.
    – TomTom
    Jul 6, 2013 at 5:45
  • AWS sets the max connections based on the size of your instance. the formula they use is: max_connections = {DBInstanceClassMemory/12582880} See the Parameter Groups documentation: https://console.aws.amazon.com/rds/home?region=us-east-1#parameter-groups:
    – user43522
    Jul 10, 2014 at 14:58
  • May be you should consider implementing some sort of connection pooling.
    – mustaccio
    Jul 10, 2014 at 15:16

1 Answer 1


Here is a Stored Procedure to kill long running SELECTs


DROP PROCEDURE IF EXISTS `test`.`Kill_Long_Running_Selects` $$
CREATE PROCEDURE `test`.`Kill_Long_Running_Selects` (time_limit INT,display INT)

    DECLARE ndx,lastndx INT;

    DROP TABLE IF EXISTS test.LongRunningSelects;
    CREATE TABLE test.LongRunningSelects
        idtokill BIGINT,
        PRIMARY KEY (id)
    INSERT INTO test.LongRunningSelects (idtokill)
    SELECT id FROM information_schema.processlist
    WHERE user<>'system user' AND info regexp '^SELECT' AND time > time_limit;

    SELECT COUNT(1) INTO lastndx FROM test.LongRunningSelects;
    SET ndx = 0;
    WHILE ndx < lastndx DO
        SET ndx = ndx + 1;
        SELECT idtokill INTO @kill_id
        FROM test.LongRunningSelects WHERE id = ndx;
        CALL mysql.rds_kill(@kill_id);

    IF lastndx > 0 THEN
        IF display = 1 THEN
            SELECT GROUP_CONCAT(idtokill) INTO @idlist FROM test.LongRunningSelects;
            SELECT @idlist IDs_KIlled;
            SELECT CONCAT('Processes Killed : ',lastndx) Kill_Long_Running_Selects;
        END IF;
    END IF;

END $$

To kill SELECTs running longer than 30 seconds, you run this

CALL test.Kill_Long_Running_Selects(30,0);

If you want to see the connections being killed, you run this

CALL test.Kill_Long_Running_Selects(30,1);

Perhaps you can create a MySQL Event to call this Stored Procedure every minute.

If Amazon does not let you have the EVENT privilege, you will have to write an external shell script on the EC2 server to connect to the DB and run the Stored Procedure. That shell script can be put into a crontab.

If Amazon does not let you have the PROCESS and SUPER privileges, you may need to move the DB out of RDS and into another EC2 instance running MySQL to accomplish this. You could then create the MySQL Event without Amazon's hosting restrictions.

  • 1
    This is a great answer! I used it on RDS today by just changing the line KILL @kill_id; to "call mysql.rds_kill(@kill_id);" and it works perfectly.
    – Dave R
    Jul 28, 2017 at 12:34
  • @DaveR thank you. I will update that line later today. Jul 28, 2017 at 12:44
  • @DaveR I just made that line change. Thanks for pointing this out. Jul 28, 2017 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.