Hi Lars,
Apologies, I made the posting very quickly while trying to resolve the problem at the same time. So, the system in question is a Db server to four web servers, each of which has three instances (a live, a staging and a training), for a total of 12 databases. The only really active ones at the moment are the live ones, but the staging and instances are running. I couldnt verify on all instances if the nightly datamart process is definitely running, but it is on at least some of them (I dont necessarily have login details on each instance). The server is question has 12Gb RAM, and postgres is tuned using the following settings: shared_buffers = 4206MB # min 128kB work_mem = 11MB # min 64kB maintenance_work_mem = 375MB # min 1MB effective_cache_size = 10516MB wal_buffers = -1 # min 32kB, -1 sets It should be noted we made NO change to the default max_connections value of 100, until I got the error. A bounce of the PostgreSQL service solved the problem temporarily, but we obviously need a long term, reliable solution that we can apply methodically across all five of our Db Servers. In the meantime, Bob has responded with something along the lines I expected, so we are using that as a launch point, but any advice you can offer would be most welcome. Kind regards, Jason. From: Lars Helge Øverland [mailto:larshe...@gmail.com] Sent: 09 April 2014 02:37 PM To: Jason Phillips Cc: DHIS 2 Users list; Renier Rousseau; Mike Nelushi Subject: Re: [Dhis2-users] DHIS2 - Db Server / Postgresql Max Connections (FATAL: remaining connection slots are reserved...) Hi Jason, some questions: - Have you scheduled nightly data mart process? - Are you running multiple dhis instances on the same database server? - What is your current max_connections setting in postgresql.conf? Lars On Wed, Apr 9, 2014 at 2:02 PM, Jason Phillips <ja...@hisp.org> 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:image001.png@01CE610B.93E5D290 Software & Information Systems Health Information Systems Programme ----------------------------------------------------- Email: <mailto:ja...@hisp.org> ja...@hisp.org Mobile : 072 9737250 Landline: 021 7120170 Fax: 021 7120170 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 : dhis2-users@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
<<image001.png>>
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
