Too many Connections on Node-MySQL

Question

I get Error Too many connections when there to much items in a loop with INSERT IGNORE INTO.

function insertCases(cases) {
    for(var i in cases) {
        var thequery = 'INSERT IGNORE INTO `cases` SET keysused='+cases.keysused+' 
        pool.query(thequery, function(ee, rr) {
            if(ee) {
                logger.info(ee);
                throw ee;
            }
        });
    }
}

When there are now 100+ cases so 100 times INSERT IGNORE INTO than I get too many connections. I don't know how much exactly crash it but with 100 it's working.

What I read is, that query runs the query and also closes the connection after done, so I read, I don't need to close it after than.

If I run 100, waiting short time and run 100 again and so on, it don't get Too many connections error.

It's only when there runs so much time at once.

That's my DB settings

var db_config = {
    connectionLimit : 5000,
    host: 'localhost',
    user: 'userexample',
    password: '*******',
    database: 'example.com'
};

and that's the function that createPool.

function database_connection() {
pool = mysql.createPool(db_config);
pool.getConnection(function(err, connection) {
    if(err) {
        logger.error('[ERROR] Connecting to database "' + err.toString() + '"');
        setTimeout(function() { database_connection(); }, 2500);
    }
    else
    {
        pool.query('SET NAMES utf8');
        pool.query('SET CHARACTER SET utf8');
        logger.trace('[INFO] Connected to database and set utf8!');
    }
});

}

All queries on the node are simply run with pool.query

The database_connection() is called on start of the node

Answer 1

You've set connectionLimit to five thousand! That means the node-mysql subsystem will keep trying to handle multiple query requests by adding new connections until it has five thousand. Your MySQL server has a connection limit of 100, it seems, so your nodejs app blows out when that limit is reached.

Set connectionLimit: 10,

Then you'll use ten pool connections, and when they're all in use your pool.query invocations will wait until one becomes available.

Why not set the limit to 100? Two reasons:

1. You want to leave some MySQL connections for other client software.
2. If you hammer the database with many similar queries (in your case INSERT queries) in parallel, it will spend time avoiding contention. With fewer connections in parallel, each query will finish much faster.