dhis2-users team mailing list archive

["Jason Phillips" <jason@xxxxxxxx>]

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 couldn’t verify on all instances
if the nightly datamart process is definitely running, but it is on at least
some of them (I don’t 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:larshelge@xxxxxxxxx] 
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 <jason@xxxxxxxx> 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:jason@xxxxxxxx> jason@xxxxxxxx

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@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp