CODEWITHSUNDEEP
springoracle-databasetomcatspring-ormtomcat-dbcp
cloudwalker
cloudwalker

Reputation: 2456

Spring Boot with Tomcat DB Pool and Oracle, not retuning connections to pool in timely manner

I'm using Spring Boot with Oracle and the Tomcat DB Pool. I am running into issues where it is taking a very long time for connections to be returned to the pool. I have a max-active of 500, which is pretty large, just for testing purposes, and I'm running some load tests using JMeter. After the test executes, I look at the datasource pool metrics, and it looks something like this:

  "datasource.myDataSource.active": 120,
  "datasource.myDataSource.usage": 0.24

At this point, there is no more database activity happening. However, if I check back about 5 minutes later, I still have the exact same amount of active connections, so if I run another test, that number of active connections will double. If I leave it for maybe 10-15 minutes or so, eventually the connections are returned to the pool. However, this means that if there are many people using the application at one time, we run out of connections very quickly, and since they are not returned to the pool for a very long time, we wind up timing out.

This is my datasource configuration:

 @Bean(name = "myDataSource")
    @ConfigurationProperties(prefix = "datasource.myDataSource")
    public DataSource userStoreDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "myDataSourceEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean myDataSourceEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("myDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.mypackage..model.entity")
                .persistenceUnit("someStuff")
                .build();
    }

    @Bean(name = "myDataSourceTransactionManager")
    public PlatformTransactionManager myDataSourceTransactionManager(
            @Qualifier("myDataSourceEntityManagerFactory") EntityManagerFactory
                    myDataSourceManagerFactory) {
        return new JpaTransactionManager(myDataSourceEntityManagerFactory);
    }

'datasource.myDataSource.driver-class-name','oracle.jdbc.OracleDriver'
'datasource.myDataSource.maxActive','500'
'datasource.myDataSource.removeAbandoned','true'
'datasource.myDataSource.removeAbandonedTimeout','500'
'datasource.myDataSource.test-on-borrow','true'
'datasource.myDataSource.test-while-idle','true'
'datasource.myDataSource.validation-query','SELECT 1 FROM DUAL'

Am I missing some key configuration piece? I would've thought that the connections should be returned as soon as Spring is finished with them.

Upvotes: 1

Views: 955

Answers (2)

cloudwalker
cloudwalker

Reputation: 2456

I used a combination of a small removeAbandonedTimeout value and a logAbandoned = true to debug this issue and found that it was coming from some code that someone had recently added that was running a pure JDBC query and not closing the connection. The logAbandoned showed me the stacktrace, which led me to the offending code.

Upvotes: 2

DaShaun
DaShaun

Reputation: 3880

For the next person that runs into the same issue:

Adjust the removeAbandonedTimeout to something very small (@cloudwalker used 10s) and see if the behavior improves.

Upvotes: 1

Related Questions