CODEWITHSUNDEEP
postgresqlspring-bootdatasource
Egemen
Egemen

Reputation: 2548

Spring Boot "PSQLException: FATAL: sorry, too many clients already" when running tests

I have a Spring Boot application that provides a REST API to front-ends. I am using jOOQ and Postgresql. I am currently getting this error when executing all integration tests locally (around 1000 tests, this starts happening after executing 700-800 tests):

org.postgresql.util.PSQLException: FATAL: sorry, too many clients already

I tried to limit the max idle and active connections via application.properties, but it seems that these values are somewhat ignored. I simply monitor the open connections when executing the tests with this statement:

SELECT datname, state, query FROM pg_stat_activity;

That's how my application.properties looks like:

spring.datasource.driverClassName = org.postgresql.Driver
spring.datasource.url = jdbc:postgresql://localhost:5432/xxx
spring.datasource.username = xxx
spring.datasource.password = xxx
spring.datasource.initialize = true
spring.datasource.continue-on-error = false
spring.jooq.sql-dialect = POSTGRES
spring.datasource.max-active = 50
spring.datasource.max-idle = 5

That's how I create my data source:

@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource dataSource() {
    return DataSourceBuilder
            .create()
            .build();
}

I see that jOOQ uses the correct data source and the connection is handled by jOOQ properly (acquiring and releasing connection from data source). So the problem shouldn't be on the jOOQ side.

I have max_connections = 200 in my postgresql.conf, so my Spring configuration should be fine. While running tests I see a lot more idle connections in pg_stat_activity than what I specify in my config. Eventually when the tests start failing due to that PSQLException I see around 90-100 idle connections in pg_stat_activity. So this yields two problems:

  1. Why do my tests fail although my local database should allow more connections than what I see in pg_stat_activity?
  2. It seems that the data source configuration in my application.properties is ignored. Any idea why?

Upvotes: 38

Views: 23016

Answers (6)

James Mudd
James Mudd

Reputation: 2373

I think a better solution is to not use Hikari connection pooling in your tests. If you set

spring.datasource.type=org.springframework.jdbc.datasource.SimpleDriverDataSource

In your test application properties, then it should resolve this issue, without the performance penalty of @DirtiesContext

Upvotes: 0

Marco
Marco

Reputation: 491

None of these solutions worked for me. Application contexts are cached between tests to help speed up tests, so annotating functional test classes (i.e. the ones with @SpringBootTest) with @DirtiesContext did the trick for me.

When a test is marked with @DirtiesContext, Spring destroys and recreates the application context before or after the test is executed, depending on the options specified. This ensures that each test starts from a clean application state that is independent of other tests.

Upvotes: 1

Daniel
Daniel

Reputation: 27

I had this problem when integration testing a Spring Boot application and using Testcontainers. I noticed that I had a connection leak which caused this to happen.

IMO the best thing to do is to make sure that all opened connections are also being closed in your code.

Upvotes: 0

Turzo
Turzo

Reputation: 600

You should change the minimum-idle property, instead of maximum-pool-size

spring.datasource.hikari.minimum-idle=5

The default value for maximum-pool-size is 10, and minimum-idle defaults to the same value as max-pool-size. Changing it to a smaller value than max-pool-size did the trick for me.

My hunch is, the application tries to make a lot of connections to the db when executing tests as they run in parallel. These connections, however, are used for very short period and one connection can easily be reused by multiple test instances. By specifying the minimum-idle property to a smaller value than max-pool size, we are telling HikariCP to add additional connections only when the number of idle connections falls below that threshold. This prevents the connection pool from saturating, and consequently, encountering too many clients scenario.

However, HikariCP did recommend to not set this minimum-idle value, in order to maximise performance and responsiveness to spike demands. I faced my issue when trying to run the tests, hence I have changed the property solely for test env.

I've found Hikari's Github Page to be quite helpful. Over there they have listed all these parameters with brief explanations. Do take a look!

Upvotes: 21

Ybri
Ybri

Reputation: 603

I tried several solutions including the one proposed by Egemen but they did not work.

For me the solution was to limit the connection pool size using this configuration in application-test.properties:

spring.datasource.maximumPoolSize=2

Upvotes: 4

Egemen
Egemen

Reputation: 2548

Since there hasn't been a suggested answer I am posting my solution. Short version: decrease the connection pool size in test properties: 

spring.datasource.hikari.maximum-pool-size=2

Longer version: Spring Boot 2 uses HikariCP by default for connection pooling, which has a default value of 10 for connection pool size (as of Jan 2019). While running a lot of ITs the Spring context is created multiple times, which means each context acquires 10 connections from the database. As far as I've observed, tests allocate connections faster than they are released. Therefore, max_connections limit allowed by the database server (which is typically 100 by default) is reached at some point, which leads to that "too many clients" error.

By limiting the connection pool size to 2 in test properties I was able to fix that problem.

Upvotes: 69

Related Questions