Mysql: Pool.Query not closing connections?

[CyrisXD]

Apologize if I'm just not understanding how pooling works but... could someone please shed some light?

I've written a small webapp that uses ClearDB on Heroku, I'm using connection pooling to handle queries.

var mysql = require('mysql');
var pool = mysql.createPool({
    connectionLimit: 10,
    host: '',
    user: '',
    password: '',
    database: ''
});

pool.query("Example Query", function(err, rows, fields) { 
//process results
}); 

In some cases, used a second nested query. Example:

pool.query("Example Query", function(err, rows, fields) { 
//process results

    pool.query("Example Query 2", function(err, rows, fields) { 
    //process results
    }); 

}); 

If I'm not mistaken "pool.query" is supposed to connect to the server, process, and close the connection automatically once done.

However when I did some testing here, I had 5 people visit the site and mess around so I can see the current connection count on ClearDB.

The connection count was at 5, but I am only allowed 10 connections on my current plan. So does that mean if I had more than 10 people on the site the SQL server would reach the connection limit?

I'm a bit confused by all this.

[dougwilson]

Hi @CyrisXD, the entire point of a connection pool is _not_ to close the connection. A connection pool is a generic concept you can read more about at https://en.wikipedia.org/wiki/Connection_pool

[CyrisXD]

Thank you @dougwilson, so if a connection is opened. That connection is not set to that user only correct? Other users who attempt a database connection should use whatever connection is open?

Does "pool.query" create a new connection each time? When I visit my page it creates a two connections, I believe this must be because of my nested queries.

[dougwilson]

Hi @CyrisXD it works pretty much exactly how that article describes. To answer those three questions:

That connection is not set to that user only correct?

Correct.

Other users who attempt a database connection should use whatever connection is open?

Correct.

Does "pool.query" create a new connection each time?

The answer is it depends. If all the connections are being used, a new connection is made and is added to the pool, otherwise an unused connection is used.

[CyrisXD]

Thank you very much @dougwilson, finally some clarity.

If all 10 connections were opened, pooling would queue the request until a connection is not being used?

Apologies for all the questions, I'm expecting +- 100 concurrent users and just wondering if 10 connections would be enough.

[dougwilson]

If all 10 connections were opened, pooling would queue the request until a connection is not being used?

Right. The pool has a limit you can set (defaults to 10) and when all of those connections are used, any new pool.query commands will wait until one is complete and then use that now-free connection. This has the benefit of ensuring that you stay below any connection limits, no matter the load on your server.

Apologies for all the questions, I'm expecting +- 100 concurrent users and just wondering if 10 connections would be enough.

Of course, users will use other resources besides your MySQL connections, and mostly people idle (i.e. if 100 people are staring at your site, not all of them are even actively loading a page). But is 10 connections enough is hard to know, especially because a large part of that is how fast your queries themselves are. If your queries average 10 milliseconds and your connection limit is 10, you can expect to handle about 1000 queries/second without any real connection queuing.

[sidorares]

If all 10 connections were opened, pooling would queue the request until a connection is not being used?

queue or respond immediately with error - depends on queueLimit option. Default is 'queue', but it's usually a good idea to set to some reasonable value - you don't want 1000s of callbacks to sit in the queue under high load, sometimes it's better to fail fast

[CyrisXD]

Thanks @dougwilson and @sidorares, it is very much appreciated.