dhis2-users team mailing list archive

["Jason Phillips" <jason@xxxxxxxx>]

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