I'm using a Liferay cluster connected to pgpool2 and when I run heavy user activity against the web servers I see a lot of exceptions regarding liferay unable to establish a connection to the DB. There are two DBs behind pgpool
Exception examples
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. I have liferay setup to use C3p0, and the db I configured is to pgpool2. I've been playing with this for days now, trying a zillion combinations and I think I'm in the best state but still not there yet and I'm no DBA. Liferay is configured with the same user/pass so I think pgpool would reuse the cached connection but I can't pinpoint the problem. Here's my configs
postgres config
max_connections = 200 shared_buffers = 100MB pgpool2 config
num_init_children = 500 max_pool = 10 child_max_connections = 4950 child_life_time = 300 connection_life_time = 0 client_idle_limit = 0 Liferay config
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 Should I be increasing max_connections on my DB servers behind pgpool? Or change the pgpool params to something like
num_init_children = 10 max_pool = 500 child_max_connections = 4950 connection_life_time = 0 client_idle_limit = 0 any help is appreciated!
