SQL Server RDS – How Many Read IOPS Are Too Many?

Just ran across this question, and can give you a couple of suggestions.

If you're trying to gauge when to move to a bigger instances (vs. increasing the size of the databases) when you're getting close to hitting some sort of resource limit: Memory, I/O and CPU all have the ability to limit your performance.

The symptom you mention in the question may be a symptom that you're using more and more memory - memory used for cache & buffers are being reclaimed for other uses. Upgrading to a large instance type will increase the amount of available memory.

The chart that Rolando posted should give you a good guideline as to the number of available connections - if you're hitting those limits, then it's time to upgrade. Available connections are directly related to available memory, so hitting connection limits means you should upgrade.

If your CPU is averaging > 50% or so, you might want to start planning on an upgrade.

Finally, if you see consistently high I/O you may want to consider a larger instance (generally, the more powerful the instance the better the I/O) or using provisioned IOPS.

Here is a Stored Procedure to kill long running SELECTs

DELIMITER $$

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

    DECLARE ndx,lastndx INT;

    DROP TABLE IF EXISTS test.LongRunningSelects;
    CREATE TABLE test.LongRunningSelects
    (
        id INT NOT NULL AUTO_INCREMENT,
        idtokill BIGINT,
        PRIMARY KEY (id)
    ) ENGINE=MEMORY;
    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);
    END WHILE;

    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.