Pool connectionsOpen and connectionsInUse values on Network drops

[dvpatel]

Greeting,

I have general questions about pool properties for cases where connections are dropped below poolMin due to actions taken by network intermediaries, ie. firewall dropping idle connections.

For example, if my pool is configured for poolMin = 5, poolMax = 10 and default poolPingInterval, what will the output of pool.connectionsOpen and pool.connectionInUse be if pooled connections drop below the poolMin? I am seeing output in my application where pool.connectionsOpen drops to 0 and then back to 1 but never regaining poolMin. With this output, is my app really using only a single connection? And why isn't poolMin maintained for cases where network intermediaries is dropping connections?

I'm using Oracle client 19.X but am a bit behind with the npm module at 3.X.

Thank you for your time and support.
-DP

[cjbj]

The pool is a living thing. At the lower level, internal heuristics in the Oracle Client libraries determine its behavior. This is continually being improved in new versions of Oracle Client. On top of this is Connection Pool Pinging (also see here), Fast Application Notification (FAN), and network settings to alter timeouts.

This text from here may help:

There are really 3 bits to a 'connection': 1. the memory structure in node-oracledb that is returned by a getConnection() call, 2. the network "pipe" to the database, and 3. a server process on the database computer.

A pool can be 0 or more connections. The pooled connections start with all three of the above parts, but network glitches or DB outages can mean that a pool.getConnection() will return the memory structure but, when you try to execute a statement, you will get an error.

If a node-oracledb app was somehow magically frozen, and the network dropped out, I would not expect connectionsOpen or connectionsInUse to change. This is because the pool and the connections (the 1st bit above) in it aren't aware of the network change (note FAN will make the pool aware and let it do some clean up). Awareness may come when the app tries to get a connection from a pool (via some internal checks, and with poolPingInterval), and definitely comes when a connection is used e.g. with execute().

You don't mention your poolIncrement or how many parallel requests you are loading the app with. If you only see 1 connection in the pool, then I expect it only needs 1 connection. @krismohan may, or may not, want want to comment on the underlying Oracle Client heuristics about re-establishing poolMin.

If you want to stop firewalls killing network connections, use EXPIRE_TIME. With 19c Client, you can set this in the easy connect string.

[dvpatel]

Thanks for the details Christopher. Very helpful!!

I did upgrade the node module to use the latest version but still seeing the same behavior of pooled connections dropping to zero when network pipes break. On a subsequent request, the module does open a single new connection but not poolMin. I'm also seeing an increase in the number of queued requests which makes sense since only a single connection is being used to process incoming requests.

You also asked a question about poolIncrement. I am using a default value. Should this value be set to poolMin to recover the pool after broken network connections? If not, then it sounds like I need to monitor the pool from my application and recover when connectionsInUse drops below poolMin or to zero.

[cjbj]

If you are seeing queuing, then it sounds like your app is not calling connection.close() correctly. (assuming your app is not being invoked with parallelism greater than your poolMax). Check your error handling. Queuing only takes place when node-oracledb thinks all connections in the underlying Oracle session pool have been obtained with a pool.getConnection() call and not yet released with connection.close().

[dvpatel]

I double checked, connections are properly released back into the pool. I also noticed right around the time pooled openConnection drops, I am seeing errors in Oracle client library log. The specific error I'm getting is 'Fatal NI connect error 12170' with error code TNS-12535.

I feel this is another indication that the network pipe is breaking due to idle connection. Because of this, pool drops down to a single value over time, even though poolMin is a much higher value. What is the best way to recover the pool back to poolMin? Is there a configuration setting(s) in this module to handle this scenario? My thought was to set poolIncrement = poolMin

[cjbj]

Apparently the lower level "session pool" in the Oracle client library won't re-establish poolMin until there is sufficient load.

[dvpatel]

Thank you Christopher.

Should an enhancement request be submitted so that active connections are always maintained by this library under certain pool properties? There may be use cases where the only way to maintain active connections is by forcing it in the client code. A configurable option to do this might be of value.

[cjbj]

@dvpatel in an earlier (offline) discussion with @krismohan he said he would note the request.

[cjbj]

@dvpatel If you ever want to track the request within Oracle, it is enhancement 31177595.

[dvpatel]

Thanks Christopher and team for all the support.