1

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
cloudwalker
  • 2,346
  • 1
  • 31
  • 69
  • I'm guessing its the test-while-ide config. Doesn't that mean that you want it to run the "select 1 from dual" while its idle? – DaShaun Jan 22 '18 at 20:19
  • In your application.properties try adding spring.datasource.tomcat.max-idle=10 and see if your datasource.myDataSource.usage > 0.1 still – DaShaun Jan 22 '18 at 20:21
  • @DeShaun, That shouldn't cause the connections to remain active and not available for use though, right? It was my understanding that it should just cause the connections to be evaluated while idle and discarded if the idle time > the eviction threshold. – cloudwalker Jan 22 '18 at 20:31
  • I assume that your usage % will include your "idle" connections. If your max-idle is not set, I don't know that your usage % should be when your test is complete. If you set it at 10 for example, your usage shouldn't be higher than 0.1. If it IS higher that 0.1, THEN you have a connection that is NOT idle and still being used by something. (My caps are psuedo code not yelling) – DaShaun Jan 22 '18 at 20:42
  • 1
    I did not think that idle connections would be reported under the "active" section. So, what is happening is that the "active" connections, or the ones currently in use are not being re-used until they are discarded. So, if I run the test 3 times in a row, the number of connections will double each time, and then by the 4th time that I run the test, it will fail due to there not being any connections left. If they were truly idle, then I would expect that they should be available for use. – cloudwalker Jan 22 '18 at 20:46
  • Can you provide an example of how you are using the connections? Are you using spring data jpa? – DaShaun Jan 22 '18 at 20:50
  • 1
    Yeah, I'm just using Spring JpaRepositories for all of this. I'm not doing any manual connection management. I did notice that if I lower the removeAbandonedTimeout to something like 10 seconds, then the connections drop off a lot more quickly. However, that should just be for connections that weren't automatically closed, so that says to me that for some reasons the connections are not being closed by Spring/Hibernate when it's done with the operation. – cloudwalker Jan 22 '18 at 21:05
  • 1
    Oh man! I turned on the logAbandoned flag, and then I found where someone had recently added a section where it does run a single pure JDBC query and they did not add anything in to close the connection. That's definitely where the connection leak is coming from. – cloudwalker Jan 22 '18 at 21:18

2 Answers2

2

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.

cloudwalker
  • 2,346
  • 1
  • 31
  • 69
1

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.

DaShaun
  • 3,722
  • 2
  • 27
  • 29