Mysql: Increasing number of connections to mysql

[nareshbhatia]

I am trying to performance tune a small sample app. I started with 100 connections and hitting the node server as fast as possible. I got a throughput of ~1200 request/second. Now I tried to increase the number of db connections to 400 (by setting the connectionLimit). However, when I do this and start hitting the server hard, I get the following error:

ER_CON_COUNT_ERROR: Too many connections

How can I increase the number of connections? I don't think this is an issue on the MySQL end because I am easily able to run the same test using a Java server on the same MySQL instance.

Here's my node.js code:

var Mysql = require('mysql');

var pool = Mysql.createPool({
    host: 'localhost',
    user: 'bfoms_javaee',
    password: 'bfoms_javaee',
    database: 'bfoms_javaee',
    connectionLimit: 400
});

exports.findAll = function(req, res) {

    pool.getConnection(function(err, connection) {
        if (err) throw err;

        connection.query('SELECT * FROM orders WHERE symbol="GOOG"', function(err, rows, fields) {
            if (err) throw err;

            res.send(rows);
        });

        connection.release();
    });
};

[dougwilson]

This means that your Java test is not making 400 simultaneous connection like node.js is. The default connection limit for MySQL is right around 100, so if you have never changed that, you'll need to. The following is a page from the MySQL manual for how to see you limit and change it:

http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

[nareshbhatia]

Doug, thanks for your quick response.

I get your point about the default limit of 100 connections on MySQL. Since I have done nothing special to my MySQL instance, I am assuming that that limit is in effect and the Java instance is indeed making atmost 100 connections. This makes the problem even more interesting since the Java instance is able to sustain about 3200 http requests/second which is a lot more that the 1200 request/second on node.js. So what limits might I be hitting in node.js? Again, I have done nothing to tune the node instance.

[dougwilson]

@nareshbhatia I'm not sure without seeing the code and this would also be beyond the score of an issue with the mysql library. I would suggest you post of something like stackoverflow.com with your Java and node.js codes as ask there for help in why you are seeing such a difference.

[nareshbhatia]

@dougwilson, will do. Thanks for your insights.

[nareshbhatia]

One follow-up question. I started monitoring MySQL connections and turns out that there is only one connection open at any time! I was assuming that with the asynchronous nature of node.js I would not block on database calls and multiple HTTP requests could be in flight simultaneously. What am I missing here? I am using a connection pool as shown above, so there should be plenty of connections available. My application level code simply sets up Express with one route as shown below, so it should be able to serve multiple HTTP requests simultaneously:

// GET /orders
app.get('/orders', OrderResource.findAll);

// Create an http server and give it the
// express app to handle http requests
var server = Http.createServer(app);
server.listen(8080, function() {
    console.log('Listening on port 8080');
});

Any clarification would greatly help my understanding.

Thanks.

[dougwilson]

If you have you pool set to 10 connections, it does not open all 10 connections up front. It only opens connections as necessary. You have to make parallel HTTP calls to your express server to see more than one open. An easy way to check is set change your SELECT to SELECT SLEEP(10) and then open the /orders page in multiple browser tabs. You should then see multiple connections open.

[dougwilson]

Oh, sorry, it is because you are using connection.release() wrong, so all your queries are queuing on a single connection. See below:

exports.findAll = function(req, res) {

    pool.getConnection(function(err, connection) {
        if (err) throw err;

        connection.query('SELECT * FROM orders WHERE symbol="GOOG"', function(err, rows, fields) {
            connection.release() // <-- must be here, AFTER you finished your query and before err handling
            if (err) throw err;

            res.send(rows);
        });

        //connection.release(); // <-- no, not here
    });
};

Your findAll function can also be simplified by using pool.query and it'll run connection.release() in the proper location for you:

exports.findAll = function(req, res) {

    pool.query('SELECT * FROM orders WHERE symbol="GOOG"', function(err, rows, fields) {
        if (err) throw err;

        res.send(rows);
    });
};

[nareshbhatia]

Thank you! Putting connection.release() at the right place fixed it.

[mitulparmar21696]

var q = connection.query(strQuery, queryParams, function (error, results, fields) {
connection.release();
if (error) {
console.log(q.sql);
console.log(error);
console.log(results);
console.log(strQuery);
}
callback(req, res, error, results, fields);
});

I put connection.release(); like this still getting error of too many connection.
Is there any other way?

[antontsvil]

@mitulparmar21696 try checking the max_connections variable in your mysql server.

show variables like "max_connections";

This variable will determine the max amount of connections your mysql server will allow at one time. You can modify it like so

set global max_connections = 200;
I referenced this link for this info