Mysql – What reason could cause AWS RDS connections to spike

connectionshigh-availabilitymax-connectionsMySQL

It's my first time using RDS on AWS, I use a t2.medium instance running MySQL Aurora with default configs. The CPU usage and DB connections is quite normal until "something" happens, which causes the DB connections go all the way up to its maximum (with t2.medium it is 80 connections).

I only have one web app, running on an EC2 instance. When the DB connections reaches its maximum, the EC2 instance's CPU usage is absolutely normal (25-30%), but all attempts to connect to the DB instance results in "Too many connections".

I also checked the DB instance's CPU Utilization at the time — it was showing no signal of high load. During the strike time, CPU Utilization dropped to 20% and kept that rate consistently.

The thing I don't understand: DB connections is at its maximum, but why is DB CPU Utilization dropped? Shouldn't it be at its maximum as well because of the computation for the queries in those connections?

Please help me understand, thank you very much.

(I had to resize the RDS instance to r4.large when the second strike happens; I'm still running it now until I find out the problem…)

Best Answer

If you run show processlist; it'll show you all the connections that are running against your DB. Running show status like 'Conn%' can show how many connections are active

You may think that a high number of connections would increase the CPU but ultimately it depends what those connections are trying to accomplish, if they aren't doing much but are preventing you're main application from running their connections then the normal processing level will go down while you are not running your normal processes

The annoying thing is to be able to run the show processlist command you need to actually be connected to the DB which you wont be able to as you're at max connections.

A slight work around is to increase your max connections (if you're able to) and set up some monitoring somewhere that runs every so often (every 5 minutes would have caught that) and when you're over 50 connections, run a command that dumps out all the active connections somewhere so you can review later.

Related Solutions

Mysql – Too Many database connections on Amazon RDS

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.

Mysql – multiple connections on MySQL database with no users on httpd

You will need to look into how your connection pool is configured in your PHP web app. Even a fully functional connection pool will often keep many connections to a database open even when they are not in use. Opening connections to the database can be very expensive for a web app and it makes sense to have some unused ones sitting around.

Most connection pools have settings for things like:

Minimum number of connections to keep open
Maximum number of connections to keep open
Number of connections to open when new connections are needed

There is also the possibility that your web app is not releasing connections back to the connection pool properly. When you are done with your connections, you need to close them to let the pool know that they are available. Not doing so is a a kind of resource leak and you will eventually run out of connections.

Best Answer

Related Solutions

Mysql – Too Many database connections on Amazon RDS

Mysql – multiple connections on MySQL database with no users on httpd

Related Question