Tomcat JDBC connection pool: testOnBorrow vs testWhileIdle

Question

For various reasons, connections in the pool may become invalid: timeout for connecting to the server, network problems ...

My understanding is that the Tomcat JDBC connection pool does not provide any guarantees as to the validity of the connections it provides to the application.

To prevent (in fact, only reduce the risk) getting an invalid connection from the pool, the solution seems to be a connection check configuration. Testing the connection means starting a very simple query in the database (e.g. SELECT 1; in MySQL).

Tomcat JDBC Connection Pool offers several options for checking connection. I find that more interesting are testOnBorrow and testWhileIdle .

At first, I thought that testOnBorrow is the best option, because it basically checks the connection before providing it to the application (with the maximum frequency determined by validationInterval ).

But after a second, although I realized that testing the connection right before using it can affect the responsiveness of the application. Therefore, I, although using testWhileIdle may be more efficient, as it checks the connections until they are used.

No matter which option I choose, it seems that they only reduce the risk of getting an invalid connection, but this risk still exists.

So I ask: should I use testOnBorrow or testWhileIdle or a combination of both?

On the other hand, I am surprised that validationInterval does not apply to testOnReturn , and I really do not understand the purpose of testOnConnect .

Answer 1

There is no 100% correct answer. This is a matter of compromise and context.

• In most cases, testOnBorrow is the least risky because it ensures (as far as possible) that before the connection is returned from the pool for your use, a basic sanity check has been made that the client and the db server are in negotiations.
• It still does not interfere with the race condition of the server’s connection, between the time of the “health check” and the time during which your application has used the connection.

• But considering this as a corner case, testOnBorrow gives pretty good confidence.

• Now the trade-off with this is that every time you request a connection, a request is requested to the database server (regardless of its lightness). It can be very fast, but the cost is still not zero.

And if you have a busy application with very good reliability for connecting to the database, then you will begin to see from the data that the COST of the "validation check on each connection request from the pool" outweighs the benefits of detecting connection problems.

• On the other hand, if your application is not evenly occupied (for example, most real-world applications), then it is extremely useful to have the testOnBorrow option.

• It provides the maximum that you have a good connection before using it. Especially considering the costs (repeat + manual intervention + loss of workflow, etc.) From the "impossibility to recover easily" from a failed database operation.

• Now imagine if you have the testOnIdle parameter. This requires that your connections be idle (depending on the connection idle timeout) before you can verify the health check.

• This is a performance improvement over testOnBorrow , but it has its drawbacks.
  • Real app-to-db applications in the real world are not just a failure based on an idle timeout, they can be discarded based on firewall rules, n / w congestion, a db server undergoing maintenance / correction, etc.
  • Thus, it returns to measuring data about how many connection errors were observed in the data when you did not have any “connection verification”.
• And one thing to keep an eye on this option is when you have your pool that works best with maximum connections and your application works well, and for some reason, if your db server is rebooting or similar. All live connections (from the point of view of the client) will now be mostly errors until idle downtime begins. Thus, your db question (which would be a fireman) is now compounded until connections to applications start again or you restart the application.

And one last point of data is that for some applications, the critical path is not “validation request time” (in more reliable ones). Applications have more problems. And, of course, for some applications this time is very important.

Answer 2

Just to let you know, I just tested this, and you can use the testOnBorrow and testOnIdle .

As mentioned above, I will choose testOnBorrow unambiguously due to the fact that my application is not under a lot of traffic and can afford to check the connection before mastering it.

As stated in the comments, testOnBorrow does not require a validation request. If you decide to keep one, this may be a simple choice:

 jdbc.hive.testOnBorrow=true jdbc.hive.validationQuery=SELECT 1 

If you want to use testWhileIdle , you can use the following:

 jdbc.testWhileIdle=true jdbc.minEvictableIdleTimeMillis=1800000 jdbc.timeBetweenEvictionRunsMillis=1800000` 

Additional information about DCHP: https://commons.apache.org/proper/commons-dbcp/configuration.html