Tomcat JDBC连接池:testOnBorrow与testWhileIdle [英] Tomcat JDBC Connection Pool: testOnBorrow vs testWhileIdle

问题描述

由于各种原因，池中的连接可能变得无效:服务器连接超时，网络问题...

我的理解是，Tomcat JDBC连接池不提供任何关于它与应用程序的连接有效性的保证.

为防止(实际上只是降低风险)从池中获得无效的连接，一种解决方案似乎是配置连接验证.验证连接意味着要在数据库上运行非常基本的查询(例如，MySQL上的 SELECT 1; ).

Tomcat JDBC连接池提供了多个选项来测试连接.我发现更有趣的两个是 testOnBorrow 和 testWhileIdle .

首先，我认为 testOnBorrow 是最好的选择，因为它基本上在将连接提供给应用程序之前对其进行了验证(最大频率由 validationInterval 定义)./p>

但是一秒钟之后，我意识到在使用连接之前先进行测试可能会影响应用程序的响应速度.因此，尽管使用 testWhileIdle 可以更有效，因为它可以在不使用连接时对其进行测试.

无论我选择哪个选项，它们似乎只会降低获得无效连接的风险，但这种风险仍然存在.

所以我最终问:我应该使用 testOnBorrow 还是 testWhileIdle 还是两者混合使用?

另一方面，令我感到惊讶的是 validationInterval 不适用于 testOnReturn ，而且我并没有真正达到 testOnConnect 的目的>.

解决方案

对此没有100％正确的答案.这是权衡和背景问题.

• 在大多数情况下， testOnBorrow 风险最小，因为它确保(尽最大可能)在从池中返回连接供您使用之前，已进行了基本的健全性检查客户端和数据库服务器处于通话状态.
• 在进行健全性检查"和测试之间，仍然不能防止服务器连接的竞争状况下降.您的应用程序使用连接的时间.

• 但是考虑到这是一个极端情况， testOnBorrow 提供了很好的保证.

• 现在要进行的权衡是，每次请求连接时，都会对数据库服务器进行查询(无论轻量).这可能非常快，但是成本仍然不为零.

如果您有一个忙碌的应用程序，并且具有很好的数据库连接可靠性，那么您将开始从数据中看到对池中的每个连接请求进行有效性检查"的成本超过了检测连接问题.

• 另一方面，如果您的应用程序不像其他大多数现实应用程序那样繁忙，那么使用 testOnBorrow 选项将非常有益.

• 最大程度地确保使用前您连接良好.尤其要考虑因数据库操作失败而造成的无法轻松恢复"的成本(重试+手动干预+工作流程丢失等).

• 现在想象一下您是否具有 testOnIdle 选项.这要求您的连接处于空闲状态(取决于连接的空闲超时)，然后才能进行健全性检查.

• 这是对 testOnBorrow 的一项性能改进，但它也有其自身的缺点.
  • 现实世界中的应用程序与数据库的连接不仅是基于空闲超时的破坏，还可以根据防火墙规则，n/w拥塞，正在进行维护/修补的db-server来删除它们.
  • 因此，当您没有任何类型的连接验证"时，就可以回到数据测量中，观察到数据中有多少连接错误.
• 使用此选项要注意的一件事是，您的池在最大连接数下运行得最好，并且应用程序运行良好，并且由于某种原因(如果您的数据库服务器经历了重新启动或类似的操作).现在，所有活动连接(从客户端的角度来看)几乎都会出错，直到空闲超时开始.因此，您的数据库问题(本来是一场战斗)现在变得有些复杂，直到应用程序连接再次恢复正常或您也重新启动应用程序为止.

最后一个数据点是，对于某些应用程序，关键路径不是验证查询"时间(希望以较低的毫秒为单位).这些应用程序有更大的问题要处理.当然，对于某些应用程序来说，时间非常重要.

For various reasons connections in a pool can become invalid: server connection timeout, network issues...

My understanding is that a Tomcat JDBC Connection Pool does not provide any guaranty about the validity of the connections it provides to the application.

To prevent (actually only lower the risk) getting an invalid connection from the pool a solution seems to be the configuration of connections validation. Validating a connection means to run a very basic query on the database (e.g. SELECT 1; on MySQL).

Tomcat JDBC Connection Pool offers several options to test the connection. The two I find the more interesting are testOnBorrow and testWhileIdle.

First I was thinking that testOnBorrow is the best option because it basically validate the connection before providing it to the application (with a max frequency defined by validationInterval).

But after a second though I realized that testing the connection right before using it might impact the responsiveness of the application. So I though that using testWhileIdle can be more efficient as it test connections while they are not used.

No matter which option I choose it seems that they only lower the risk from getting an invalid connection but this risk still exist.

So I end up asking: should I use testOnBorrow or testWhileIdle or a mix of both?

On a side note, I'm surprised that validationInterval does not apply to testOnReturn and I don't really get the purpose of testOnConnect.

解决方案

There is no 100% right answer to this. It is a matter of trade-off and context.

• Most of the times, testOnBorrow is the least risky since it ensures (as best it can) that before a connection is returned from the pool for your use, a basic sanity check has been made that the client and db-server are on talking terms.
• It still doesn't prevent the race condition of the server connection going down, between the time 'the sanity check' was made & the time your application used the connection.

• But considering this as a corner-case, the testOnBorrow gives pretty good assurance.

• Now the trade-off with that is that, every time you request a connection, a query (no matter how light-weight) is made to the database-server. This maybe very fast, but the cost is still not zero.

And if you have a busy application, with very good database-connection-reliability, then you'll start seeing from the data, that the COST of "validity check on every connection-request from the pool" outweighs the benefits of detecting connection issues.

• On the other hand, if your application is not uniformly busy (like most real-world applications) then it is extremely beneficial to have the testOnBorrow option.

• It ensures to the max, that you have a good connection before you use it. Especially considering the cost (retry + manual intervention + loss of workflow etc.) of "not being able to recover easily" from a failed DB operation.

• Now imagine if you have the testOnIdle option. This requires that your connections go idle (dependent on the idle timeout of the connection) before a sanity check can be made.

• This is a performance improvement over testOnBorrow but it comes with its own disadvantages.
  • Real world app-to-db-connections are not just idle-timeout-based breakage, they can be dropped based on firewall rules, n/w congestion, db-server-undergoing maintenance/patching etc.
  • So it goes back to the data-measurement of how many connection errors were observed in the data, when you did not have any sort of "connection validation".
• And one thing to watch out with this option is when you have your pool working the best with max connections and your app performing well, and for some reason if your db-server undergoes a restart or likewise. All the live connections (from a client perspective) will now mostly error out, till the idle timeout kicks-off. So your db-issue (which would have been a fire-fight) is now compounded a bit, till the app connections get well again or you restart the app as well.

And one last data point is that for some applications, the critical-path is not the "validation query" time (in lower millis hopefully). The applications have bigger issues to deal with. And of course, for some applications, that time is very significant.

这篇关于Tomcat JDBC连接池:testOnBorrow与testWhileIdle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！