dhis2-users team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

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/dhis2-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(ConnectionFactoryImpl.java:471)
>
>                 at
> org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.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>(AbstractJdbc3gConnection.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(DriverManagerDataSource.java:134)
>
>                 at
> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
>
>                 at
> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
>
>                 at
> com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
>
>                 at
> com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
>
>                 at
> com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
>
>                 at
> com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.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*
>
>
>
> [image: Description: cid:image001.png@01CE610B.93E5D290]
>
> Software & Information Systems
>
> *Health Information Systems Programme*
>
> -----------------------------------------------------
> Email:        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
>
>