CTA DB connection limits

General Discussion
1

Hi,

At Fermilab the CTA DBs are mananged by IT data base support group and they have default setting for max number of connections set to 100.

On our ITB instance we observe:

template1=# select count(*),client_addr from pg_stat_activity where datname='cta_cms_dev' group by client_addr ;
 count |   client_addr
-------+-----------------
     4 | tapesrv1
     4 | tapesrv2
     4 | tapesrv3
     9 | fesrv1
    50 | fesrv2
     4 | okd

(names are bogus and reflect machine purpose, I do not want to publish actual IPs)

fesrv{1,2} - hosts running frontend. tapesrv[1-3] - host running taped. Two taped per host.
okd, I believe runs another frontend

As you can see a small installation uses up 75 connections. I have run into connection limit issues when running metadata migration to CTA (from Fermilab own Enstore HSM)

50 connections to fe* are idle.

I imagine CTA maintains connection pool that typically has some parameters to control
its behavior (including maximum and minumum number of idle connections)

By looking at /etc/cta I see this setting:

# CTA Catalogue options
cta.catalogue.numberofconnections 256

And that’s all. I assume this is max number of connections CTA can make to DB. Are there other parameters controlling idle connection behavior?

Another question - what is the total required number of connections needed. From the query I see we need 4 per taped host. But what is the reasonable value for :

cta.catalogue.numberofconnections

IOW I am asking what setting for above parameter is sufficient? We use CTA w/ dCache frontend, dCache communicates with CTA through cta-frontend-grpc, plus, I assume admin commands need connections. Every dCache pool communicates with cta-frontend-grpc. Do I need the scale cta.catalogue.numberofconnections with number of pools?
So how many connection I need to reserve for front end hosts. I will need this number to bring it to DB group so we can “negotiate” max_connections limit in postgresql.

Thank you,
Dmitry

2

Hi Dmitry,

I hope our discussion from yesterday’s dev meeting was helpful. Once we have added Telemetry to CTA, we should be able to give you a more precise answer.

Cheers,
Niels

3

Thank you.
This is what has been discovered. Our gRPC configuration had:

cta.schedulerdb.numberofthreads 1024
cta.catalogue.numberofconnections 256

It has always been my experience that it is never a good idea to have thread pool larger than number of connections. We trimmed it down to:

cta.schedulerdb.numberofthreads 100
cta.catalogue.numberofconnections 100

And I think we are doing better now. The initial problem with the setup was - when we dropped about 1K restores simultaneously some of them failed due to gRPC failure to respond to requests. I assume it was due to threads blocking while waiting for available connection (number of connections shot to maximum 256).

1 Like