Use Hikari to optimize SAP Solution Sales Configuration Runtime Engine connection to database

[alexandre_gueriot]

If you run SAP Product Configuration or SAP Solution Sales Configuration in SAP Commerce, you can very likely optimize the performance of your product or solution configurator by configuring Hikari as described in the SAP Note 2863047. Before jumping into the technical details of configuring Hikari for SAP Solution Sales Configuration Runtime Engine, let me detail when and how you can benefit from this new library.

Motivation

The SAP Solution Sales Configuration Runtime Engine is designed to operate from in-memory data. You might have noticed its numerous caches starting with _AP in hAC cache monitoring view. The engine connects to the database to load data not present yet in the caches and don't use further the database once data are loaded and cached. Since database operations are supposed to be one-offs, a single database connection suffices and configuring a database connection pool is an overkill. However, it is not always possible to put the theory into practice and cache all required data. For example, if you enabled pricing and have hundred of millions of pricing condition records, it might not be possible to load all of them in caches without running out-of memory. The engine will consequently fetch the non-cached condition records from the database each time they are needed, which could be each time the configuration is updated and pricing needs to be refreshed. Moreover, cached items in SAP Commerce can have a maximum time to live, which is set to 5 minutes by default for SAP Product Configuration and SAP Solution Sales Configuration. Expired cached data have to be reloaded periodically. In these situations, data load is no more a one-off operation but is frequent and might increase with the load. The single database connection design can hit its limits and might cause increased processing time in the product or solution configurator when concurrent accesses to the database are required. The solution is to configure the SAP Solution Sales Configuration Runtime Engine to use a database connection pool.

Why using Hikari and not another library for configuring the database connection pool? First of all, starting from 3.x release, Hikari is embedded in the SAP Solution Sales Configuration Runtime Engine JAR. No need to install an extra library in SAP Commerce platform, which simplifies a lot the operations. Secondly, the multiple benchmarks available on internet show that Hikari is currently one if not the best choice when reliability and performance are the most valued aspects. Note that you can operate SAP Solution Sales Configuration Runtime Engine with other database connection pools than Hikari and use the one you are more familiar and comfortable with.

Configuration

Configuring SAP Solution Sales Configuration Runtime Engine with a Hikari database connection pool is a three steps operation:

1. Configure the database connection pool in Tomcat as a global JNDI datasource using Hikari


2. Expose the global JNDI datasource in each web application as local JNDI datasource


3. Configure the SAP Solution Sales Configuration Runtime Engine to use JNDI datasource

First, you should edit hybris/config/tomcat/conf/server.xml and add the following XML element under the <GlobalNamingResources> element to setup the Hikari database connection pool as global JNDI datasource.

<Resource name="${crm.ssc_jndi_datasource}"

          auth="Container"

          type="javax.sql.DataSource"

          factory="com.zaxxer.hikari.HikariJNDIFactory"

          driverClassName="${cpq.db.driver}"

          jdbcUrl="${cpq.db.url}"

          username="${cpq.db.username}"

          password="${cpq.db.password}"

          minimumIdle="${cpq.db.minIdle}" maximumPoolSize="${cpq.db.maxPoolSize}"

          registerMbeans="true" poolName="${crm.ssc_jndi_datasource}"/>

Note that Hikari offers more options to configure the connection pool.

Then, you should edit hybris/config/tomcat/conf/context.xml and add the following XML element under the <Context> element.

<ResourceLink name="${crm.ssc_jndi_datasource}"

              global="${crm.ssc_jndi_datasource}"

              type="javax.sql.DataSource"/>

Finally, you should add and adapt the following properties to your local.properties to control the configuration of the connection pool for your scenario.

# Configure SAP Solution Sales Configuration Runtime Engine to use JNDI datasource

crm.ssc_jndi_usage=true

crm.ssc_jndi_datasource=jdbc/CPQ



# Settings used normally by the SAP Solution Sales Configuration Runtime Engine to setup single database connection and still needed to avoid error in Data Loader (see limitations section)

crm.database_hostname=localhost

crm.system_type=MySQL

crm.database_port=3306

crm.database_user=****

crm.database_password=****

crm.database=cpqdb

crm.client=000



# Configure Hikari database connection pool reusing the single database connection settings from the SAP Solution Sales Configuration Runtime Engine

cpq.db.username=${crm.database_user}

cpq.db.password=${crm.database_password}

cpq.db.url=jdbc:mysql://${crm.database_host}:${crm.database_port}/${crm.database}

cpq.db.driver=com.mysql.cj.jdbc.Driver

cpq.db.minIdle=5

cpq.db.maxPoolSize=5

All changes will be applied once you restart your system.

Testing

Now, that the Hikari connection pool is setup and the SAP Solution Sales Configuration Runtime Engine is configured to leverage it, your next step is to ensure it works as promised. First, you should find in the SAP Commerce server startup logs messages similar to the following ones indicating that Hikari started and initialized successfully the connection pool.

[HikariDataSource] jdbc/CPQ - Starting...

[...]

[HikariDataSource] jdbc/CPQ - Start completed.

Secondly, you can monitor the connection pool usage statistics via JMX. For example, you can use JConsole to connect your SAP Commerce JMX port (usually 9003) and check the number of active connections. Note that SAP Solution Sales Configuration Runtime Engine uses often the database connection for couple milliseconds. It is consequently not always easy to see an active connection as it requires to refresh Hikari's JMX MBean during this short time interval.

If you use application monitoring, I highly recommend to connect Hikari's JMX MBeans to your monitoring solution to track the connection pool usage over time, in particular during load tests to validate your pool configuration. Solutions like Dynatrace allows to add custom JMX MBeans and display their metrics in charts.

Limitations

Hikari is embedded in the SAP Solution Sales Configuration Runtime Engine JAR starting with 3.x release. If you run 2.x release, I would highly recommend to upgrade as 3.x release offers a lot of benefits. If you cannot upgrade, you can always add manually the Hikari library to the SAP Commerce platform and follow the same configuration steps.

Although Data Loader uses the same database connection settings that the SAP Solution Sales Configuration Runtime Engine and leverages the connection pool when configured, it expects the single connection properties to be maintained. The bug was reported to the product team and a fix was released with the SAP Note 3199261. If your SAP Solution Sales Configuration Runtime Engine version is older than the fixed one mentioned in the SAP Note, make sure you added exactly the properties I mentioned in your local.properties.

Conclusion

Configuring SAP Product Configuration or SAP Solution Sales Configuration to prevent response time degradation due to the database connection starvation is now very easy. It is a potential simple and quick solution to solve performance issues in the product or solution configurator under load.

However, remember that the SAP Solution Sales Configuration Runtime Engine is designed to operate with in-memory data and its performances will necessarily be degraded when accessing the database. Consequently, you should long term address the reasons preventing data to be cached. For instance, you should review with your business the frequency changes are made to knowledge bases and/or pricing condition records and adapt the time to live parameters for the caches retaining the data. Alternatively, you can disable the cache expiration mechanism and take care to invalidate outdated data (see SAP Note 2408624).