Best practices for Spring Boot connection pool configuration

Question

I am configuring JDBC connection pool for Spring Boot app and Postgres DB to use HikariCP connection pool and trying to find best practices for configuration setup, unfortunately there is not much info on this theme in the web.

I am preparing my own performance testing for different setups but would appreciate any help. The average throughput is around 20 req/sec for application node

I am the most interested in the most optimal values for such properties as:

    minimumIdle: ?
    maximumPoolSize: ?
    idleTimeout: ?
    maxLifetime: ?
    connectionTimeout: ?

Especially would be great to know the most optimal value for the maximumPoolSize There are plenty of other options available for connection pool setup would appreciate any advice about their impact on application performance.

Was

You won't get a definitive answer for this question. Tuning a connection pool is very depending on your use cases (queries), data, concurrent access, ... At the end - it all goes to the throughput of the database. please see some basic tips – Erena

HikariCP has very good documentation and suggestions github.com/brettwooldridge/HikariCP – Cushion

@Cushion Thanks, there is some interesting info in there. – Was

You should add more info as DB oracle/mysql, web container... – Cantrell

@user7294900 Add Postgres DB, thank you – Was

Answer 1

@aliaksei-stadnik 20 req/sec is, in the grand scheme of things, pretty low. So, I wouldn't be overly concerned about pool tuning; more important is to focus on query performance. The lower your query times, the more requests you can handle with a smaller number of connections.

We always recommend running HikariCP as a fixed-size pool for best performance (leaving minimumIdle and idleTimeout unset). The maximumPoolSize is probably the key number you need to tune, and as the referenced link above says, it depends primarily on the number of CPU cores that your database server has.

With an average query time of 2ms, even a single connection could handle ~500 req/sec, and an average query time of 10ms would yield ~100 req/sec per connection. However, at the cost of a single request possibly waiting up to one second to be serviced. Additional connections would, in that case, serve to reduce the queueing time of requests.

Letter

The thing is that I have pretty complicated query being executed, which took around 500-600ms, so with low number of connections in the pool (default 10) requests are waiting for connection around 7-8 sec. On the other hand with large number of connections DB CPU got wracked. Thats basically why I am trying to find the best solution. – Was