Database Connection Pooling

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 the following values as the initial pool size for the various use cases. There are different factors for the different use cases that influence the initial size of the pools.

config file optional range for initial poolsize factors for bigger poolsize

repositoryList.xml

no

5-100

amount of different repositories, amount of selfservice usage, amount of creators or analysts

configurationdatabase-config.xml

no

3-50

amount of concurrent users

jobs-config.xml

no

3-20

amount of use of the subscribe feature

userpreferences-config.xml

no

2-10

amount of use of shapefile import

auditlogging-config.xml

yes

3-50

amount of concurrent users

accessmanager-config.xml

yes

2-4

amount of logins

RDBMS data sources with data connectable in the Cadenza Management Center

see databaserepository-config.xml

here 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>