☰

使用 Liferay 集群的 Pgpool 配置

tag-icon • tag-icon database cluster liferay postgresql
使用 Liferay 集群的 Pgpool 配置

我正在使用连接到 pgpool2 的 Liferay 集群，当我在 Web 服务器上运行大量用户活动时，我看到很多有关 liferay 无法与数据库建立连接的异常。pgpool 后面有两个数据库

异常示例

Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
----------
Caused by: com.mchange.v2.resourcepool.ResourcePoolException: A ResourcePool cannot acquire a new resource -- the factory or source appears to be down.
org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:152)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:393)
    at org.postgresql.Driver.connect(Driver.java:267)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:211)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
Caused by: java.io.EOFException
    at org.postgresql.core.PGStream.ReceiveInteger4(PGStream.java:289)
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:282)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
    ... 14 more
------
02:34:55,197 WARN  [C3P0PooledConnectionPoolManager[identityToken->Q5VpVuN8]-HelperThread-#0][BasicResourcePool:894] Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@427db1c2 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

我已经将 liferay 设置为使用 C3p0，并且我配置的数据库是 pgpool2。我已经玩了好几天了，尝试了无数种组合，我认为我处于最佳状态，但仍然没有达到最佳状态，而且我也不是 DBA。Liferay 配置了相同的用户/密码，因此我认为 pgpool 会重用缓存的连接，但我无法确定问题所在。这是我的配置

postgres 配置

max_connections = 200
shared_buffers = 100MB

pgpool2 配置

num_init_children = 500
max_pool = 10
child_max_connections = 4950
child_life_time = 300
connection_life_time = 0
client_idle_limit = 0

Liferay 配置

jdbc.default.acquireIncrement=5
jdbc.default.acquireRetryAttempts=3
jdbc.default.acquireRetryDelay=1000
jdbc.default.connectionCustomizerClassName=com.liferay.portal.dao.jdbc.pool.c3p0.PortalConnectionCustomizer
jdbc.default.idleConnectionTestPeriod=60
jdbc.default.maxIdleTime=3600
jdbc.default.maxPoolSize=1000
jdbc.default.minPoolSize=10
jdbc.default.numHelperThreads=100

我是否应该增加 pgpool 后面的数据库服务器上的 max_connections？或者将 pgpool 参数更改为类似

num_init_children = 10
max_pool = 500
child_max_connections = 4950
connection_life_time = 0
client_idle_limit = 0

任何帮助都将受到赞赏！

答案1

从pgpool-II 用户手册

max_pool*num_init_children <= (max_connections - superuser_reserved_connections)

如果您需要 500 个并发 pgpool 连接，那么您的 Postgres 配置中就需要更多max_connections。因此，对于 500 个并发连接，您需要：

在pgpool.conf：

num_init_children = 500
max_pool = 1 # there's no need for more if you don't use different credentials

在postgresql.conf：

max_connections = 550

另外 - 我认为你不应该使用 C3PO 和 PgPool-II - 将两个连接池堆叠在一起是没有意义的。另外我认为你不应该使用多个数据库。

相关内容