DHIS2 - Db Server / Postgresql Max Connections (FATAL: remaining connection slots are reserved...)

Jason_Phillips · 9 April 2014 12:02

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

Description: cid:image001.png@01CE610B.93E5D290

Software & Information Systems

Health Information Systems Programme

···

Email: jason@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.

bobj · 9 April 2014 12:36

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):

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.

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)
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@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(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

Software & Information Systems

Health Information Systems Programme

Email: jason@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

Lars · 9 April 2014 12:37

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@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(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

Software & Information Systems

Health Information Systems Programme

Email: jason@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

Jason_Phillips · 9 April 2014 13:17

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@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 jason@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(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

Description: cid:image001.png@01CE610B.93E5D290

Software & Information Systems

Health Information Systems Programme

Email: jason@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

Lars · 9 April 2014 13:23

Okay. So each DHIS instance will require its own set of connections, so you
should take required connections per instance and multiply with number of
dhis instances for your max_connections.

Running 12 dhis instances on a single database servers sounds a bit too
much, then the db must have enough mem to support close to 1000
connections. So bumping your max_connections will fix the problem, but
might lead to trouble down the line.

max_connections = 900

Jason_Phillips · 9 April 2014 13:50

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@gmail.com]
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):

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.

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)
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@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(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

Description: cid:image001.png@01CE610B.93E5D290

Software & Information Systems

Health Information Systems Programme

Email: jason@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

bobj · 9 April 2014 14:05

Yes Lars makes good warning about increasing max_connections means you need to take into account increased memory use per connection. Which is mostly affected by the work_mem setting.

A few things to consider :

perhaps you only need to run a training server when training.
you might not need to run a training server per instance
if you are using a staging server (eg for doing metadata editing) then you probably are not going to have many concurrent users for this so you can tune down all pool settings.

Lars, I haven’t looked very recently, but to what extent are we creating new jdbc connections outside of the c3p0 pool? If we are only strictly using the pool its easier to provision these things.

···

On 9 April 2014 14:23, Lars Helge Øverland larshelge@gmail.com wrote:

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

Okay. So each DHIS instance will require its own set of connections, so you should take required connections per instance and multiply with number of dhis instances for your max_connections.

Running 12 dhis instances on a single database servers sounds a bit too much, then the db must have enough mem to support close to 1000 connections. So bumping your max_connections will fix the problem, but might lead to trouble down the line.

max_connections = 900

Lars · 9 April 2014 14:27

Lars, I haven't looked very recently, but to what extent are we creating
new jdbc connections outside of the c3p0 pool? If we are only strictly
using the pool its easier to provision these things.

Like you point about we have deliberately moved away from direct
acquisition of connections to using connection pool. Right now there is
only data mart generation process (which should in most cases be disabled
anyway) and the reporting rates summary in reports module which will get
connections directly. For the latter we have a blueprint of moving it to
analytics engine for 2.16 (and hence go through connection pool). So a few
extra connections could be given for now.

···

bobj · 9 April 2014 14:29

Lars, I haven't looked very recently, but to what extent are we creating
new jdbc connections outside of the c3p0 pool? If we are only strictly
using the pool its easier to provision these things.

Like you point about we have deliberately moved away from direct
acquisition of connections to using connection pool. Right now there is
only data mart generation process (which should in most cases be disabled
anyway) and the reporting rates summary in reports module which will get
connections directly. For the latter we have a blueprint of moving it to
analytics engine for 2.16 (and hence go through connection pool). So a few
extra connections could be given for now.

right so with a c3p0 pool size of 30 then 50 postgres connections would be
a safe bet? Maybe even 40.

···

On 9 April 2014 15:27, Lars Helge Øverland <larshelge@gmail.com> wrote:

Lars · 9 April 2014 14:31

Yes I would say 50.

···

On Wed, Apr 9, 2014 at 4:29 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

On 9 April 2014 15:27, Lars Helge Øverland larshelge@gmail.com wrote:

right so with a c3p0 pool size of 30 then 50 postgres connections would be a safe bet? Maybe even 40.

Lars, I haven’t looked very recently, but to what extent are we creating new jdbc connections outside of the c3p0 pool? If we are only strictly using the pool its easier to provision these things.

Like you point about we have deliberately moved away from direct acquisition of connections to using connection pool. Right now there is only data mart generation process (which should in most cases be disabled anyway) and the reporting rates summary in reports module which will get connections directly. For the latter we have a blueprint of moving it to analytics engine for 2.16 (and hence go through connection pool). So a few extra connections could be given for now.

Jason_Phillips · 9 April 2014 15:23

Thanks to you both!

···

From: Lars Helge Øverland [mailto:larshelge@gmail.com]
Sent: 09 April 2014 04:32 PM
To: Bob Jolliffe
Cc: Jason Phillips; DHIS 2 Users list; Renier Rousseau; Mike Nelushi
Subject: Re: [Dhis2-users] DHIS2 - Db Server / Postgresql Max Connections (FATAL: remaining connection slots are reserved…)

Yes I would say 50.

On Wed, Apr 9, 2014 at 4:29 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

On 9 April 2014 15:27, Lars Helge Øverland larshelge@gmail.com wrote:

Lars, I haven’t looked very recently, but to what extent are we creating new jdbc connections outside of the c3p0 pool? If we are only strictly using the pool its easier to provision these things.

Like you point about we have deliberately moved away from direct acquisition of connections to using connection pool. Right now there is only data mart generation process (which should in most cases be disabled anyway) and the reporting rates summary in reports module which will get connections directly. For the latter we have a blueprint of moving it to analytics engine for 2.16 (and hence go through connection pool). So a few extra connections could be given for now.

right so with a c3p0 pool size of 30 then 50 postgres connections would be a safe bet? Maybe even 40.