Alfresco millions connection to PostGres

Corentin — Tue, 18 May 2021 12:36:53 GMT

Hello,

We have an Alfresco community installed on an Unix server.
It was installed with the docker-compose.

We made a modification to this to move the PostGres docker to an other machine (dedicated to postgres database).

Everything works.

Our IT service warned us that the Alfresco PostGres DB received a million of connections each hour.
21,177,491 one da, 27,189,102 the next day to be precise.

Is it normal ?

Our IT service is not very happy with that.

Re: Alfresco millions connection to PostGres

afaust — Wed, 19 May 2021 08:23:02 GMT

It depends on how you are using Alfresco and how it has been configured. E.g. if your Alfresco receives millions of calls per hour or runs significant internal (background) processes, and you have configured the connection pool to frequently evict idle connections (default: every 10 minutes connections that have been idle for 30 min are evicted), then this could be expected. Similarily, if connections are dropped because of network issues or becoming invalid in some way, they would be dropped and recreated if needed.

Relevant config properties that affect eviction:

db.pool.evict.interval=600000
db.pool.evict.idle.min=1800000
# number of connections to test per eviction run - "-n" = fractional test of 1/n of all idle
db.pool.evict.num.tests=-1

Relevant config properties that affect connection validation:

db.pool.validate.query=
db.pool.validate.borrow=true
db.pool.validate.return=false

The above default means that when connections are retrieved from the pool, they are first validated IF a query is set for validation (e.g. I have seen SELECT 1 FROM DUAL when using Oracle DB). If the connection is valid, it will be used, if not, it will be removed and a new one retrieved from the pool. If there are sufficient idle connections in the pool, one of them is likely to be used, otherwise a new connection may be created.

db.pool.initial=10
db.pool.max=275
db.pool.min=10
db.pool.idle=10

By default, Alfresco keeps ~10 idle connections. You could change the min/idle numbers to reduce the number of connections Alfresco evicts when the system is not receiving "enough" work to use them.

topic Alfresco millions connection to PostGres in Alfresco Forum

Alfresco millions connection to PostGres

Re: Alfresco millions connection to PostGres