Hikaricp: Add a table listing the configuration fields (summary) suggesting range values

[manueljordan]

Just a friendly suggestion and request about documentation from the source for this excellent software.
Could be added a special section of documentation about best settings about configuration?

Because you are the creator of this software I am assuming you have some formulas or math rules to apply special settings according some ranges or scenario, it for production or for yourself to apply your own tests . Of course it for each possible 'field' that expects a number value. Thus what could be the best or recomended value for minimumIdle according some N scenario, perhaps number of possible connections in production or anything else, same thought for maximumPoolSize for example it has a page to About Pool Sizing. Thus a table listing all these fields and suggested range values would be nice.

Thus when a developer try to configure Hikari for a first time, he has already available a suggested range valid range of possible values according other factors.

I know is very relative because even hardware (memory ram) and other factors are involved.

Note: I am not saying the current documentation is useless.

[billoneil]

As you stated there are many factors to consider here which would make a generic formula next to impossible.

• Hardware of the database
• Do you have read replicas
• How is the database configured
• Is the load read / write heavy
• How many app servers do you have connected to a single database
• What is the hardware of the app servers
• What is the expected query throughput
• How long does a query take to run on average
• Do you have a mix of fast running queries and long running queries (OLAP / OLTP)
• Is there anything sitting between your app server and the database (Timeouts need to be configured based on that as well)
• How fast do you need to fail over

You should monitor the pool usage and consider using multiple pools if you have various types of queries OLAP vs OLTP.

If you are low scale you will probably not gain a whole lot from tweaking pool settings. Other than that you really have to test it yourself. Every application is different.

[manueljordan]

Yes, I understand and are valid your points.

But the general point is that should exists something (a 'pattern') to apply the following quickly:

• How edit and tune the settings prior to release the software to production, it for Hikari of course

I know it is very hard, because it depends hardware, db configuration etc... but I think should exists a 'reach point' in someway.

Testing could help in some way. I mean, how create 'fake' user connections to apply stress testing to all the environment about persistence in Java, and here working or playing around with the Hikari settings

I hope you see my point.

[billoneil]

how create 'fake' user connections to apply stress testing to all the environment about persistence in Java

This is a very difficult problem but the answer is make it as similar to production as possible.

Playing around with the settings should be pretty straight forward. Just measure and test.

That sounds like a job for a performance / load testing framework which still requires intimate application knowledge to be useful. What is the distribution of different queries that are run, how many run concurrently, what is the usage pattern?

I believe all the recommendations are listed.

• If you have a very high throughput app don't use minimum idle at all and let the pool always stay at the max size.
• Connection lifetimes should be slightly shorter than the minimum connection timeout of anything between your application and the database.
• The about pool sizing lists an approximate formula for number of connections with the caveat that if you have a mixed use pool (short and long running connections) you probably want multiple pools configured individually for their use case.

All of the above apply to any JDBC connection pool you use.

[manueljordan]

Thanks for the points.

BTW about:

If you have a very high throughput app don't use minimum idle at all and let the pool always stay at the max size.

I had this experience:

• Improve documentation about: HikariConfig.setMaximumPoolSize best value #920

Thus not always the obvious is the correct.

I am assuming that the author (and team) have a table with values about these settings for his own testing prior to release a new version of HikariCP

[brettwooldridge]

@manueljordan @billoneil Bill makes a lot of valid points. Still, there may be something we can come up with.

But in general I will say this, for the vast majority of "typical" internal IT applications, with a few hundred active users, just run with the defaults and investigate/tune if you have issues

If you are part of a large organization, like Wix, with hundreds of front-end web servers and tens and tens of thousands of users, then I assume there is already a performance team who is capable, and armed with all of the tools necessary, to profile and tune HikariCP.

[manueljordan]

Still, there may be 'something' we can come up with.

Sounds great, some extra information is valuable for the audience

Thanks to both.