休息时间后，连接超时 [英] Connection time out isues after inactivity period

问题描述

我们有一个使用hibernate作为ORM工具的API，我们使用c3p0作为连接池处理程序。当我们负荷很重时，我们没有任何问题。但是，如果api在一天左右停用，我们正在耗尽无法获得连接的例外。因此，如果周末没有人使用api，我们会在周一早上收到连接错误。

 由java.sql引起.SQLException：客户尝试检出连接已超时。 
  

我们使用mysql作为数据库。在我的研究中，我知道mySQL会在8个小时左右之后使连接失效。连接池可能会向客户端发出一个陈旧的连接，从而导致客户端的连接超时异常。

目前，我们没有任何C3Po中配置的连接测试。比方说，如果我使用IdleTestPeriod在连接池被给予客户端之前测试连接。那么如果我所有的连接在某个时间点都通过了测试，会发生什么？将这些失败的连接从池中删除并重新生成新的活动连接？

目前，这是我们正在使用的c3p0设置。任何其他原因可能造成这个问题？

 < bean id =dataSourceclass =com.mchange.v2.c3p0 .ComboPooledDataSourcedestroy-method =close> 
< property name =driverClassvalue =$ {----}/> 
< property name =jdbcUrlvalue =$ {----}/> 
< property name =uservalue =$ {----}/> 
< property name =passwordvalue =$ {------}/> 
< property name =minPoolSizevalue =5/> 
< property name =acquireIncrementvalue =5/> 
< property name =maxPoolSizevalue =125/> 
< property name =maxStatementsvalue =10/> 
< property name =maxIdleTimevalue =180/> 
< property name =maxIdleTimeExcessConnectionsvalue =30/> 
< property name =checkoutTimeoutvalue =3000/> 
< property name =preferredTestQueryvalue =SELECT 1/> 
< / bean> 
  

感谢您的帮助

解决方案

所以你的checkoutTimeout设置为3秒（3000毫秒）。这是你看到的例外。客户端只允许等待三秒钟才能检出池中的连接;如果三秒钟不够，他们会看到你的异常。

问题是，为什么客户需要这么长时间才能获得连接？通常检查一个连接是一个非常快的操作。但是，如果所有连接都被检出，则客户端必须等待（缓慢）从数据库中获取连接。

您已将池配置为非常积极地剔除连接。如果minPoolSize = 5以上的连接数超过maxIdleTimeExcessConnections = 30秒，则它们将被销毁。然而，您的池配置为大规模爆发：maxPoolSize = 125。假设你的应用安静了一段时间，然后从客户端得到一连串的连接请求。池将很快耗尽连接并开始以acquireIncrement = 5的突发获取。但是如果突然有25个客户端，而且这个池只有5个连接，那么第25个客户端在获得Connection之前可能会超时并不是不可能。

有很多可以做的。这些调整是可以分开的，你可以根据自己的喜好进行混合或匹配。

<1>禁止闲置多余连接不太积极，所以一般来说，处理突发请求的能力。您可以完全删除maxIdleTimeExcessConnections，并且让连接慢慢地在maxIdleTime = 180秒废弃之后到达。 （下行？在闲置期间资源占用空间较大）

2）将minPoolSize设置为更高的值，这样池不太可能会看到活动的方式太少连接（下降？更大的永久资源足迹。）

<3>从您的配置丢弃checkoutTimeout。 c3p0的默认设置是允许客户端无限期地等待Connection。 （下行？也许你更喜欢客户快速报告失败，而不是等待可能的成功。）

我不认为你观察到的问题有很多连接测试或MySQL超时本身，但这并不意味着你不应该处理这些问题。我会推迟nobeh对MySQL重新连接问题的建议。 （我不是一个大的MySQL用户。）你应该考虑实现连接测试。你有一个preferredTestQuery，所以测试应该相当快。我通常的选择是使用testConnectionOnCheckin和idleConnectionTestPeriod。请参阅 http://www.mchange.com/projects/c3p0/#configuring_connection_testing

祝你好运！

We have an api which uses hibernate as ORM tool and we use c3p0 as the connection pool handler. We have no problems when we are under load. However, we are running out into "unable to obtain a connection" exceptions when the api has been inactive for a day or so. So, if no body uses the api over the weekend, we get connection errors on monday morning.

Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

We use mysql as the database. On my research, i got to know that mySQL makes connections stale after 8 hours or so. It might be possible that the connection pool is giving out a stale connection to the client and hence the connection timeout exceptions for the client.

At present, we do not have any connection testing configured in C3Po. Lets say, if I use IdleTestPeriod to test the connection before they are given to the client by the pool. Then what happens if all my connections fail the test at a point of time? Will those failed connections be removed from the pool and new active connections be generated again?

Currently, this is the c3p0 settings that we are using. Any other reasons possible for this problem?

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="${----}"/>
        <property name="jdbcUrl" value="${----}"/>
        <property name="user" value="${----}"/>
        <property name="password" value="${------}"/>
        <property name="minPoolSize" value="5"/>
        <property name="acquireIncrement" value="5" />
        <property name="maxPoolSize" value="125" />
        <property name="maxStatements" value="10" />
        <property name="maxIdleTime" value="180" />
        <property name="maxIdleTimeExcessConnections" value="30" />
        <property name="checkoutTimeout" value="3000" />
        <property name="preferredTestQuery" value="SELECT 1" />
    </bean>

Thanks for the help

解决方案

So you have a checkoutTimeout of 3 secs (3000 msecs) set. That's the Exception your seeing. Clients are only permitted to wait for three seconds to checkout a Connection from the pool; if three seconds isn't enough, they see your Exception.

The question is, why are clients taking so long to get a Connection? Normally checking out a Connection is a pretty fast operation. But if all Connections are checked out, then clients have to wait for (slow) Connection acquisition from the database.

You have your pool configured to pretty aggressively cull Connections. Any number of Connections above minPoolSize=5 will be destroyed if they are idle for more than maxIdleTimeExcessConnections=30 seconds. Yet your pool is configured for large-scale bursts: maxPoolSize=125. Suppose that your app is quiet for a while, and then gets a burst of Connection requests from clients. The pool will quickly run out of Connections and start to acquire, in bursts of acquireIncrement=5. But if there are suddenly 25 clients and the pool has only 5 Connections, it's not improbably that the 25th client might time out before acquiring a Connection.

There's lots you can do. These tweaks are separable, you can mix or match as you see fit.

1) Cull idle "excess" Connections less aggressively, so that in general, your pool has some capacity to servce bursts of requests. You might drop maxIdleTimeExcessConnections entirely, and let Connections slowly whither after maxIdleTime=180 seconds of disuse. (Downside? A larger resource footprint for longer during periods of inactivity)

2) Set minPoolSize to a higher value, so that it's unlikely that the pool will see a burst of activity for which it has way too few Connections (Downside? Larger permanent resource footprint.)

3) Drop checkoutTimeout from your config. c3p0's default is to allow clients to wait indefinitely for a Connection. (Downside? Maybe you prefer clients to quickly report a failure rather than wait for possible success.)

I don't think that the problem that you are observing has much to do with Connection testing or MySQL timeouts per se, but that doesn't mean you should not deal with those issues. I'll defer to nobeh's advice on the MySQL reconnect issue. (I'm not a big MySQL user.) You should consider implementing Connection testing. You have a preferredTestQuery, so tests should be reasonably fast. My usual choice is to use testConnectionOnCheckin and idleConnectionTestPeriod. See http://www.mchange.com/projects/c3p0/#configuring_connection_testing

Good luck!

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