连接池问题 [英] Connection pool issue

问题描述

如果我在闲置一段时间之后启动我的应用程序，我会遇到以下错误。 （我使用Spring + Hibernate + MySQL作为数据库）

$ p $ ERROR [org.hibernate.util.JDBCExceptionReporter]最后一个数据包成功从服务器收到的是74,188,684毫秒前。
成功发送到服务器的最后一个数据包是74,188,685毫秒前。比服务器配置的'wait_timeout'值长。在应用程序中使用之前，应考虑过期和/或测试连接有效性，增加服务器配置的客户端超时值，或者使用Connector / J连接属性'autoReconnect = true'来避免此问题。
org.hibernate.exception.JDBCConnectionException：无法执行查询


我解决了这个问题添加到我的servlet-context.xml中。

 < beans：property name =validationQueryvalue =SELECT 1 /> 
  

我曾问过这个问题这里更具体到解决方案。我需要知道为什么我得到这个错误。

我在上面的链接和两个工作。尽管如此，我仍然没有明白为什么我得到了错误。

这是我更新的servlet-context.xml文件，我使用ApacheDBCP进行连接池。

 < beans：bean id =MyIDclass =org.apache.commons.dbcp.BasicDataSourcedestroy-method =close> 
< beans：property name =driverClassNamevalue =com.mysql.jdbc.Driver/> 
< beans：property name =urlvalue =jdbc：mysql：// localhost：17761 / myDB/> 
< beans：property name =usernamevalue =myname/> 
< beans：property name =passwordvalue =mypwd/> 
< beans：property name =maxIdlevalue =5/> 
< beans：property name =maxActivevalue =20/> 
< beans：property name =minIdlevalue =5/> 
< beans：property name =validationQueryvalue =SELECT 1/> 
< / beans：bean> 
  

它是否存在一些连接到期问题？请帮我理解。

解决方案

以下是用于说明发生的事件的流程： $ b

1. 连接被调用者（应用程序或连接池）请求和使用


2. 调用者保留对它的引用，以便可以重新使用连接


3. 呼叫者经历一段时间的不活动状态（例如，隔夜的开发系统或周末的QA系统）。
   
4. 一旦该数据库连接没有被使用，数据库认为连接是空闲的。因为它是空闲的，所以在一段时间之后（MySQL默认是8小时），数据库关闭连接。


5. 调用者仍然有一个连接句柄，当调用者尝试再次使用连接不愉快地发现连接已关闭。
   

autoReconnect = true的原因，并且池正在测试合法性的连接工作，是你指示呼叫系统测试这种情况的连接，并在这种情况发生时再试一次。

至于验证查询是否会影响性能：理论上它使用连接来执行某些操作。在实践中，某些东西如此微不足道，以至于在整个系统环境中它的效果可以忽略不计。

在这种情况下，Apache DBCP是挂在连接上的连接池，但您不希望DBCP在每次调用后关闭连接。连接池的要点是为下一次呼叫保持连接，因为创建连接非常昂贵。由池维护的连接对象由实际的数据库连接支持，数据库是在空闲超时期限后关闭实际连接的连接对象。请注意，关闭空闲连接的超时在数据库上配置，而不是在连接池上配置。因此，DBCP无法知道连接是否已关闭，除非它实际尝试连接它。这就是为什么您需要验证查询。

有关配置DBCP的更多信息，请参阅配置页面和 API文档。

If I launch my application after it was idle for some time, I used to get below error. ( I am using Spring+Hibernate+MySQL as DB )

ERROR [org.hibernate.util.JDBCExceptionReporter]The last packet successfully received from the server was 74,188,684 milliseconds ago. 
The last packet sent successfully to the server was 74,188,685 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
org.hibernate.exception.JDBCConnectionException: could not execute query

I solved this issue by adding below to my servlet-context.xml.

<beans:property name="validationQuery" value="SELECT 1"/>

I had asked this question here which was more specific to solution.I need to know why I was getting that error.

I tried the 1st (Configure the connection string with autoReconnect=true ) and 3rd option (Configuring the connection pool to test the validity of the connection) provided in the above link and both worked. Still I dont get why in first place I was getting the error.

Here is my updated servlet-context.xml file and I am using ApacheDBCP for connection pooling.

<beans:bean id="MyID" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <beans:property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <beans:property name="url" value="jdbc:mysql://localhost:17761/myDB"/>
        <beans:property name="username" value="myname"/>
        <beans:property name="password" value="mypwd"/>
        <beans:property name="maxIdle" value="5"/>
        <beans:property name="maxActive" value="20"/>
        <beans:property name="minIdle" value="5"/>
        <beans:property name="validationQuery" value="SELECT 1"/>
</beans:bean>

Is it some connection expiry issue ? Please help me to understand.

解决方案

Here is the flow of events to illustrate what's happening:

1. A connection is requested and used by the caller (application or connection pool)
2. The caller keeps a reference to it so that the connection can be re-used
3. The caller goes through a period of inactivity (for example, a dev system overnight or a QA system over the weekend).
4. Once that database connection is not in use, the database considers the connection to be idle. Because it is idle, after a certain amount of time (MySQL default is 8 hours) the database closes the connection.
5. The caller still has a handle to the connection, and when the caller tries to use the connection again unpleasantly discovers that connection has been closed.

The reason autoReconnect=true works, and that the pool testing the validity of the connection works, is that you are instructing the calling system to test the connection for this situation and to try again if this situation happens.

As for whether the validation query will affect performance: In theory it is using a connection to do something. In practice that something is so trivial that its effect is negligible in the context of your entire system.

[EDIT]

In this case Apache DBCP is the connection pool hanging on to the connection, but you do NOT want DBCP to close the connection after every call. The point of the connection pool is to keep a connection ready for the next call because creating connections is expensive. The connection objects maintained by the pool are backed by actual database connections, and the database is the one who closes that actual connection after the idle timeout period. Note that the timeout to close idle connections is configured on the database, not on the connection pool. Because of this, DBCP has no way of knowing whether the connection has been closed or not unless it actually tries to connect with it. That’s why you need a validation query.

For more information about configuring DBCP, see the configuration page and the API docs.

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