I am getting an error as below.
org.apache.commons.dbcp2.LifetimeExceededException: The lifetime of the connection [198,970] milliseconds exceeds the maximum permitted value of [60,000] milliseconds
What exactly I am doing ?
- I am trying to do a load run of 500 query updates and deletes, into MS SQL 2019. Out of this 500, we can consider 50 query as a part of a transaction boundary. Which means only 10 transaction sets are run per second.
Now I am getting a below error in Mule ESB 3.8.0 and I have a connection pool of 256 as min size. If any one can suggest, what could be the reason? Will it be because the query is spending more time in DB ? Will it be a DB query issue? As I am seeing some transaction set (50 queries) spending more than 3 minutes.
I am using Hibernate queries
I am not sure where to kick off the analysis and how to handle this. Can any one suggest?
More Error:
2023-04-13 11:04:41,052 [4616] [WARN] commons.dbcp2.BasicDataSource | An internal object pool swallowed an Exception.
org.apache.commons.dbcp2.LifetimeExceededException: The lifetime of the connection [198,970] milliseconds exceeds the maximum permitted value of [60,000] milliseconds
at org.apache.commons.dbcp2.PoolableConnectionFactory.validateLifetime(PoolableConnectionFactory.java:424) ~[commons-dbcp2-2.1.1.jar:2.1.1]
at org.apache.commons.dbcp2.PoolableConnectionFactory.passivateObject(PoolableConnectionFactory.java:364) ~[commons-dbcp2-2.1.1.jar:2.1.1]
at org.apache.commons.pool2.impl.GenericObjectPool.returnObject(GenericObjectPool.java:578) [commons-pool2-2.4.2.jar:2.4.2]
at org.apache.commons.dbcp2.PoolableConnection.close(PoolableConnection.java:206) [commons-dbcp2-2.1.1.jar:2.1.1]
That's not exactly an error. It is a warning that the connection was closed because it exceeded the maximum time configured in the DBCP2 database connection pool which seems to be of 60 seconds according to the error (or 60000 milliseconds). That's internally handled by the pool. You only need to worry about that if it is causing some issue to the application. Otherwise just ignore it because it is just the expected behavior of a database connection pool. For example if your transactions are taking 3 minutes, which sounds an excessive time for a database operation, you might want to increase the lifetime. But only if transactions are actually failing.
From the Mule side you could try as a basic profiling technique to capture several thread dumps and analyze if many threads are waiting on answers of the database or doing something else. I would expect that they are waiting on the database but there is not enough information. You'll have to learn how to interpret thread dumps and how to relate it to the application on your own.
From the database side a database admin (DBA) can use administrative tools to understand load on the database and identify bottlenecks. For example in some cases lack of appropriate indexes could introduce delays to queries.
As it is the question is too abstract. From a symptom on the application that not necessarily is an issue you are trying to infer a problem. You need to look into the queries and the database and start analyzing the behavior before looking into the pool. That doesn't meant that the problem could not be in the pool, but there are many factors in this kind of problem, and without providing specific details it is usually not possible to point to a single root cause.