Using PostgreSQL max_connections and max_prepared_transactions

The topic provides details about estimating the max_connections and max_prepared_transactions required for the Content Cortex domain.

On the PostgreSQL database server, the max_connections configuration option sets the maximum number of client connections that this server will accept. The number of database client connections required for a Content Cortex domain can be estimated for each of the categories listed below, then summed up and the sum increased by 10-20% to account for possible underestimates. If database server configuration changes are needed, assistance from the site administrator for the PostgreSQL database server may be required.

Many of these use values that fluctuate over time, for example, peak logged in users. The change is both in the short term such as more users and batch document processing at end of month processing, and long term, such as more users from rollouts or increased use of system. These estimates will serve as a good starting point, but best practice is to also monitor connection use and track the trends.

The estimate can be used to determine what values to use for the PostgreSQL configuration options max_connections and max_prepared_transactions. Set max_connections to a value greater than the sum of all the maxPoolSize settings in all the data sources defined. The Content Cortex also requires max_prepared_transactions to be set. PostgreSQL recommends that max_prepared_transactions to be at least as large as max_connections so that every session can have a prepared transaction pending.

For each JVM in each pod there will be a number of processes running that use database connections in the connection manager's available pool. The size of the latter is controlled by the properties in the data source definition:
  • minPoolSize - minimum number of available connections in the pool. default 0.
  • maxPoolSize - maximum number of available connections in the pool. default 50.
The number of data sources present depends on the use of the database:
  • GCD database has a pair of data sources defined; one for non-XA transactions and one for XA transactions.
  • Object store databases can use a dedicated pair or shared pair of data sources.
  • Configuration databases for other components typically have one non-XA datasource defined for the component.
Other factors to consider:
Base connections per Content Platform Engine instance
Because of the possible sharing of the data sources by multiple object store, the estimation looks at the database connection pools allocated for the data sources and not at the total number of object stores. Estimate sum of the GCD database and all the data sources for the object stores:
  • GCD database: maxPoolSize * 2 * number of pods
  • Object store databases: maxPoolSize * number of data sources * number of pods
Database connections for other pods
For other CCx components that use the same database server, determine what the maxPoolSize value is. This will either be the default value or a value provided in the overrides for the data source definition. You make separate calculations for each component and sum them up. Other components that require database connections include IBM® Content Navigator and Task Manager. Estimate for each component configuration database:
  • Component database connection: maxPoolSize * number of pods
PostgreSQL
Check the information for the PostgreSQL type you are using in your environment for additional contributions to the max_connections calculation.