When use poolConnection or CreateConnection felixge/node-mysql

Question

I use https://github.com/felixge/node-mysql for my application When and Why use

db_pool = mysql.createConnection(db);

or

db_pool = mysql.createPool(db);

what are the differences? and when to use them?

Answer 1

A single connection is blocking. While executing one query, it cannot execute others. Hence, your DB throughput may be reduced.

A pool manages many lazily-created (in felixge's module) connections. While one connection is busy running a query, others can be used to execute subsequent queries. This can result in an increase in application performance as it allows multiple queries to be run in parallel.

Answer 2

Connection pooling allows you to reuse existing database connections instead of opening a new connection for every request to your Node application.

Many PHP and .Net folks are accustomed to connection pooling, since the standard data access layers in these platforms pool connections automatically (depending on how you access the database.)

Opening a new database connection takes time and server resources. Using a connection that is already there is much faster, and overall, your application should need to maintain less total open connections at any one time if you use connection pooling.

The connection pooling functionality of node-mysql works very well and is easy to use. I keep the pool in a global variable and just pass that to any modules that need to access the database.

For example, here the env_settings variable in the app server holds global settings, including the active connection pool:

var http = require("http");
var mysql = require('mysql');

var env_settings = {
    dbConnSettings: {
        host: "localhost",
        database: "yourDBname",
        user: "yourDBuser",
        password: "yourDBuserPassword"
    },
    port: 80
};

// Create connection pool
env_settings.connection_pool = mysql.createPool(env_settings.dbConnSettings);

var app = connect()
    .use(site.ajaxHandlers(env_settings));

http.createServer(app).listen(env_settings.port);

And here is the ajaxHandlers module that uses the connection pool:

ajaxHandlers = function (env_settings) {

    return function ajaxHandlers(req, res, next) {

        var sql, connection;

        env_settings.connection_pool.getConnection(function(err, connection) {

            sql = "SELECT some_data FROM some_table";

            connection.query(sql, function(err, rows, fields) {

                if (err) {
                    connection.release();
                    // Handle data access error here
                    return;
                }

                if (rows) {
                    for (var i = 0; i < rows.length; i++) {
                        // Process rows[i].some_data
                    }
                }

                connection.release();
                res.end('Process Complete');
                return;
            });
        });
    }
}

/* Expose public functions ------ */
exports.ajaxHandlers = ajaxHandlers;

The connection_pool.getConnection method is asynchronous, so when the existing open connection is returned from the pool, or a new connection is opened if need be, then the callback function is called and you can use the connection. Also note the use of connection.release() instead of ending the connection as normal. The release just allows the pool to take back the connection so it can be reused.

Here is a good way to think about the difference. Take the example of a very simple app that takes requests and returns a data set containing the results. Without connection pooling, every time a request is made, a new connection is opened to the database, the results are returned, and then the connection is closed. If the app gets more requests per second that it can fulfill, then the amount of concurrent open transactions increases, since there are more than one connection active at any time. Also, each transaction will take longer because it has to open a new connection to the data server, which is a relatively big step.

With connection pooling, the app will only open new connections when none are in the pool. So the pool will open a bunch of new connections upon the first few requests, and leave them open. Now when a new request is made, the connection pooling process will grab a connection that is already open and was used before instead of opening a new connection. This will be faster, and there will be less active connections to the database under heavy load. Of course, there will be more "waiting" connections open when no one is hitting the server, since they are held in the pool. But that is not usually an issue because the server has plenty of resources available in that case anyway.

So database connection pooling can be used to make your app faster, and more scalable. If you have very little traffic, it is not as important - unless you want to return results as quick as possible. Connection pooling if often part of an overall strategy to decrease latency and improve overall performance.