Hikaricp: Hikari connection pool grows to maximum size at start

[caiiiycuk]

Hi. I switched to hikari cp from apache dbcp and i confused a little. There is a config option "maximumPoolSize", this option means that connection count will grow until to maximum pool size if needed. This option works as expected in apache dbcp, but in hikari all connections will be established at start. I use scala, and my test case is:
build.sbt

libraryDependencies += "org.postgresql" % "postgresql" % "9.3-1102-jdbc41"

libraryDependencies += "com.zaxxer" % "HikariCP-java6" % "2.3.2"

Test.scala

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

object Test extends App {
  val config = new HikariConfig()
  config.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource")
  config.addDataSourceProperty("serverName", "...")
  config.addDataSourceProperty("databaseName", "...")
  config.addDataSourceProperty("user", "test")
  config.addDataSourceProperty("password", "...")
  config.setMaximumPoolSizebuild(100)
  config.setConnectionTimeout(3000)
  config.setValidationTimeout(1000)

  val dataSource = new HikariDataSource(config)

  while (true) {
    println("sleep")
    Thread.sleep(1000)
  }
}

I run this test case with sbt run. After that i check connection count with command:

ps aux|grep postgre|grep test|wc -l

This command always returns 100, but should returns 0.

[arteam]

Brett believes in the fixed pool design.

At startup, HikariCP fills the pool with a configured maximum of connections and maintain them during the life of the pool. If you are not satisfied with this behaviour, you can set the _minimumIdle_ property,
that will control amount of connections in idle periods. But this configuration is discouraged.

By the way, you rarely need a pool of 100 connections, only if you don't connect to a powerful multicore server with a well-tuned database. Check out Brett's article on the subject.

Maybe Brett will say more or correct me if I'm wrong.

[caiiiycuk]

I understand this. Can you give me advice for configuring database pool. I have three nodes + one backup node of my application. And all of them i configured to use 20 connections in the pool. To maintain 4 nodes i need 20*4 = 60 connections. When all 4 nodes are enabled in production then only 5-6 connection needed (for node), but if some nodes are down for maintenance then count of required connection grows. But most of time all nodes are active and we have 35 idle connection. We use pg_pool which configured to share connections between clients, and this 35 connection can`t be shared to other clients (other apps) because they are stay in Hikari pool.

Only using minimumIdle can solve this problem? Is there any recommended value for this option?

[brettwooldridge]

@caiiiycuk My understanding of pg_pool is that it has it's own maximum connections configuration. What is this currently set to?

Next question is, do you have "spike" demands in traffic? For example, a node normally needs 5-6 connections but sometimes needs 10-15 quickly?

If you don't have spike demands:

If you have 3 active nodes (and 1 backup), and in production each node normally needs 5-6 connections, possibly set maximumPoolSize to 20, minimumIdle to 2, and idleTimeout to something like 2 minutes (120000ms).

If you do have moderate spike demands:

Try maximumPoolSize at 20, minimumIdle at 5-10, and again idleTimeout of something like 2 minutes (120000ms).

[caiiiycuk]

@brettwooldridge
In our current configuration maximum connections in pg_pool is setted to 860 (we have ~15 applications). We have "spike" demands in traffic in rush hours. If we set idleTimeout (to 2 minutes) then connection count will grow on requests or it will fall to actual count after 2 minutes? Can we use "overselling"? Can sum of maximumPoolSize be bigger then max connections in pg_pool?

When client makes request a connection and all connections are busy then does hikari immediately increase pool size if he can? Is there any option that configures time delay before establishing new connection to database?

[brettwooldridge]

@caiiiycuk The only thing idleTimeout helps with is reducing the pool size _after_ a higher demand load.

Imagine that you have a minimumIdle of 2, and a maximumPoolSize of 20. When the pool starts, and there are no client request, the pool will look like this:

Total: 2, Idle: 2, Active: 0

Now, if three requests come in concurrently:

• The 2 idle connections are immediately used
• A new connection is created to handle the third request
• And then 2 additional connections are created to satisfy a minimumIdle of 2 (but this is not _instantaneous_)

The pool would then (possibly) look like this:

Total: 5, Idle: 2, Active: 3

Immediately after the three requests are complete, the pool would like this:

Total: 5, Idle: 5, Active: 0

If idleTimeout is set to 2 minutes, assuming no activity, then after 2 minutes the pool would again look like the initial condition (Total: 2, Idle: 2, Active: 0).

---

Basically, minimumIdle tries to ensure that there are at least minimumIdle connections available in the pool. If minimumIdle is 5, the number of connections in an idle pool will be 5. "As soon as" a request comes in and consumes one of the connections, there is now only 4 idle connections, so the pool will try to add a new connection to bring the idle connection count back up to 5.

The good news is, this is _not_ an instantaneous action. The pool "refill" occurs every 30 seconds or so. So, if there are 5 idle connections and a request comes in and consumes one of them, leaving 4 idle, if the request completes and the connection is returned before the "refill", the pool will again have 5 idle connections and will not grow.

The "bad news" is, _if at the instant_ that the "refill" runs all connections are consumed (0 idle connections), then the refill will add 5 new connections. When the 5 _active_ connections are closed, the pool will then have 10 idle connections. The idleTimeout (or maxLifetime) will close connections, and the pool will eventually return back to 5 idle connections.

If you want the pool to shrink quickly, set idleTimeout to 30 seconds, and maxLifetime to something like 1 minute.

---

Now, getting to "overselling". According to the pgpool documentation:

... pgpool-II also has a limit on the maximum number of connections, but extra connections
will be queued instead of returning an error immediately.

This means you could configure the sum of the HikariCP maximumPoolSize to be higher than the pgpool maximum connection limit. With the understanding that HikariCP will still throw SQLExceptions to a client if getConnection() is called and cannot be satisfied within connectionTimeout. The connectionTimeout applied to getConnection() is decoupled from the creation of new connections, which occurs asynchronously, so even if pgpool connection attempts were timing out the error is not necessarily passed through to HikariCP clients (if Connections are being returned to the pool by other threads within the connectionTimeout period).

Does that make sense?

[caiiiycuk]

@brettwooldridge
Thank you very much for good explanation!
I think i set maximumPoolSize to 20, minimumIdle to 5, and idleTimeout to 2 minutes.

[mmuruganandam]

Sorry to be writing a comment on the closed ticket. Since it is a relevant ticket, i thought of using the same ticket to discuss further on this topic.

@brettwooldridge
I feel that it is still a very complicated topic. Fixed pool might not be a solution for all needs. Here is one of the current issue when we go with the fixed pool is that when the lifetime reaches (20 days. We now moved to 60 days as a temporary workaround), all the connections would be dropped and then recreated before it start serving the application again. We had a couple critical system issues as it took much longer to get the connection as the listeners are busy on a peak load to give out the connections.

What is the behavior when the lifetime have reached for all the connection at the same time (since they were all created nearly at the same time)?

[brettwooldridge]

@mmuruganandam Currently maxLifetime is spread out somewhat with random variance. Currently, this spread is only 10 seconds. Maybe it makes sense to make the spread random over a range that is proportional to the maxLifetime.

For example, maybe if maxLifetime is one minute then a spread of 10 seconds is fine, but if maxLifeTime is one day maybe the spread should be five minutes, etc.

[elopezanaya]

is there any way to refresh my pool of connection after change the privileges of the connection user, without kill the connections ??

[brettwooldridge]

Look at the wiki page about JMX. The API you want is softEvictConnections.

[shiv6146]

@brettwooldridge I have set my maximumConnectionPoolSize to 10. But when I check the number of established connections to mysql, I always find a number which is greater than the max size. For instance in my case it is always 30 more than the max pool size which is set. So, if my max pool size is 10, then the number of established connections to mysql is always 40. Could you please explain this behavior?

[johnou]

@shiv6146 you may want to create a new issue instead of hijacking this one and attach debug logs so we can see what is going on.

[brettwooldridge]

@johnou Agreed. But also @shiv6146 make sure you are counting connections correctly:

mysql> show processlist;
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  3 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist | 
|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             | 
|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             | 
|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+

Run that command remotely, from a different machine than the application, and you should be able to count actual live connections. It is unlikely that HikariCP is holding more than the configured number of connections.

[f0y]

@brettwooldridge Hello Brett! Hikari is awesome, we got up to 30% performance boost compared to dbcp2.
However we have some issue related to fixed pool design.
Imagine we have 2 datacenter with 2 application instances deployed in each dc, so 4 application instances total.
Our requirement is that system should run without degradation even after 3 intances is down due to hardware or network issues.
So on each instance we set hikari max total connections to 20 in order to handle max load.
When we deploy application hikari opens max total connections on all application instances so we got 20*4 connections on postgresql server. However, we use only 20 of them under max load.
The problem is we don't have so much connections on postgresql server and cannot increase it.
Could you provide a boolean switch that will control how many connections to open: minidleCount or maxTotalCount? Is that a bad idea? If so, can you answer why?

[brettwooldridge]

@f0y minimumIdle is what you are looking for. Without minimumIdle set HikariCP acts as a fixed-size pool, which provides better response to varying or spike loads.

[f0y]

@brettwooldridge minimumIdle doesn't help. We set it to 0 and still see activeCount around 1-3 and idleCount around 7-10. I know that fixed-size pool is the best solution, however our PostgreSQL backend cannot handle so much connections produced by several application instances.