Hi Bob, Thanks very much for your quick response - we'll do a little tuning on this end tonight, and see how it goes. The pool info was useful. I will do some investigation and experimentation and let you know what we end up with.
Kind regards, Jason. From: Bob Jolliffe [mailto:[email protected]] Sent: 09 April 2014 02:37 PM To: Jason Phillips Cc: dhis2-users; Renier Rousseau; Mike Nelushi Subject: Re: [Dhis2-users] DHIS2 - Db Server / Postgresql Max Connections (FATAL: remaining connection slots are reserved...) Hi Jason These problems happen under high concurrency conditions which seems to agree with your log snippet (11am would be when the users are thronging). There are 3 limits to consider which have some impact on one another (I think your immediate problem is the last of the 3): 1. From the java application side I think the default max connection pool size is set to 30 in code. You can set hibernate.c3p0.max_size = 100 in hibernate.properties which can make sense for very busy servers. 2. The number of concurrent web connections (which place a strain on the c3p0 db connection pool) is limited by the threadpool settings in tomcat. You can look here for example of how to adjust connection pool parameters (http://bazaar.launchpad.net/~dhis2-devs-core/dhis2/trunk/view/head:/tools/d his2-tools-deb/pkg/usr/share/dhis2-tools/skel/conf/server.xml) 3. Postgresql has a max connections limit which often needs to be increased - particularly if (like I think you are doing) you have a central postgresql database serving multiple instances. I think the default setting in postgresql.conf is 100. You need to make sure that this is sufficient to cater for all your dhis instances plus other sundry connections you might make. In the past dhis2 has had a tendency to attempt to acquire new connections quite greedily, but it has become better behaved and now mostly (if not entirely) goes through the pool. So if your pool size is 30 I would conservatively factor a max number of connections of 50. So if you have 5 instances consider increasing max_connections in postgresql.conf to something more than 5x50=250. eg. 300. I don't think anyone has done a good performance prediction analysis to help with this kind of tuning. There are really many things to consider. So it is something of an art more than science at the moment. Would be good to measure and document more. Meanwhile I hope the above helps. Regards Bob On 9 April 2014 13:02, Jason Phillips <[email protected]> wrote: Hi all, Our Db Server / Postgresql appears to be having connection slot issues - a catalina log extract example below: WARN 2014-04-09 11:43:34,299 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@7127652d -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: (BasicResourcePool.java [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0]) org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionF actoryImpl.java:471) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFa ctoryImpl.java:112) 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.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnecti on.java:22) at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection. java:32) at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.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(DriverManagerDataS ource.java:134) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(Wrap perConnectionPoolDataSource.java:182) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(Wrap perConnectionPoolDataSource.java:171) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourceP oolManager.acquireResource(C3P0PooledConnectionPool.java:137) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.ja va:1014) at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.j ava:32) at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourceP ool.java:1810) Is the solution just as simple as increasing the number of max slots, along with the shmmax? Has anyone else had this sort of config issue, and if so, are there any stats to be used as guideline for the number of connections generated by a set number of users in DHIS2? i.e. how does one plan for this sort of thing? Kind regards, Jason Phillips Description: cid:[email protected] Software & Information Systems Health Information Systems Programme ----------------------------------------------------- Email: <mailto:[email protected]> [email protected] Mobile : 072 9737250 Landline: 021 7120170 <tel:021%207120170> Fax: 021 7120170 <tel:021%207120170> Skype: jason.n.phillips This message may contain privileged and confidential information intended only for the person or entity to which it is addressed. Any review, retransmission, dissemination, copy or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient, is prohibited. If you received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the material from any computer. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the view of the entity transmitting the message. _______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
<<image001.png>>
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
