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:
- poolMin: 5
- poolMax: 10
- poolIncrement: 1
- poolTimeout: 10 seconds
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.
Questions:
- What could be the possible reasons for the connection pool not releasing connections back to `poolMin`?
- How can we ensure that the connection pool releases idle connections as expected?
Any insights or suggestions would be greatly appreciated. Let me know if you need me to add anything to provide better answers.
Thank You.
Additional Info:
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.