Check active connection timeout in Tomcat JDBC Connection Pool

Question

We have a connection to postgres database that is configured with tomcat connection pool. The problem is that when connection becomes active it never goes back to idle.

When I start my microservice it has 0 active connections and 10 idle ones. After one hour of work there are 7 active and 3 idle. After weekend there were 100 active, it reached the limit and service was down.

Is there any way to configure tomcat connection pool to check active connections state and if they are stucked to close them?

score 1 · Answer 1 · answered May 21 '18 at 10:58

1

Looks like your application is leaking connection. By default hibernate c3p0 provide facilities for detecting leaks , there are two parameters to configure :

5 true

After this it will print stack trace for long active connections and close them.

Recommended not to use on high load. If using another pool, search for a similar thing

answered May 21 '18 at 10:58

techagrammer

1,291
8
19

I suppose that the problem is that some http packages are lost inside of our cluster. While other people are look for this I need to add some workaround before this is fixed – Vitalii May 21 '18 at 12:06

score 0 · Answer 2 · answered May 22 '18 at 06:17

As we have http timeouts inside our cluster, it seems that due to this there is a connection leak. I investigated and connection remains always active.

The solution for me was to enable abandoned connections verification.

private DataSource configureDataSource(String url, String user, String password, String driverClassName){
    DataSource ds = DataSourceBuilder.create()
            .url(url)
            .username(user)
            .password(password)
            .driverClassName(driverClassName)
            .build();

    org.apache.tomcat.jdbc.pool.DataSource configuredDataSource = (org.apache.tomcat.jdbc.pool.DataSource) ds;

    // some other configurations here
    // ...

    configuredDataSource.getPoolProperties() 
           .setRemoveAbandonedTimeout(300);
    configuredDataSource.getPoolProperties()
           .setRemoveAbandoned(true);
}

@Bean(name = "qaDataSource")
public JdbcTemplate getQaJdbcTemplate()  {
    DataSource ds = configureDataSource(qaURL, qaUsername, qaPassword ,qaDriverClassName);
    return new JdbcTemplate(ds);
}

RemoveAbandoned and RemoveAbandonedTimeout flags mean that if some connection is in active state more that timeout value it will be closed. If you put this to your code ensure that this timeout is superior that the maximum query execution time for your service.

Check active connection timeout in Tomcat JDBC Connection Pool

2 Answers2