Max connection is weirdly blown up while lots of idle connections

Question

I've checked the max available connection should be 97 since my max_connection is 100 and superuser_reserved_connections is 3 by default.

I am using hibernate-envers 5.2.3.Final and Spring Boot 1.5.2.RELEASE to manage the database operations.

I am using @Autowired to wire the repository and pass it into hundreds of threads that are going to search from & save to database.

I was prompted by connection exception but when I check the connections via pgAdmin4

Questions

1. Why the connection is so high while idle taking up almost the whole connections?
2. Is each operation in Hibernate, I will create a connection? Any official doc to elaborate this design?
3. Is there some way in Hibernate I can free the idles?
4. Or actually there is another place I shall look into instead of Hibernate?

Updated

I resolved the issue by caching up the data in java code to avoid frequent searching & saving, but the questions above still there.

Any help will be appreciated ;)

Answer 1

When using JPA/Hibernate, you have to set up a DataSource, which is often an implementation that uses a connection pool so that it doesn't have to recreate connections the whole time. Instead, an existing connection from the connection pool will be taken.

There are several implementations available and supported by Spring, such as HikariCP, DBCP, Tomcat and so on. By default, Spring boot 1.x uses the Tomcat connection pool, while Spring boot 2.x uses HikariCP.

You can configure the minimum amount of idle connections by setting the minimumIdle property. By default this is the same as maximumPoolSize, which in turn defaults to 10. This means that Hikari by default will attempt to keep 10 connections, either idle or not.

Within Spring boot, you can configure the HikariCP properties by using the spring.datasource.hikari.* prefix, for example:

spring.datasource.hikari.minimumIdle=10
spring.datasource.hikari.maximumPoolSize=10

Quoting the relevant documentation:

This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool. Default: same as maximumPoolSize

As you can see, it's recommended to not change this behaviour, and thus, it's pretty normal if you use HikariCP to see a lot of idle connections if your applications aren't in use. However, each application should take 10 connections at most, unless configured otherwise. They also recommend to keep the maximum pool size as low as possible, as mentioned in the About Pool Sizing article.

Additionally, you can configure how long a connection is kept when idling, by configuring the idleTimeout setting.

Answer 2

With the help of @g00glen00b, I checked the Spring Boot dependencies and I found that Tomcat 8.5.11 is actually used by Spring Boot.

There are attributes maxIdle & minEvictableIdleTimeMillis used by tomcat to control how many idled connections is allowed & how long they can stay before being evicted. Its default is maxActive: 100 which directly explained everything.

And in Spring Boot, you can manage this attribute as spring.datasource.tomcat.max-idle=50.

But as @g00glen00b mentioned, perhaps it's not good to manage this value directly which might damage the performance. It should be used with great caution with clear intention based on the system itself.

In my case, I didn't touch them, I refactored my java code to cache the data to reduce the connections in java side.