Alfresco millions connection to PostGres

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

Corentin

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.

afaust

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.