Database Connection Pools

Cadenza uses different Connection Pools for each infrastructure use case and for each data source configured in the management center. On a multi node environment the pools exists on each node. Please note that the total number of connections that Cadenza establishes to the database can become very large because of this.

Databases for Cadenza infrastructure functions

Cadenza uses fixed size connection pools for the cadenza management connections. All connections of the pools will be established on startup of the cadenza server and are maintained as long as the server is running. It is therefore necessary to set the pool sizes individually for each environment (e.g. development, testing, production).

We recommend keeping the size of the connection pools rather small and monitoring the pools with the help of a monitoring system and only making them larger if necessary.

The following metrics should be monitored:

metric usecase

cadenza_dbpool_connection_acquire_time_seconds

time to get a connection from the pool

cadenza_dbpool_connections_active

number of connections currently in use

cadenza_dbpool_configured_max_connections

configured maximum connection count of the pool

See Monitoring with Prometheus for more information on monitoring.

We recommend to start with the lower bound as the pool size for the various use cases. There are different factors for the different use cases that influence the size of the pools.

configuration optional typical range for pool size factors for bigger pool size

databaserepository or repositoryList.xml

no

5-100

more repositories, more self-service usage, more creators or analysts

configurationdatabase

no

3-50

more concurrent users

jobs

no

3-20

more usage of the subscribe feature

userpreferences

no

2-10

more usage of the Shapefile import feature

auditlogging

yes

3-50

more concurrent users

accessmanager

yes

2-4

more logins using “CadenzaDb” Classic authentication

RDBMS data sources with data connectable in the Cadenza Management Center

See also databaserepository configuration.

There is a large set of configuration options for these data sources. However, it is usually sufficient to set the maximumConnectionCount parameter and otherwise leave the default values.

Example for minimum configuration
<connectionPool>
  <maximumConnectionCount>7</maximumConnectionCount>
</connectionPool>
Example for maximum configuration
<connectionPool>
  <maximumConnectionCount>7</maximumConnectionCount>
  <maximumWaitTimeInMilliseconds>30000</maximumWaitTimeInMilliseconds>
  <idleConnectionTimeoutInMilliseconds>60000</idleConnectionTimeoutInMilliseconds>
  <minimumIdleConnectionCount>2</minimumIdleConnectionCount>
  <validationSqlStatements>
    <validationSqlStatement type="Oracle">select 1 from dual</validationSqlStatement>
    <validationSqlStatement>select 1</validationSqlStatement>
   </validationSqlStatements>
   <validationQueryTimeoutInMilliseconds>15000</validationQueryTimeoutInMilliseconds>
</connectionPool>