时间戳

首页 > 解决方案 > C3PO：客户端在等待获取资源时超时

mysql - C3PO：客户端在等待获取资源时超时

问题描述

当前设置：

服务主机 (Java) 连接到 (JDBC) 主数据库 (MySQL RW) 并具有工作程序（只读）以提高可靠性。连接管理由 C3PO 处理

设想：

在翻转的情况下，我需要将其中一名工作人员提升为主数据库。当前主节点变为只读，新主节点提升为读写。我希望可靠地自动将 C3P0 连接池刷新到新的主数据库。哪种现在有效，但在某些情况下被破坏了。

技术：服务有一个数据库主机作为 cname，它将在翻转时更新。在 DNS 更改传播且新 db 为 RW 之前，所有连接都会失败。尝试在连接池上无限地设置 C3PO 重试，直到连接通过 (acquireRetryAttempts)

现有 C3PO 配置：

Generic configuration, inherited by primary and worker DB
    <property name="maxIdleTime" value="44000"/>
    <property name="idleConnectionTestPeriod" value="30"/>
    <property name="maxStatements" value="50"/>
    <property name="minPoolSize" value="3"/>
    <property name="maxPoolSize" value="3"/>
    <property name="acquireIncrement" value="2"/>
    <property name="checkoutTimeout" value="15000"/>
    <property name="acquireRetryDelay" value="1000"/>

Primary DB specific config
    <property name="acquireRetryAttempts" value="0"/>
    <property name="minPoolSize" value="3"/>
    <property name="maxPoolSize" value="5"/>
    <property name="testConnectionOnCheckout" value="true"/>
    <property name="connectionTesterClassName" value="${Custom.connectionTesterClassName}"/>

问题：

自定义连接测试器检查连接是否可写（@@global.read_only）并返回连接无效或有效。C3PO 遇到问题，它将按照中间日志中的说明翻新（杀死并重新初始化）连接池。最终，它会检查所有 (max_pool_size, 5) 个连接（在 4-5 分钟内），并在连接不检查回池的状态下挂起。这会导致所有后续数据库连接检查超时并失败，如最终日志中所述。强制重新启动服务，以便重新初始化 C3PO 池。

connectionTesterClassName理想情况下，如果返回 false 或 onidleConnectionTestPeriod或在某个随机时间段，我需要连接池继续终止和重新初始化。由于已签出连接，因此它们不属于空闲连接并且不会刷新。

尝试使用unreturnedConnectionTimeout并debugUnreturnedConnectionStackTraces按照C3PO 文档中的说明进行调试，但是没有运气。

日志：

中间日志

com.mchange.v2.resourcepool.BasicResourcePool: Resource [com.mchange.v2.c3p0.impl.NewPooledConnection@24e7ebec] could not be refurbished in preparation for checkout. Will try to find a better resource.
    (C3P0PooledConnectionPoolManager[identityToken->2tyqoiac1gfqk68krloa8|7e2c64]-HelperThread-#6) com.mchange.v2.resourcepool.BasicResourcePool: Preparing to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@24e7ebec
23 Sep 2020 22:06:23,780 [DEBUG]com.mchange.v2.resourcepool.BasicResourcePool: acquire test -- pool size: 0; target_pool_size: 3; desired target? 1
23 Sep 2020 22:06:23,780 [DEBUG]  com.mchange.v2.resourcepool.BasicResourcePool: awaitAvailable(): com.mchange.v2.c3p0.impl.NewPooledConnection@4b52cf0c
23 Sep 2020 22:06:23,780 [DEBUG]  (C3P0PooledConnectionPoolManager[identityToken->2tyqoiac1gfqk68krloa8|7e2c64]-HelperThread-#6) com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool: Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@24e7ebec
23 Sep 2020 22:06:23,780 [DEBUG]  (C3P0PooledConnectionPoolManager[identityToken->2tyqoiac1gfqk68krloa8|7e2c64]-HelperThread-#6) com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool: Successfully destroyed PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@24e7ebec
23 Sep 2020 22:06:23,780 [DEBUG]  (C3P0PooledConnectionPoolManager[identityToken->2tyqoiac1gfqk68krloa8|7e2c64]-HelperThread-#6) com.mchange.v2.resourcepool.BasicResourcePool: Successfully destroyed resource: com.mchange.v2.c3p0.impl.NewPooledConnection@24e7ebec
23 Sep 2020 22:06:23,781 [DEBUG]  com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool: Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@7fff041e] on CHECKOUT.
23 Sep 2020 22:06:23,782 [INFO]  ReadWriteConnectionTester: Connection com.mchange.v2.c3p0.impl.NewProxyConnection@21eb35f6 [wrapping: com.mysql.jdbc.JDBC4Connection@44732406] is read only!
23 Sep 2020 22:06:23,782 [DEBUG]  com.mchange.v2.c3p0.impl.NewProxyConnection: com.mchange.v2.c3p0.impl.NewProxyConnection@21eb35f6 [wrapping: null]: close() called more than once.
23 Sep 2020 22:06:23,782 [DEBUG]  com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool: Test of PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@7fff041e] on CHECKOUT has FAILED.
java.sql.SQLException: Connection is invalid

最终日志：

org.hibernate.engine.jdbc.internal.LogicalConnectionImpl: Obtaining JDBC connection
com.mchange.v2.resourcepool.BasicResourcePool: acquire test -- pool is already maxed out. [managed: 5; max: 5]
com.mchange.v2.resourcepool.BasicResourcePool: awaitAvailable(): com.mchange.v2.c3p0.impl.NewPooledConnection@4b52cf0c

com.mchange.v2.sql.SqlUtils: Converting Throwable to SQLException...
com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@43aeb5e0 -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) ~[c3p0-0.9.5.1.jar:0.9.5.1]

org.hibernate.engine.jdbc.spi.SqlExceptionHelper: Could not open connection [n/a]
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118) ~[mchange-commons-java-0.2.10.jar:0.2.10]
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77) ~[mchange-commons-java-0.2.10.jar:0.2.10]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:164) ~[spring-jdbc-4.3.24.RELEASE.jar:4.3.24.RELEASE]
    at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:141) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.internal.SessionImpl.connection(SessionImpl.java:550) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]

Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@43aeb5e0 -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    ... 51 more

org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: null
org.hibernate.engine.jdbc.spi.SqlExceptionHelper: An attempt by a client to checkout a Connection has timed out

标签： mysqlhibernatec3p0

解决方案

因此，这不是“翻新（杀死并重新初始化）连接池”的正确解释。

Connection游泳池正试图在这里“翻新”一个人。它正在尝试更新Connection以供客户使用。由于您已testConnectionOnCheckout设置为 true，并且数据库已“翻转”，因此您的 ReadWriteConnectionTester 将声明当前池中的每个连接无效（因为只读），直到池用完连接尝试或由于客户端超时而超时.

您的 ReadWriteConnectionTester 是否返回ConnectionTester.CONNECTION_IS_INVALID或ConnectionTester.DATABASE_IS_INVALID发现 aConnection无效？您应该希望它返回ConnectionTester.DATABASE_IS_INVALID以重置整个池。

推荐阅读