java 非活动期后连接超时问题

Connection time out issues after inactivity period

提问by Npa

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.

我们有一个使用 hibernate 作为 ORM 工具的 api，我们使用 c3p0 作为连接池处理程序。当我们在负载下时，我们没有问题。但是，当 api 已闲置一天左右时，我们会遇到“无法获得连接”的异常。所以，如果周末没有人使用 api，我们会在星期一早上收到连接错误。

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

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

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.

我们使用mysql作为数据库。在我的研究中，我了解到 mySQL 在 8 小时左右后会使连接失效。连接池可能会向客户端发出陈旧的连接，因此客户端的连接超时异常。

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?

目前，我们没有在 C3Po 中配置任何连接测试。可以说，如果我使用 IdleTestPeriod 在连接由池提供给客户端之前测试连接。那么如果我的所有连接在某个时间点都未通过测试，会发生什么情况？那些失败的连接会从池中删除并再次生成新的活动连接吗？

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

目前，这是我们使用的 c3p0 设置。这个问题还有其他可能的原因吗？

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

回答by Steve Waldman

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.

因此，您设置了 3 秒（3000 毫秒）的 checkoutTimeout。这就是你看到的异常。客户端只允许等待三秒钟从池中检出连接；如果三秒钟不够，他们会看到您的异常。

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 improbable that the 25th client might time out before acquiring a Connection.

您已将池配置为非常积极地剔除连接。如果连接空闲时间超过 maxIdleTimeExcessConnections=30 秒，则任何超过 minPoolSize=5 的连接都会被销毁。然而，您的池是为大规模突发配置的：maxPoolSize=125。假设您的应用程序安静了一段时间，然后从客户端收到了大量的连接请求。池将很快耗尽连接并开始获取，以获取增量 = 5 的突发形式进行。但是，如果突然有 25 个客户端并且池中只有 5 个连接，那么第 25 个客户端在获取连接之前可能超时并不是不可能的。

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

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

1. 不那么积极地剔除空闲的“多余”连接，因此一般来说，您的池有一定的容量来服务突发的请求。您可能会完全放弃 maxIdleTimeExcessConnections，并在 maxIdleTime=180 秒不使用后让 Connections 慢慢枯萎。（缺点？在不活动期间，更大的资源占用会持续更长时间。）

2. 将 minPoolSize 设置为更高的值，这样池就不太可能看到连接数太少的活动爆发。（缺点？更大的永久资源足迹。）

3. 从您的配置中删除 checkoutTimeout。c3p0 的默认设置是允许客户端无限期地等待连接。（缺点？也许您更喜欢客户快速报告失败而不是等待可能的成功。）

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

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

Good luck!

祝你好运！

回答by nobeh

In the section of High availability and clusteringin MySQL Java Connector, take a look at the properties; specifically autoReconnectand autoReconnetForPools. Use the properties in your JDBC connection URL. They have helped me before when using MySQL, Hibernate, and C3P0. Hope that this helps.

在部分高可用性和集群在MySQL的Java连接器，看看属性; 具体autoReconnect和autoReconnetForPools。使用 JDBC 连接 URL 中的属性。他们以前在使用 MySQL、Hibernate 和 C3P0 时帮助过我。希望这会有所帮助。