Search code examples
postgresqlspring-bootconnection-leaks

jdbcTemplate connection leak to postgresql db in Spring Boot

It seems that my service has a database connection leak. Yesterday after redeployment I mentioned that there were 27 opened connections to postgres database. This morning they are 60.

Executing this query to my database I see that the last usage time of some connections was only yesterday.

SELECT * FROM pg_stat_activity
ORDER BY state_change DESC

enter image description here

It seems that my jdbcTemplate should close them but it did not do it.

Here is my configuration

@Configuration
public class DatabaseConfiguration {
    // reading data from application properties
    // ........

    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;
        configuredDataSource.setTestWhileIdle(connectionTestWhileIdle);
        configuredDataSource.setValidationQuery( connectionValidationQuery);
        configuredDataSource.setTimeBetweenEvictionRunsMillis( 
              toIntExact(connectionTimeBetweenEvictionRunsMillis));

        return configuredDataSource;
    }

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

Any ideas what's wrong with my configuration? Or maybe this is the database configuration that's wrong.

Solution

  • After hours of investigation, it seems that this connection leak is caused by http request timeouts and other network issues inside our cluster.

    If request to postgres database form my service is sent and some network issue happened, connection remains active. After some time, the number of maximum active connections is reached and service cannot connect to database anymore.

    As a workaround, while the source of network problems is not found, to avoid service down after some time of work I configured RemoveAbandoned verification in my configureDataSource method

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

    This will check than connection in active state does not exceed 5 minutes, if it does connection will be considered as abandoned and will be closed. Don't forget to ensure that RemoveAbandonedTimeout should be more that the longest execution time of any sql query in your service.