Connection pool setting guidelines

Good day friends

We have an ERP type of system with about 400 total users, but only about 100 or so are actively logged in at any one time.

The system is
32 core Intel cores.
5 10 TB drives in RAID

Above system runs CentOS 7.5

We are using PostgreSQL 10 and Wildfly 12

So application server and DB server runs on same manner machine non virtualised (on bare metal).

The Hibernate version in Wildfly 12 was upgraded to latest 5.2 version. Using JPA throughout the app with named queries as much as possible.

For the above, what would you recommend the PostgreSQL datasource connection pool setting be set at?

What should the minimum and maximum pool size be?

Should I use prefill?

What would the recommended flush strategy be?

Should fast fail be enabled?

Sincerest thanks in advance for any guidance.

Check out FlexyPool. It allows you to figure out the pool size and monitor all required parameters.

The flush strategy AUTO is ok if you use JPA bootstrap.

What fail fast are you referring to?

Thanks Vlad, I will do so.

With regards to the Fast Fail, this is a boolean selection available when configuring a Datasource in Wildfly.

Not on PC at the moment to provide exact description. Will update this post when I can.

If you’re using a Java EE application server, FlexyPool will provide you metrics for monitoring, but it will not increase the pool size upon getting a connection acquisition timeout because application servers hide the DataSource implementation.

Anyway, the metrics alone can help you reason about the right pool size to use.