Why is a fixed size pool better?

[Gustavo Almeida]

Imagine this scenario:

idleTimeout: 10 minutes

minimumIdle: 1

maximumPoolSize: 10

how I think things go:

under low load:

1 connection is created and used and returned. Maybe 2.

at peak start:

9 more connections will be created in a short time (bad).

The 10 connections will be in use, back to the pool, in use, back to the pool... all the time.

They will have no time to reach the "idleTimeout".

the peak ends, back to low load:

1 connection will be used and returned from time to time.

The 9 other will have chance to be discarded.

After a time, there will be only 1 or 2 connections as in the beginning.

What I don't get is, why is this worse than setting

minimumIdle: default=maximumPoolSize=10

as recommended by HikariCP?

I mean, the only bad thing I can see in the first scenario is that in the beginning of the peak, many connections need to be created in a sudden.

But the second scenario seems worse to me because I will have always all connections open even when I don't need them.

I can rewrite my question as:

Under heavy load, what is the real difference between a fixed size pool and a dynamic sized poll? Won't both use the same connections already opened?

---

Thanks for your time.

[Brett Wooldridge]

The "only bad thing" is pretty much the major bad thing.  If you want an application or website that is capable of handling unexpected bursts (peaks) without stumbling, you pretty much have to avoid creating connections on-demand.  Trying to spool up new connections when a peak is occuring is almost guaranteed to make things worse.

Under constant heavy load, there is no difference between the two pool configurations mentioned.

Having all connections open is only a problem if you are running an over-committed database, for example several applications sharing the same DB, where you are gambling on the fact that peaks do not occur across several applications simultaneously.  Don't get me wrong, there are definite use cases for that kind of setup, particularly internal business applications where peak demands are rarely high.  

For example, imagine a database that physically reaches peak performance at 10 active queries, after which performance starts to degrade.  If you have 3 applications sharing the DB server that are mostly idle but experience peaks individually, it probably makes sense to have them configured with a minimumIdle rather than having 3 pools of a constant 10 connections.  30 constant connections to the DB may (or may not) have a detrimental impact.  However, if you have one application only, there is no reason not to configure the application to hold a constant pool of connections, because the database is clearly capable of handling 10 connections reliably.

That said, for many databases idle connections have little memory or CPU impact on the server, in which case even 3 applications with constant pools might make sense.  The "risk" of sharing a database that peaks at 10 queries with 3 applications is that a "perfect storm" is possible in which all applications experience peak demand at the same time, which demands that the DB execute 30 simultaneous queries.  This is true whether the pool is constant or dynamic.  However, at least in the constant case there is not the additional load of creating connections at the time that the demand occurs.

Does that make sense?

-Brett

[Andrés Salcedo]

This along some profiling gave us insights about the pool's behavior. Also, helped me on pushing HikariCP into our product (elbows used to make room for it despite other devs' thoughts).
I've seen how a fixed pool provides a predictable response time (which is really important for us), while a dynamic one can get connTimeouts depending on DB server response, its load and the network latency. There's one last caveat to be solved before releasing the HikariCP pools to the world:

Does it make sense to set a larger maxLifetime (3600000, doubling the default value) to reduce the overhead caused by session recreation? What would be the ultimate goal of setting a shorter maxLifetime given the fact that transactions are properly ended every time?

The only time I found necesary to set this up was in a pool used to perform operations using an Oracle DBLink, which uses a two-phase commit and requires an explicit end/commit/rollback to close the current transaction, so the connection requires a low idleTimeout and a minimunIdle of zero to ensure no process is left zombie on the pool or the DB (seen Oracle DB processes exhausted because of this). Other than this, why would we want to recreate DB sessions/pool connections every 30 minutes?

P.S. Loving HikariCP! Got 99 problems but poolin' ain't one.

[Brett Wooldridge]

The default maxLifetime of 30 minutes is "safe", but not necessarily optimal.  At the same time, the cost of creating, for example, 10 or 20 connections in a 30 minute period is not very high.

HikariCP will add random variation to the maxLifetime to avoid "mass connection retirement" followed by "mass connection creation".  Over time, whatever the maxLifetime interval is, connection retirements will become evenly distributed over that interval, which makes the cost nearly transparent.

As to why connections are retired at 30 minutes, there are many organizations that place load-balancers and/or stateful firewalls between the applications and the database.  Often these have hard limits on connection lifetimes, and will actively terminate sessions that exceed the internal time-outs.  Thirty minutes is actually quite common.

In other cases, Oracle is one example, the database itself will terminate idle connections after a configured timeout.  Other databases have their own max. lifetime settings.  In the case of MySQL, the default max. session lifetime is (I believe) 8 hours.  At 8 hours the DB will terminate the connection, whether it has been idle or not.

You can, and probably should, tune the maxLifetime to your environment.  Understand the infrastructure between your application and the database to know if those impose lower-bound limits.  The important thing for reliability and smooth operation is to set the HikariCP maxLifetime to several minutes shorter than the shortest time limit in the chain.  If there are no limits imposed by the infrastructure, but MySQL is configured with an 8 hour limit, then set HikariCP maxLifetime to something like 7 hours and 50 minutes.

-Brett