Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

What am I missing with releasing pool? #58

Open
drewhamlett opened this Issue · 12 comments

5 participants

@drewhamlett

Ok I have a min of 2 max of 3. I run a query on a page hit. I hit the page once fine. I refresh gets another connection, fine. Hit the page again it grows the pool and gets a connection. Hit the page again and nothing happens. I would think that if I'm releasing the pool after each db query then the pool would shrink? I thought that was the whole point of a pool?

var pool = poolModule.Pool({
    name: 'mysql',
    create: function (callback) {

        console.log('create');

        var connection = mysql.createConnection({
            host: 'localhost',
            user: 'admin',
            password: 'test',
            database: 'test'
        });

        connection.connect(function () {
            return callback(null, connection);
        });

    },
    destroy: function (client) { // this is never called.  ever

        console.log('destroy');
        client.destroy();
    },
    max: 3,
    min: 2,

    idleTimeoutMillis: 3000, //changed this  to 3000
    log: true
});


pool.query = function (query, data, callback) {

    try {

        pool.acquire(function (err, client) {

          //called 3 times.  never called again.

            client.query(query, data, function (err, results, fields) {

                try {

                    pool.release(client); // I've also tried pool.destroy(client);          
                    return callback(err, results);

                } catch (err) {
                    console.log(err)
                }

            });
        });
    } catch (err) {
        console.log(err);
    }   
};
@kadishmal

I have an identical problem. I acquire CUBRID database connection via node-cubrid, then always release the connection after I'm done with querying. However, just like @drewhjava said, the pool doesn't shrink. In fact, whenever I refresh the page:

  1. It hangs because no connection can be acquired;
  2. The number of waiting clients in the pool grows:

    ...
    INFO pool CUBRID - dispense() clients=72 available=0
    INFO pool CUBRID - dispense() clients=73 available=0
    INFO pool CUBRID - dispense() clients=74 available=0
    INFO pool CUBRID - dispense() clients=75 available=0
    INFO pool CUBRID - dispense() clients=76 available=0
    ...
    

What am I missing here?

@kadishmal

One problem I found while investigating this issue is that generic-pool calls removeIdle() to remove those connections which have been idle for quite some time even though waitingClients.size() > 0, which I think is incorrect. The pool should not destroy an idle connection if there are clients which are waiting for a connection.

@zekenie

1+ I'm having the same problem with node-mysql

@coopernurse
Owner

Hi. I took the code @drewhjava posted and added a loop that runs "select 1" 10 times then calls pool.drain() to destroy the pool. I didn't see anything unusual in the output. Here's the gist:

https://gist.github.com/coopernurse/5233934

I'm curious if you get different behavior. I'm using the latest generic-pool.js code, which should match the latest version in npm.

@kadishmal
@zekenie

In my case its not that the connections don't close its that when they come in rapid succession, they start to hang for several minutes

@raajeshk88

For me too... I have a mysql connection pool which is set to min =5, max =50. Eventhough i release the connection my available.length=1 always. @coopernurse could you please help me by explaining why the available.length=1

@coopernurse
Owner

@raajeshk88 Please post a minimal case that reproduces this and I can try to explain it.

@raajeshk88

@coopernurse I have a mysql pool configured as
var pool = poolModule.Pool({
name : 'mysql',
create : function(callback) {
var config = require('./config');
var Client = require('mysql');
c = Client.createConnection({
host : config.MYSQL_SERVER,
user : config.MYSQL_USERNAME,
password : config.MYSQL_PASSWORD,
database : config.MYSQL_DB ,
});
c.connect();
callback(null, c);
},
destroy : function(client) { client.end();
},
max : 50,
min : 5,
idleTimeoutMillis : 3000,
log : false
});

I acquire connections,query and release as
var dosomething = function(k){
pool.acquire(err,conn){
conn.query(query1{
for (loop) { }
conn.query( query2 { })
pool.release(conn)
})
}

for (loop) { dosomething(k);}

I call the function dosomething 8 times and my output was

VERBOSE pool mysql - createResource() - creating obj - count=1 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866596
INFO pool mysql - dispense() clients=0 available=1
VERBOSE pool mysql - createResource() - creating obj - count=2 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866600
INFO pool mysql - dispense() clients=0 available=2
VERBOSE pool mysql - createResource() - creating obj - count=3 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866602
INFO pool mysql - dispense() clients=0 available=3
VERBOSE pool mysql - createResource() - creating obj - count=4 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866603
INFO pool mysql - dispense() clients=0 available=4
VERBOSE pool mysql - createResource() - creating obj - count=5 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866604
INFO pool mysql - dispense() clients=0 available=5
INFO pool mysql - dispense() clients=1 available=5
VERBOSE pool mysql - dispense() - reusing obj
INFO pool mysql - dispense() clients=1 available=4
VERBOSE pool mysql - dispense() - reusing obj
INFO pool mysql - dispense() clients=1 available=3
VERBOSE pool mysql - dispense() - reusing obj
INFO pool mysql - dispense() clients=1 available=2
VERBOSE pool mysql - dispense() - reusing obj
INFO pool mysql - dispense() clients=1 available=1
VERBOSE pool mysql - dispense() - reusing obj
INFO pool mysql - dispense() clients=1 available=0
VERBOSE pool mysql - createResource() - creating obj - count=6 min=5 max=50
INFO pool mysql - dispense() clients=1 available=0
VERBOSE pool mysql - createResource() - creating obj - count=7 min=5 max=50
INFO pool mysql - dispense() clients=1 available=0
VERBOSE pool mysql - createResource() - creating obj - count=8 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866627
INFO pool mysql - dispense() clients=0 available=1
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866637
INFO pool mysql - dispense() clients=0 available=2
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866640
INFO pool mysql - dispense() clients=0 available=3
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866642
INFO pool mysql - dispense() clients=0 available=4
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866645
INFO pool mysql - dispense() clients=0 available=5
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866656
INFO pool mysql - dispense() clients=0 available=6
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639866659
INFO pool mysql - dispense() clients=0 available=7
count :7size : 8
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639867073
INFO pool mysql - dispense() clients=0 available=8
VERBOSE pool mysql - availableObjects.length=8
VERBOSE pool mysql - availableObjects.length=8
VERBOSE pool mysql - availableObjects.length=8
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639867599 timeout:1379639866627
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639867599 timeout:1379639866637
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639867599 timeout:1379639866640
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639867599 timeout:1379639866642
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639867599 timeout:1379639866645
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639867599 timeout:1379639866656
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639867599 timeout:1379639866659
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639867599 timeout:1379639867073
VERBOSE pool mysql - createResource() - creating obj - count=5 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639870602
INFO pool mysql - dispense() clients=0 available=5
VERBOSE pool mysql - createResource() - creating obj - count=5 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639870603
INFO pool mysql - dispense() clients=0 available=5
VERBOSE pool mysql - createResource() - creating obj - count=5 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639870604
INFO pool mysql - dispense() clients=0 available=5
VERBOSE pool mysql - createResource() - creating obj - count=5 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639870604
INFO pool mysql - dispense() clients=0 available=5
VERBOSE pool mysql - createResource() - creating obj - count=5 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639870605
INFO pool mysql - dispense() clients=0 available=5
VERBOSE pool mysql - availableObjects.length=5
VERBOSE pool mysql - availableObjects.length=5
VERBOSE pool mysql - availableObjects.length=5
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639870604 timeout:1379639870602
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639870604 timeout:1379639870603
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639870604 timeout:1379639870604
VERBOSE pool mysql - removeIdle() destroying obj - now:1379639870604 timeout:1379639870604
VERBOSE pool mysql - createResource() - creating obj - count=5 min=5 max=50
INFO pool mysql - return to pool
VERBOSE pool mysql - timeout: 1379639873606
INFO pool mysql - dispense() clients=0 available=5
VERBOSE pool mysql - createResource() - creating obj - count=5 min=5 max=50
INFO pool mysql - return to pool

@raajeshk88

As you could see the removeIdle removes all the 8 resources even when my min is set to 5. I even tried removeIdle =false then too it removes all the resources and recreates them.

When 50 connections are created it will nolonger create or dispense any connections hence i cannot call the function more than 6 times and on 7th time only 2 connections are dispensed( 6*8 + 2)

@coopernurse
Owner

Re removeIdle behavior: you can set refreshIdle=false in your factory options passed to poolModule.Pool() -- this will prevent the destroy/re-create case. With a MySQL pool I would recommend against that because the sockets could timeout. Personally I'd increase the idle timeout to 30 seconds and leave the refreshIdle=true (default).

Re: 50 connections created: Once the pool max is reached, calls to acquire are placed in a queue. As resources are released, the queued callers are dispensed the available resources.

If you have a base repro case that shows that this doesn't work, please submit it? Typically when folks experience deadlocks with their pool it's because they're leaking resources.

If you have a base repro, I'll need the exact nesting of your callbacks in order to assist (which your pseudo code above omits). This stuff can get tricky fast, so you can omit your actual sql statements, but the nesting of the callbacks cannot be omitted if you want help debugging a leak.

cheers

@raajeshk88

Hi ,
Re: 50 connections created : This was not working properly may the resources are not released after they are acquired and hence i see that once all the connections are acquired the log logs as
clients =1; available =0;
clients =2; available =0;
clients =3; available =0;
clients =4; available =0;
clients =5; available =0;

and so on.

I have pasted my exact workflow here... please kindly help and thanks for all your support so far

var taxcl = fucntion (len,callback){

var iterate = function (k)
{
var pool = require('./databaseConnection');
pool.acquire(function (err,connection){
var tmp = " SELECT EMP_NAME FROM EMPLOYEE";
connection.query(tmp,function (err,rows){
    if (err) catch_Exp(err);
    try { 
    if (rows.length == 0) throw (new Error("No data found"));
     var tmp_query1 = "SELECT SALARY FROM SALARY WHERE EMP_NAME ="+rows[0].EMP_NAME;
    connection.query(gps_query,function (err, rows) {
        if (err) catch_Exp(err);
    { 
    var update_query = "UPDATE `tax` SET EMP_TAX?  WHERE EMP="+rows[0].EMP_ID;
    connection.query(updat_query,10,function (err, results){
            if (err) catch_Exp(err);
        });
    }
        });


            for ( var i=0; i<count ;i++)
            {
            var max = 0;
            var min = 0;    
            sal = 10000;
                        for ( var j=0;j<rows.length;j++)
                {
                            if (rows[j].SAL == sal)
                    {
                        if( (value <=  rows[j].MAX_VALUE) && (value >=  rows[j].MIN_VALUE))
                        {
                         tax calcualtion
                        }
                    }
                }
            }
                       if( k == (len))
            {
            pool.release(connection);
            callback(1);
            }
            }
            catch (Exp )
            {           
                 callback(1);
            }       


    });                      
 });
}

while (k < len)
{
  iterate(k++);
} 
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.