Pool objects are not going back to available pool.

[JankiGadhiya]

Hi All,

We are in a serious mess because of this issues. My website is in production and every single day we are facing this issues since last 2 week.

My pool connection configurations are:

pool: {
      min: 5,
      max: 30,
      acquireTimeoutMillis: 60000,
      evictionRunIntervalMillis: 1000
    }

at the start of the service my pool statistics are:
Available : 30
Size : 30
Pending : 0
Borrowed : 0

After execution of some 500 requests, The pool statistics are doubtable.

Available : 0
Size : 30
Pending : 0
Borrowed : 0

i.e. the pool should contain minimum available connections after the execution but it is not happening in my case.

For this pool statistics I am getting Resource Request timeout . There are cases when it is working fine.

Available : 1
Size : 30
Pending : 0
Borrowed : 0 (Working)

Available : 30
Size : 30
Pending : 0
Borrowed : 0 (Working)

Available : 0
Size : 30
Pending : 0
Borrowed : 0 (Not Working)

The mssql module is not able to create available minimum connection after some time. Can anyone suggest any connection configuration or is this any bug?

PS : My MSSQL is azure managed service.

[sandfox]

Have you attached any event listeners for the factory errors, and if so, have they logged anything?

pool.on('factoryCreateError', function(err){
  //log stuff maybe
})

pool.on('factoryDestroyError', function(err){
  //log stuff maybe
})

Also - could you paste/show your factory object, and your mysql connection settings (or whatever the defaults are).

I'll try to take a look at this in more detail later, but I suspect this probably fixable by tuning the connection settings (I'll try to find a GitHub issue I saw where some had a similar problem to you).

Unfortunately the current stats the pool shows you aren't very detailed don't include many of the internal queues, which would probably help pinpoint where something might be going wrong.
it might be helpful to log out these pool internals as well:

pool. _factoryCreateOperations.size
pool._factoryDestroyOperations.size
pool._validationOperations.size

[JankiGadhiya]

Thank you so much @sandfox. I have added the logs and error listers as you suggested.

available 3
size 30
pending 0
borrowed 0
_factoryCreateOperations 2
_factoryDestroyOperations 0
_validationOperations 0

There are times when _factoryCreateOperations gets stuck to a non zero number and service show no error at all. (like above scenario) and then the pool object never gets created and my available count is always (min - number of _factoryCreateOperations stuck).

available 0
size 30
pending 0
borrowed 0
_factoryCreateOperations 5
_factoryDestroyOperations 0
_validationOperations 0

In above case, there are times when my available falls to 0, _factoryCreateOperations is 5 (which 5 connections never gets created.) and I have no option other then crashing/restarting my node service.

It also happens 1 in 10 cases that my azure database gives connection timeout but after a millisecond I am able to connect to the database successfully.

Is there any way by which we can retry _factoryCreateOperations??

[munir131]

@sandfox It looks like #198 & #221 are related to this issue. Will you verify those PRs and release new version?

[JankiGadhiya]

Guys, Any updates in this? My production is heading to a big event today and I am in a big mess because of this issue.

[sandfox]

Hey @JankiGadhiya sorry, I've been a bit busy and probably won't have time to properly look at this till the weekend.

I am guessing here, but it could be worth using 'promise-retry as mentioned here: https://github.com/coopernurse/node-pool/pull/198/files but instead inside your factory.create function and see if that fixes anything. As it looks like the createConnection calls are just hanging" I think it might also be worth adding something like a timeout to your factory.create calls if you can. There might be a setting in your mysql driver to alter the timeouts, or how long the client will wait to try and establish a connection before giving up.

As long as your factory.createfunction errors, then pool will continue to try making new connections, but if your factory.create function just hangs it can't do anything to help.

I'll have a look at adding timeouts to pool to protect against this.

edit: I only just saw you've edited your previous messages with more info! thanks

[JankiGadhiya]

@sandfox, Can you please do these changes to the generic-pool code and release a new version?

[JankiGadhiya]

@sandfox Hi,

I am not able set retry logic for factory create calls. Can you please help me with this?

[jacqt]

@JankiGadhiya I ran into a similar problem with MS SQL, perhaps this will help you - tediousjs/node-mssql#610 (comment)