闲置时间过后，连接超时问题 [英] Connection time out issues after inactivity period

问题描述

我们有一个使用hibernate作为ORM工具的api，并使用c3p0作为连接池处理程序.在负载下，我们没有任何问题.但是，当api在一天左右的时间内处于非活动状态时，就会遇到无法获得连接"的异常.因此，如果没有人在周末使用api，则周一星期一我们会收到连接错误.

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.

原因:java.sql.SQLException:客户端尝试检出Connection的尝试已超时.

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

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

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.

目前，我们没有在C3Po中配置任何连接测试.可以说，如果我使用IdleTestPeriod来测试连接，然后再由池将其提供给客户端.然后，如果我的所有连接都在某个时间点测试失败，会发生什么情况?那些失败的连接是否将从池中删除，并再次生成新的活动连接?

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?

当前，这是我们正在使用的c3p0设置.还有其他可能导致此问题的原因吗?

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>

推荐答案

因此，您将checkoutTimeout设置为3秒(3000毫秒).那是您所看到的异常.客户端只能等待三秒钟才能从池中检出Connection；如果三秒钟还不够，他们会看到您的异常.

So you have a checkoutTimeout of 3 secs (3000 msecs) set. That's the Exception you're 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.

问题是，为什么客户需要这么长时间才能获得连接?通常，签出Connection是一个非常快的操作.但是，如果所有连接都已签出，则客户端必须等待从数据库中获取(缓慢)连接.

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.

您已将池配置为非常主动地删除Connections.如果minPoolSize = 5以上的任何连接数闲置时间超过maxIdleTimeExcessConnections = 30秒，则这些连接数将被销毁.但是，您的池已配置用于大规模突发:maxPoolSize = 125.假设您的应用程序安静了一段时间，然后收到了来自客户端的大量连接请求.该池将快速用完Connections并开始获取，突发数量为AcquisitionIncrement = 5.但是，如果突然有25个客户端，并且池中只有5个连接，则第25个客户端在获取连接之前可能会超时是不可能的.

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 improbable 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. 以较低的积极程度消除空闲的多余"连接，因此，一般来说，您的池具有一些服务于突发请求的能力.您可能会完全丢弃maxIdleTimeExcessConnections，并在maxIdleTime = 180秒停用后让Connections慢慢枯萎. (不利之处是，在闲置期间资源占用量更大，时间更长.)

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

将minPoolSize设置为较高的值，以使该池不太可能看到突发的活动，而该活动的连接数太少. (缺点?永久资源占用量更大.)

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.)

从配置中丢弃checkoutTimeout. c3p0的默认设置是允许客户端无限期地等待连接. (不利之处?也许您希望客户快速报告失败，而不是等待可能的成功.)

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.)

我认为您观察到的问题与连接测试或MySQL超时本身没有太大关系，但这并不意味着您不应该处理这些问题.我会遵从nobeh关于MySQL重新连接问题的建议. (我不是MySQL的大用户.)您应该考虑实施Connection测试.您有preferredTestQuery，因此测试应该相当快.我通常的选择是使用testConnectionOnCheckin和idleConnectionTestPeriod.请参见 http://www.mchange.com/projects/c3p0/#configuring_connection_testing

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

祝你好运！

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