Node-mssql: Connection Pooling Best Practice

[mhunting]

I’ve been using mssql with tedious and it’s been working well, but I’m concerned I’m not using the connection pool correctly. I’m using the built in sproc sp_who2 to see the connections from my applications. Even though I have only six instances of my app running with a max connections set to 20 I’m still seeing several connections in a sleeping status 800 + after running for a while.

I’m looking for a javascript way to ensure all connections get cleaned up even in cases of error. A best practice code snippet.

Thanks in advance,

var DAOConfig= {
    server: '*****',
    user:     '******',
    password: '********',
    driver: 'tedious',
    database: '******',
    options: {
        // tedious options
        //instanceName:''
        appName:'****',
        connectTimeout:15000,
        requestTimeout:150000
    },
    pool: {
        max: 20,
        min: 10,
        idleTimeoutMillis: 3000
    }
};

var sql = require('mssql');

var DAO = function(daoConfig, path) {

    var self = this;
    self.engineType = null;

    function doneCallBack(jobtype, elapsed){
        console.log("%s took %d seconds and (%d milliseconds or %d nanoseconds - pid:%d)", jobtype, elapsed[0], elapsed[1]/1000000, elapsed[1], process.pid);
    }

    self.Init = function (callback) {
        callback();
    };

    self.GetJobsFromQueue = function (sessionToken, engineID, callback) {

        var error = null;

        sql.connect(daoConfig, function (err) {

            if (err) {
                console.log(err);
            }
            else {
                var jobs = {
                    SessionToken: sessionToken,
                    Jobs:[]
                };

                var request = new sql.Request();

                request.input('Session', sql.VarChar(128), sessionToken);
                request.input('EngineID', sql.Int, engineID);
                //todo:make config value for MaxProcessingSeconds
                request.input('MaxProcessingSeconds', sql.Int, 300);
                request.input('BatchSize', sql.Int, 100);
                var start = process.hrtime();
                request.execute("[db].[schema].[sproc]", function (err, recordset) {
                    sql.close();
                    doneCallBack('Get jobs for session',process.hrtime(start));
                    if (err) {
                        console.log(err);                       
                        callback(err, null);
                    }
                    else {
                        if(recordset[0].length > 0) {
                          ...
                        }else{
                            console.log('No jobs found');
                        }
                        //build up jobs result
                            ...
                    }

                    callback(error, jobs);
                });
            }

        });
    };    
};

module.exports = DAO;

[patriksimek]

I only want to let you know that I have this in mind but had not enough time to resolve this yet. Hope I will take a look into this soon.

[mhunting]

I appreciate your time. Let me know how I can help.

From: Patrik Simek [mailto:[email protected]]
Sent: Wednesday, August 27, 2014 1:33 AM
To: patriksimek/node-mssql
Cc: Michael Hunting
Subject: Re: [node-mssql] Connection Pooling Best Practice (#56)

I only want to let you know that I have this in mind but had not enough time to resolve this yet. Hope I will take a look into this soon.

—
Reply to this email directly or view it on GitHubhttps://github.com/patriksimek/node-mssql/issues/56#issuecomment-53535764.

[patriksimek]

So, finally I have made some tests and found no problems in connection pool at all.

Maximum number of connections exposed by sp_who2 was always the same as max config. I have also test automatic releasing (idleTimeoutMillis) and min config and found no issues.

I would recomend you to use option config.options.appName to set custom app name for each process. Once you do that, you will have a clear view on what app uses more connections than you have allowed it to use.

From the code you posted I can see you're using global connection. I would recomend you to use new connection instead. See: https://github.com/patriksimek/node-mssql#quick-example

Hope it helps.

[tracker1]

It's worth noting that the pool is per process... if you're using cluster, especially on multiple machines, then you will have up to 20 connections per clustered process, per machine you have connected..

[patriksimek]

Closing due to inactivity.

[uNki23]

i hope it's okay to post to this closed thread again. i'm facing a similar problem/question since no real best practice advise can be found on the net.

From the code you posted I can see you're using global connection. I would recomend you to use new connection instead. See: https://github.com/patriksimek/node-mssql#quick-example

does that mean my database related functions (like getAccountsById() or updateUserSettings()) should look like your quick example. meaning that each function should create a new connection?
what about closing the connection explicitly with "connection.close()" after the request was successful? is it needed or done automagically?

thanks!!