Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

JVM Advent Calendar: Connection Pools

DZone 's Guide to

JVM Advent Calendar: Connection Pools

Go check your pool settings!

· Java Zone ·
Free Resource

Let’s talk about connection pools.

The default settings of the most popular connection pools are poor!

For you, it means:

Go review your connection pool settings.

You might have a problem if you rely on default settings. You may have memory leaks and unresponsive application (even if the load is not high at all).

Below, I will show you some of the most important settings and my recommendations on how they really should be configured.

What Is the Connection Pool?

A plain web application that needs to write or read data from the database does it like this:

  1. Open a connection to DB // takes N ms
  2. Read/write data
  3. Close the connection

By the way, in old CGI applications, it was the only possible approach.

This approach is fine in many cases. And you probably don’t need anything more. But it has some disadvantages for highly-performant systems:

  • Step 1 can take some time. Tens or hundreds of milliseconds (it depends, of course).
  • It’s easy to forget Step 3 (close the connection), which causes a connection leak (causing memory leaks and other problems).

A New Hero

That’s why another approach was born: the application may preliminarily open a bunch of connections and hold them open all the time. The bunch of open connections is called connection pool. Then, any operation looks like this:

  1. Take a DB connection from pool // blazingly fast in most cases
  2. read/write data
  3. return the connection to the pool

Seems cool. But new power always means new problems.

… And New Problems

When using a connection pool, we need to solve (at least) the following questions:

  • How many connections we should keep open?
  • How long should they be kept?
  • What if they appear to be broken?
  • What if the application needs more connections than the pool currently has?
  • What if somebody forgets to return connection to pool?

To answer these questions, connection pools have a lot of settings. And their default values are mostly bad. Intrigued? Let me show.

Basic Settings

I will consider the two most popular connection pools in Java world:

The basic parameters, of course, are:

  • Min size (minimum number of connections that should be open at any moment)
  • Initial size (how many connections application opens at the start)
  • Max size (maximum number of connections in a pool)

By the way, these are the only settings that have reasonable defaults. Here, they are:

c3p0 HikariCP
min size 3 10
initial size 3 10
max size 15 10


Let’s continue with more problematic settings.

Critical Settings

Checkout Timeout

How long can application wait until it gets a connection from the pool?

  • c3p0 setting: checkoutTimeout
  • HikariCP setting: connectionTimeout

Default values:

c3p0 HikariCP I recommend
checkoutTimeout 30 s 1 ms

Both default values are just disaster.

As I mentioned, in most cases, getting a connection from the pool is blazingly fast. Except for the case when the pool has no more open connections. Then, the pool needs to acquire a new connection, which takes less than a second, as a rule. But if maxSize is reached, the pool cannot open a new connection and just waits until somebody returns its connection to the pool. But if the application has a connection leak (a bug that prevents connections to be returned), the pool will never get the connection back!

What Happens Next?

In the case of c3p0, we end up with all Threads frozen in the following state:

"qtp1905485420-495 13e09-3211" #495 prio=5 os_prio=0 tid=0x00007f20e078d800 nid=0x10d7 in Object.wait() [0x00007f204bc79000]

   java.lang.Thread.State: WAITING (on object monitor)

at java.lang.Object.wait(Native Method)

at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable()

- locked <0x00000000c3295ef8> (a com.mchange.v2.resourcepool.BasicResourcePool)

at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource()

    …

    at org.hibernate.jpa.internal.QueryImpl.getResultList()

    at domain.funds.FundsRepository.get()

    …


It may seem that the HikariCP default “30 seconds” is a bit better. No, it doesn’t really help in high-performant applications. During those 30 seconds, a lot of new requests may come, and all of them are just frozen. Apparently, the application will get an OutOfMemory error soon. Any waiting just postpones the death of application for a few seconds.

That’s why I recommend setting the checkoutTimeout to the minimal possible value: 1ms. Unfortunately, we cannot set it to 0 because 0 means endless waiting The sooner we fail, the more chances we give working threads to complete their job. And we can clearly inform the user that the application is currently overloaded, and he should try later.

Test Connection on Checkout

Sometimes, connections in the pool may die. The database can close them by its initiative, or a system administrator can just break network cable. That’s why pool should monitor connection aliveness.

The easiest setting to do that is “testConnectionOnCheckout” in c3p0 (I haven’t found a similar setting in HikariCP, it seems to be always enabled).

Default values:

c3p0 HikariCP I recommend
testConnectionOnCheckout false true? true

Definitely, it should be enabled by default!

Otherwise, you will end up with lots of such exceptions in the log:

org.hibernate.TransactionException: Unable to rollback against JDBC Connection
at o.h.r.j.i.AbstractLogicalConnectionImplementor.rollback()
at o.h.r.t.b.j.i.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.rollback(JdbcResourceLocalTransactionCoordinatorImpl.java:294)


If you want to achieve even better performance, you may consider testing connection in the background, not on checkout:

  • testConnectionOnCheckout=false
  • testConnectionOnCheckin=true
  • idleConnectionTestPeriod=10

Preferred Test Query

But how exactly should the pool test connections?

The problem is that it depends on the database.

By default, both pools test connections by executing

  • “connection.isValid()” (in case of JDBC4), or
  • “connection.getMetaData().getTables()” (in case of JDBC3)

It may be slow because getTables() retrieves meta information about all tables each time. A recommended value is something like

  • “SELECT 1” (in case of MySql), or
  • “SELECT 1 FROM DUAL” (in case of Oracle) etc.

By executing this simple and fast query, the pool can check if a connection is still alive.

Max Idle Time

How long can an unused connection stay in the pool?

  • c3p0 setting: maxIdleTime
  • HikariCP setting: idleTimeout

Default values:

c3p0 HikariCP I recommend
maxIdleTimeout 10 minutes 1..10 minutes

It’s not probably a big deal, but every opened connection...

  • Holds some resources inside the database
  • Prevents other systems from getting connections to the same database (every database has some limit of the maximum possible number of connections)

That’s why it’s a good idea to close the unused (idle) connection. I recommend setting this value to non-endless period. Probably several minutes is reasonable.

Min Pool Size

How many connections pools should always have (even if unused)?

  • c3p0 setting: minPoolSize
  • HikariCP setting: minimumIdle

Default values:

c3p0 HikariCP I recommend
maxIdleTimeout 3 max pool size 0…N

For the same reason, it’s probably a good idea to close unused connections. I would set this value to 0 or 1 in most cases. If some user unexpectedly decides to log in to your application at midnight, they will just wait for a few more milliseconds. Not a big deal.

Max Connection Age

How long a connection may live in the pool (no matter if it’s idle or used)?

  • c3p0 setting: maxConnectionAge
  • HikariCP setting: maxLifetime

Default values:

c3p0 HikariCP I recommend
maxIdleTimeout 30 minutes say, 30 minutes

Just in case, it’s probably a good idea to close connections time-to-time. Probably, it helps to avoid some memory leaks.

A quote from the HikariCP documentation:

“We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit.”

Unreturned Connection Timeout

One of the typical problems is a connection leak. Some buggy code took a connection from the pool and didn’t return it. How to detect this problem?

Fortunately, we have a good setting for this case:

  • c3p0 setting: unreturnedConnectionTimeout
  • HikariCP setting: leakDetectionThreshold

Default values:

c3p0 HikariCP I recommend
maxIdleTimeout disabled disabled 5 minutes?

If any buggy code took a connection and didn’t return it during 5 minutes, the pool will forcedly return the connection and write warnings like this:

[C3P0PooledConnectionPoolManager Logging the stack trace by which the overdue resource was checked-out.
java.lang.Exception: DEBUG STACK TRACE: Overdue resource check-out stack trace.
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource()
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1885)
at domain.application.ApplicationReportSender.sendWeeklyReport(ApplicationReportSender.java:63)


It will help you to find the guilty code.

Conclusion

I gave an overview of some connection pool settings. There is more of them. I gave some advice, which seems reasonable from my experience. But your application may have a different load. Users may have different behavior. My advice may seem stupid to you.

No problems. Don’t trust me. But please, also dont trust defaults!

Go check your pool settings!

Topics:
java ,connection pools ,pool ,pools

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}