OpenLiberty opens a lot of database sessions

[wumacH]

Describe the bug
Since we upgraded OpenLiberty from 25.0.0.2 to 26.0.0.1 we see a strange behaviour around the opened sessions to the datasource.
Before the upgrade OpenLiberty created around 40-50 sessions per day. The duration of a session was nearly around 23h, which is nearly the value we configured (see property agedTimeout in connectionManager). Since the upgrade there are a lot of sessions created (around 1200 - 2300 per day). A session is usually closed after around 35-40 minutes.

Anyone having an idea, what could cause the issue?

Thanks for your help!

Best regards
Markus

Configuration for our datasource:

Diagnostic information:

• OpenLiberty Version: 26.0.0.1
• Java Version: Java 8
• Database: Oracle
• Persistence Provider: Hibernate 5
• Features:
• batch-1.0 ejbLite-3.2 ejbPersistentTimer-3.2 jaxws-2.2 jaxb-2.2 javaMail-1.6 jdbc-4.2 servlet-4.0 appSecurity-3.0 cdi-2.0 jaxrs-2.1 jndi-1.0 jpa-2.2 managedBeans-1.0 restConnector-2.0 localConnector-1.0 adminCenter-1.0 batchManagement-1.0 wsSecurity-1.1 mpOpenAPI-2.0

[KyleAure]

@wumacH
Thanks for opening an issue. I have some clarifying questions below that should help us figure out what has changed:

• Can you verify that you are using Liberty's connection pool and not Oracle's Universal Connection Pool (UCP)?
• Can you please send your connectionManager configuration for the DataSource(s) that are affected?
• What version of Oracle and the Oracle JDBC driver are you using?
• When you upgraded Liberty versions did you update anything else like Database versions or Oracle JDBC Driver versions?
• Where are you getting your metrics on session usage, is it from the Database side or the Connection Pool trace/mbean in Liberty?

[wumacH]

Thanks @KyleAure for your answer.

We do not add any additional jar for UCP and use ojdbc8.jar as jdbcDriver. So i don't think we use UCP.

Our configuration:
<connectionManager id="applicationCM" connectionTimeout="10s" maxPoolSize="100" minPoolSize="10" reapTime="1680s" maxIdleTime="1800s" agedTimeout="82800s" numConnectionsPerThreadLocal="2" purgePolicy="FailingConnectionOnly"/>

We have a second connectionManager for the batch dataSource:
<connectionManager id="batchCM" connectionTimeout="10s" maxPoolSize="8" minPoolSize="4" reapTime="1680s" maxIdleTime="1800s" agedTimeout="82800s" numConnectionsPerThreadLocal="2" purgePolicy="FailingConnectionOnly"/>

JDBC driver name : Oracle JDBC driver
JDBC driver version : 19.7.0.0.0
Oracle Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

We didn't updated any thing else.
The information about the session usage is from the database side. But we see also a change in openliberty metrics delivered to Grafana. The created Connections Total for the connection Pool of the datasource is much higher: Around 5-10x higher per day.

If you need more information, please let me know.
Thanks for your help.

Best regards,
Markus

[KyleAure]

Hey Markus,

Okay from the information you provided it sounds like connections are being closed and re-opened more frequently.

More investigation

• Do the Grafana metrics you collect track the pool size over time, or just the number of connections opened opened over time?
  • If so, has the pool size itself also increased since you upgraded?
  • for example: usually having an average pool size around 20, but now its averaging 40
• Since you have purgePolicy="FailingConnectionOnly" are you seeing any spikes in caught SQLRecoverableExceptions in your application at runtime?
• What OS are you using?

If you have IBM support, please open a case for this issue.
My guess is that to actually debug this we will need to collect trace and narrow down why connections are leaving the pool.

Troubleshooting

If you are using Liberty without support and this is easy to recreate, it would help to narrow down the issue with some troubleshooting steps:

• Do you see the same increased number of sessions if you were to remove numConnectionsPerThreadLocal="2" between the two versions?
• Can you test this on the LTS versions between 25.0.0.2 and 26.0.0.1 to help narrow down when this change occurred? (25.0.0.3, 25.0.0.6, 25.0.0.12)

Thanks. Kyle