tomcat 7.0.42 pooling，hibernate 4.2，mysql坚实的autoreconnect解决方案 [英] tomcat 7.0.42 pooling, hibernate 4.2, mysql rock solid autoreconnect solution

问题描述

我读过很多关于自动重新连接到hibernate session的问题的文章。其他人提到增加了mysql的wait_timeout（不是我最喜欢的），使用autoReconnect = true（不推荐），测试连接e.t.c.我目前正在尝试一些选项，但我想问问是否有人使用tomcat的连接池（而不是休眠的c3po）的坚实解决方案。即使它们不是最佳的性能调整，我仍在寻找最具弹性的jndi设置。

非常感谢，

Regards

解决方案

非常好的问题。我用这个问题来解决这个问题。对于几乎所有问题，stackoverflow上最常见的答案是它取决于......。我讨厌这么说，但没有比调整连接池更重要的地方。它确实是一种供求博弈，其中连接请求是需求，供应是MySQL可用的连接数量。这主要取决于您的主要担心是否阻止从池中返回陈旧的连接，或者您的担心是否确保MySQL不会因空闲连接而过载，因为您不会以足够快的速度查杀它们。大部分人在中间的某些地方都是碱液。

如果你真的明白为什么有人会选择任何一个连接池配置，那么相信我你会停止搜索Rocket Solid设置，因为你会知道这就像使用Google搜索为您的商店制定商业计划;这完全取决于您获得多少连接请求以及您愿意提供多少个持续连接。下面我举例说明你为什么要使用某些设置。我引用的变量必须在Context.xml文件的Context标记的Resource标记内进行更改。一个完整的配置示例可以在底部看到。

低流量

在这种情况下，您对应用程序的请求很少，所以很有可能连接池中的所有连接都将失效，并且应用程序通过陈旧连接请求的第一个请求将导致错误。 （根据您使用错误的MySQL驱动程序，可能会解释上次收到的成功数据包超出了数据库的wait_timeout设置）。所以你的连接池策略是防止返回一个死连接。对于低流量网站，以下两个选项几乎没有副作用。

> - 您可以通过在MySQL配置中更改 wait_timeout 的值来实现。在MYSQL工作台中，您可以在Admnin> Configuration file> Networking下轻松找到该设置。对于流量很大的站点，通常不推荐这样做，因为它可能会导致池总是充满
空闲连接。但请记住，这是低流量的
方案。

测试每个连接 - 您可以通过设置 testOnBorrow = true 和 validationQuery =SELECT 1。性能如何？在这种情况下你的流量很低。测试从池返回的每个连接都不是问题。所有这一切意味着您将在单个连接上执行的每个MySQL事务中添加一个额外的查询。在低流量的网站上，这真的是你会担心的事情吗？您的连接因池未被使用而死亡的问题是您的主要焦点。

中等流量

• 定期检查所有连接 - 如果您不想在每次连接使用时测试每个连接，或者延长等待超时时间
  ，则可以定期使用您选择的默认或
  自定义查询来测试所有连接。例如，设置 validationQuery =SELECT 1， testWhileIdle =true和 timeBetweenEvictionRunsMillis =3600或任何你想要的时间间隔。对于非常低的流量，这是
绝对需要更多的工作。想想看。如果您在池中有30
连接，并且在1小时内只有4个连接被调用，那么您可以使用前面的 testOnBorrow 轻松地检查每个请求上的所有4个连接。几乎没有性能问题。但是如果你做了每小时检查一次的方法，那么当你只使用
4时，你会发出30个请求来检查所有连接。



高流量

• 很快关闭闲置连接 - 这是所有人都表示不应该延长wait_timeout并且您
  不应该测试每个连接的情况。这不是每种情况下的模式理想。当你有很大的流量
  时，池中的每个连接都会被利用，你的实际问题
  会增加可用连接的数量，而
  实际上缩短了你的 wait_time ，所以你不会在数据库上结束
  空闲连接。下面是一个讨论他如何每天为一个繁忙的站点多达10000个空闲连接的例子，所以他想要降低wait_timeout 降低繁忙站点的wait_timeout
  

示例Context.xml配置

 < Context> 
 
< Resource name =jdbc / TestDB
 auth =Container
 type =javax.sql.DataSource
 factory =org.apache .tomcat.jdbc.pool.DataSourceFactory
 testWhileIdle =true
 testOnBorrow =true
 testOnReturn =false
 validationQuery =SELECT 1
 validationInterval =30000
 timeBetweenEvictionRunsMillis =30000
 maxActive =100
 minIdle =10
 maxWait =10000
 initialSize =10 
 removeAbandonedTimeout =60
 removeAbandoned =true
 logAbandoned =true
 minEvictableIdleTimeMillis =30000
 jmxEnabled =true
 jdbcInterceptors =org.apache.tomcat.jdbc.pool.interceptor.ConnectionState; 
 org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer
 username =root
 password =密码
 driverClassName =com.mysql.jdbc.Driver
 url =jdbc：mysql：// localhost：3306 / mysql/> 
< / Context> 
  

示例web.xml配置

 < web-app xmlns =http://java.sun.com/xml/ns/j2ee
 xmlns：xsi =http： //www.w3.org/2001/XMLSchema-instance
 xsi：schemaLocation =http://java.sun.com/xml/ns/j2ee 
 http：//java.sun。 com / xml / ns / j2ee / web-app_2_4.xsd
 version =2.4> 
< description> MySQL测试应用程序< / description> 
< resource-ref> 
< description> DB Connection< / description> 
< res-ref-name> jdbc / TestDB< / res-ref-name> 
< res-type> javax.sql.DataSource< / res-type> 
< res-auth>容器< / res-auth> 
< / resource-ref> 
< / web-app> 
  

Tomcat Pool属性调整文档 Tomcat Pool

i've read a lot of posts regarding problems with auto reconnecting to mysql from hibernate session. Others mention an increase of mysql wait_timeout (not my favorite), using autoReconnect=true (not recommended), testing connection e.t.c. I am currently trying a few options but i would like to ask if anyone has a rock solid solution using tomcat's connection pooling (not hibernate's c3po). I am looking at the most bullet proof jndi settings even if they are not the best performance tuned.

Thank you very much,

Regards

解决方案

Excellent question. I use to struggle with this question. The most common answer on stackoverflow is "It depends...." for virtually every problem. I hate to say it but no where is that more relevant than tweaking your connection pool. It really is a game of supply and demand, where your connection requests are the demand and the supply is the number of connections MySQL has available. It really comes down to whether your primary concern is preventing stale connections from being returned from the pool, or whether your concern is ensuring MySQL is not being overloaded with idle connections because your not killing them fast enough. Most people lye in the middle some where.

If you really understand why someone would choose any one connection pool configuration then believe me you will stop searching for the "Rocket Solid" setting because you will know that is like googling for a business plan to your shop; It's entirely rooted in how many connection requests you get and how many persistent connections you are willing to make available. Below I give examples of why you would use certain settings. I reference variables that you will have to change inside the "Resource" tag of the "Context" tag of your Context.xml file. A sample full configuration can be seen at the very bottom.

Low Traffic

In this situation you have few requests to your application so there is a good chance ALL connections in your connection pool will go stale and the first request by your application by a stale connection will cause an error. (Depending on the MySQL driver you are using the error may explain the last successful packet received exceeded the database's wait_timeout setting). So your connection pool strategy is to prevent a dead connection from being returned. The following two options have little side effect for a low traffic site.

• Wait Longer Before Killing Connections - You would do this by changing the value of wait_timeout in your MySQL configuration. In MYSQL workbench you can find that setting easily under Admnin > Configuration file > Networking. For a site with lots of traffic this is not often recommended because it may lead to the pool always being filled with lots of idle connections. But remember this is the low traffic scenario.

• Test Every Connection - You can do this by setting testOnBorrow = true and validationQuery= "SELECT 1". What about performance? You have low traffic in this situation. Testing every connection returned from the pool is not an issue. All it means is that an additional query will be added to every MySQL transaction you are performing on a single connection. On a low traffic site is this really something you will be worrying about? The problem of your connections going dead in the pool because they are not being used is your primary focus.

Medium Traffic

• Check All Connections Periodically -If you don't want to test every connection every time it is used ,or extend the wait timeout, then you can periodically test all connections with a default or custom query of your choosing. For example set validationQuery = "SELECT 1", testWhileIdle = "true", and timeBetweenEvictionRunsMillis = "3600" or whatever interval you want. For very low traffic this is absolutely going to require more work. Think about it. If you have 30 connections in the pool and in 1 hour only 4 get called, then you could have easily checked all 4 connections on each request using the previous testOnBorrow approach with little performance hit. But if instead you do the "Check all every hour" approach then you make 30 requests to check all connections when only 4 were used.

High Traffic

• Kill Idle Connections Sooner - This is the situation that has everyone saying you shouldn't extend the wait_timeout and you shouldn't test every connection. It's not a model ideal for every situation. When you have significant traffic every connection in the pool will be utilized and your actual problem will become increasing the number of available connections while actually shortening the length of your wait_time so you don't end up lots of idle connections on the DB. Here is an example of a chap talking about how he has up to 10,000 idle connections a day for a busy site so he wants to lower the wait_timeout Lowering the wait_timeout for busy site

Sample Context.xml Configuration

<Context>   

<Resource name="jdbc/TestDB"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          testWhileIdle="true"
          testOnBorrow="true"
          testOnReturn="false"
          validationQuery="SELECT 1"
          validationInterval="30000"
          timeBetweenEvictionRunsMillis="30000"
          maxActive="100"
          minIdle="10"
          maxWait="10000"
          initialSize="10"
          removeAbandonedTimeout="60"
          removeAbandoned="true"
          logAbandoned="true"
          minEvictableIdleTimeMillis="30000"
          jmxEnabled="true"
          jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
            org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
          username="root"
          password="password"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/mysql"/>
</Context>

Sample web.xml configuration

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

Documentation on Tomcat Pool properties to tweak Tomcat Pool

这篇关于tomcat 7.0.42 pooling，hibernate 4.2，mysql坚实的autoreconnect解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！