The exception “All connections are in use” indicates the shortage of connections in the pool at some moment meaning that the UCP is unable to satisfy an application’s connection borrow request.
There could be two possible reasons for that:
- A UCP-based application borrows connections holding them for a long time without usage and never returns them back to a pool or return it after a long time not using it. This is the classic use case of connection leakage. It needs to be fixed by eliminating non-productive connection borrows for long periods (and maybe forever).
- There are no connection leakages in the investigated application, as described in 1, but the pool has inessential capacity for processing the whole flow of borrow requests. In this case pooled connections supply is simply too small to do the expected job.
Universal Connection Pool has rich collection of solutions to analyze connection leaks and tune up pool properties to optimize its operation. Please refer to the “UCP Developer’s Guide” for details on the features and debugging/tuning tips.
Following is the list of connection properties, timeouts and callbacks with a short description of their function.
- Abandoned Connection Timeout (ACT) – UCP API that allows to set up a timeout for a connection that has been borrowed but unused. With this timeout set, all connections that are leaked and under-used will be revealed.
- Time-To-Live Connection Timeout (TTL)– similarly as ACT, this timeout reveals connections that are too busy. These types of connections are borrowed and run queries for times that are longer that the TTL timeout.
- Connection Harvesting Mechanism – a mechanism that allows UCP to always keep certain number of available connections, preventing the situation of pool’s “All connections are in use” over-use.
- Connection Wait Timeout (CWT) – when an application attempts to borrow a connection out of a pool and there are no available connections at the moment, UCP waits for an available connection to appear that amount of time. By default, CWT is set for 3 seconds. In many applications it makes sense to increase this timeout to allow a pool to wait for longer for an available connection to appear and to get rid of “All connections are in use” exception.
- Maximum Pool Size (MaxPoolSize) – the more the pool capacity, the more the throughput… actually, no. It is recommended to have small pool size, typically a small number multiplied by the number of cores on a database server. It is better to increase the CWT, than making MaxPoolSize higher.
- Inactive Connection Timeout (ICT) in combination with MaxPoolSize –allows UCP to automatically close available connections that didn’t have chance to be borrowed for longer than the ICT. That way the UCP can get rid of connections in a pool working set is too big to perform a given throughput. It is possible to set a MaxPoolSize to a really big value and set an ICT: that way, the pool would auto-tune the required number of connections in pool’s working set.
- Pool Size Auto-Tuner– this is the experimental mechanism that appeared in 19.3 release. It allows to automatically tune up pool size for better throughput. If you want to try this feature, you might want to set the “"oracle.ucp.selftuning" system property to “true”.
- Pool Statistical Metrics – set of stats allowing to figure out what is going on with a pool. Special interest in this context could be the number of available connections, the number of borrowed connections and the average connection wait time (ACWT). ACWT can find a proper value of CWT property for pool tuning. If ACWT is big, it indicates that the UCP is close to its capacity over-use.
- Pool Logging – The UCP has an extremely rich and flexible logging subsystem. Please read an appropriate section in the UCP Developer’s Guide. With logging it is possible to figure out events of connection opening, closure, borrowing, return and borrow request wait time.
Samples in GitHub