PIM Core database workload consists of a large amount of parallel requests. To handle such workload PIM Core is using DB connection pool. Connection pool holds established jdbc connections and returns them to the pool when higher level data access layer such as Hibernate or UDA close them. Such pool helps to save time required to establish and prepare jdbc connection. However connection pool increase memory consumption. PIM Core maintains one connection pool per data source (MAIN,MASTER,SUPPLIER) and per data access module (UDA,Hibernate), total 6 connection pools. Quartz and JBPM maintain their own connection pools. Due to performance reasons PIM Core is using tomcatjdbc connection pool which is optimized for concurrent access. Popular implementations DBCP and c3p0 are not suitable for PIM Core workloads.

It is important to configure connection pool appropriately. See https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html for more detail on tomcat jdbcpool configuration options.

To improve jdbc performance the connection pool is capable of configuring prepared statement caches. This means prepared statements are cached per connection, and please note that this will significantly increase memory consumption! Also the connections need to be configured in a way that they do not remove idling connections too fast otherwise you will render the cache useless.

JMX

Use hibernate and connection pool JMX managed beans to analyse workload and adjust values on production systems, see also Tuning Advisory

High performance connection pool configuration example

The default configuration should be sufficient for regular workloads. However in context of having many concurrent users and/or system jobs (import, export, data quality, etc.) the connection pool might become a bottle neck. The connection pool will throw an error in case the connections are exhausted, but this will not necessarily happen because there is a wait time until the error will be thrown. To adjust connection pool settings, the following data access template files must be adjusted.

Oracle

When running PIM on Oracle do not enable caching of prepared statements unless you make sure that initialization parameter OPEN_CURSOR is set high enough. The limit is counted globally for all connections that belong to the same pool.

Hibernate

The template files when running PIM on Oracle for the Hibernate connection pools are located in the server\configuration\HPM\database folder:

main.properties.template.ORA11g
master.properties.template.ORA11g
supplier.properties.template.ORA11g

In case you are running PIM on Microsoft SQL Server, the corresponding template files have the MSSQL2008 suffix.
Make sure to modify the template files since PIM will always generate the real property files during server startup.

Configuration for 250 maximum active connections

# increase validation interval (value in milliseconds) to avoid permanent connection check which is unnecessary in today's networks
hibernate.tomcatjdbc.pool.validationInterval = 120000

# increase eviction interval (value in milliseconds) when the pool should check which idling connections could be removed from the pool
hibernate.tomcatjdbc.pool.timeBetweenEvictionRunsMillis = 30000

# increase idle time (value in milliseconds) before the connection may be removed from the pool
hibernate.tomcatjdbc.pool.minEvictableIdleTimeMillis = 1800000

# increase the amount of initial connections since creating new connections is a very time intensive task
hibernate.tomcatjdbc.pool.initialSize = 50


# increase the amount if minimum idling connections which will be kept open at any time to fully utilize statement cache and avoid hard parsing repeating queries
hibernate.tomcatjdbc.pool.minIdle = 125


# increase the amount of maximum idling connections to avoid closing idling connections during peak times
hibernate.tomcatjdbc.pool.maxIdle = 250


# increase the amount of maximum idling connections to avoid the situation that PIM processes are starving from available connections
hibernate.tomcatjdbc.pool.maxActive = 250


# lower the maximum wait time (value in milliseconds) until the connection pool will throw an exception that the pool is exhausted
hibernate.tomcatjdbc.pool.maxWait = 10000


# enable jdbc interceptor for statement cache, increase max value if required by application load pattern
hibernate.tomcatjdbc.pool.jdbcInterceptors = ConnectionState;StatementFinalizer;StatementCache(prepared=true,callable=false,max=250)


# Oracle only: this will force the jdbc driver to enable its builtin statement cache to avoid hard parsing repeating queries
hibernate.tomcatjdbc.pool.connectionProperties = oracle.jdbc.implicitStatementCacheSize=250


# Oracle only: prepared statements (INSERT or UPDATE) can be batched if batch_size is > 1
hibernate.jdbc.batch_size = 50

All other settings should be left untouched. Increasing the amount of connections and/or statement cache size will result in higher memory consumption, so make sure that application server and database memory target is sized properly.

UDA

The template file when running PIM on Oracle for the UDA connection pools is located in the server\configuration\HPM\database\uda folder:

uda-jdbcpool.xml.template.ORA11g

In case you are running PIM on Microsoft SQL Server, the corresponding template file has the MSSQL2008 suffix.
Make sure to modify the template file since PIM will always generate the real property files during server startup.

This example will adjust the same values like the Hibernate connection pool (please note that this is a xml file):

Configuration for 250 maximum active connections

<!-- increase validation interval (value in milliseconds) to avoid permanent connection check which is unnecessary in today's networks -->
<property name="validationInterval">120000</property>

<!-- increase eviction interval (value in milliseconds) when the pool should check which idling connections could be removed from the pool -->
<property name="timeBetweenEvictionRunsMillis">30000</property>

<!-- increase idle time (value in milliseconds) before the connection may be removed from the pool -->
<property name="minEvictableIdleTimeMillis">1800000</property>

<!--  increase the amount of initial connections since creating new connections is a very time intensive task -->
<property name="initialSize">50</property>

<!-- increase the amount if minimum idling connections which will be kept open at any time to fully utilize statement cache and avoid hard parsing repeating queries -->
<property name="minIdle">125</property>

<!-- increase the amount of maximum idling connections to avoid closing idling connections during peak times -->
<property name="maxIdle">250</property>

<!-- increase the amount of maximum idling connections to avoid the situation that PIM processes are starving from available connections -->
<property name="maxActive">250</property>

<!-- lower the maximum wait time (value in milliseconds) until the connection pool will throw an exception that the pool is exhausted -->
<property name="maxWait">10000</property>

<!-- enable jdbc interceptor for statement cache, increase max value if required by application load pattern -->
<property name="jdbcInterceptors">ConnectionState;StatementFinalizer;StatementCache(prepared=true,callable=false,max=250)</property>

<!-- Oracle only: this will force the jdbc driver to enable its builtin statement cache to avoid hard parsing repeating queries -->
<property name="connectionProperties">oracle.jdbc.implicitStatementCacheSize=250</property>

Again, all other settings should be left untouched. Increasing the amount of connections and/or statement cache size will result in higher memory consumption, so make sure that application server and database memory target is sized properly.