CODEWITHSUNDEEP
node.jsoracle-databaseconnection-poolingnode-oracledbnode-oracle
Srikanth Vudharapu
Srikanth Vudharapu

Reputation: 1

Issue with node-oracledb Connection Pool Not Releasing Connections Back to Database

We're experiencing a steady increase in PGA memory in our Oracle database, and we're trying to identify the root cause. We suspect that the connection pool in our application is not releasing connections back to the database as expected. Here are the details of our configuration and the issue we're facing:

Connection Pool Configuration:

Issue Description: During periods of traffic, the number of connections increases from 5 (poolMin) to 10 (poolMax). However, when the traffic is low, the connections are not being released back to 5 (poolMin), even after 10 seconds (poolTimeout) of inactivity.

Reference: According to the node oracledb documentation:

If the application returns connections to the pool with connection.close(), and the connections are then unused for more than poolTimeout seconds, then any excess connections above poolMin will be closed. When using Oracle Client prior to version 21, this pool shrinkage is only initiated when the pool is accessed.

Let me know if you need me to add anything to provide better answers.

What I Tried:

  1. Monitoring Connections: Used the pool.getStatistics() method to monitor the number of open, in-use, and idle connections in the pool.
  2. Traffic Simulation: Simulated traffic using a k6 script to observe the behavior of the connection pool during periods of high and low traffic.
  3. Database Query: Ran a query to monitor active connections and session PGA memory in the Oracle database.

What I Expected:

  1. During periods of high traffic, I expected the number of connections to increase from 5 (poolMin) to 10 (poolMax).
  2. During periods of low traffic, I expected the number of connections to decrease back to 5 (poolMin) after 10 seconds (poolTimeout) of inactivity.
  3. I expected the session PGA memory to decrease correspondingly as idle connections are terminated.

What Actually Happened:

  1. During high traffic, the number of connections increased to 10 as expected.
  2. During low traffic, the number of connections did not decrease back to 5, even after 10 seconds of inactivity. Though sometimes, it decreased one by one, but not all the way back to 5 (poolMin value)
  3. The session PGA memory did decrease to some extent, but few idle connections were not being terminated.

Question: What could be the possible reasons for the connection pool not releasing idle connections back to poolMin when there is not much traffic?

Upvotes: 0

Views: 73

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10556

As discussed on Slack you are using Thick mode with Instant Client 19c libraries.

Your scenario is covered in the node-oracledb doc:

When node-oracledb Thick mode is using using Oracle Client 19 or earlier, this pool shrinkage is only initiated when the pool is accessed, so a pool in a completely idle application will not shrink."

This was the original design of Oracle client libraries. It was only in 21c that some threading was introduced and background closure can occur.

Our performance team very strongly advise using a fixed size pool to avoid database load issues, so dynamic pools should not be an issue for most people. Also keep the pool size small.

If you have a very specific use case that want to force close a connection then you can drop it from the connection pool with:

await connection.close({drop: true});

The pool will regrow as needed.

Regarding the PGA, there are various resources around, including the Support note How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1), the user documentation Tuning the Process Global Area, and blogs like Don’t run tight on PGA from one of Oracle's PMs (the take-home point from the latter is to make sure you are on the latest database Release Update).

Upvotes: 0

Related Questions