Improve SAP Commerce Cloud performance with Read only DB

[Harish_Vatsa]

Improve Hybris performance with Read only DB

Introduction:

It is very often with all the websites running on Hybris that with time, as their business grows and data and transaction volume increases, the website starts showing decaying performance with an increase in the response time.

Replica DB or Reader DB is one option to scale up the performance of Hybris website by segregating read and write operations at the database level.

Unfortunately, in most of the projects because of the lack of proper monitoring, it often leads to the site going down.

What is the right time to use Read only DB?

As the business grows, with more products to sell and more customers to buy, transaction volume increases and starts causing a heavy load on the database, which in turn causes high memory and CPU utilization and often leads to deadlocks also then it is the right time to introduce a replica/reader DB.

The below image shows Database CPU utilization having spikes and reaching almost 100%.

The general architecture of Hybris implementation:

At a very high level, all website traffic is handled by Hybris application and a database (Oracle/MySQL/HSQL/Hana/MaxDB, etc.) running in AWS.

Reader/Replica DB implementation:

In Hybris, product and listing pages execute thousands of read-only queries per second. These queries only read the content from the Database and are not involved in any kind of DDL and DML operations. The main intent of Reader/Replica DB is to redirect all the read-only operations to a new database while all the write operations can be performed on the existing database which decreases the load of the database and hence CPU and memory utilization stays low and chances of deadlocks also reduces.

Implementation 1:

In Hybris application, read-only queries can be easily directed to reader DB by adding the hostname to JDBC URL.

jdbc:mysql://writer_host,replica_host

and then mark the transactions as Read-only.

@Transactional(readOnly = true)
public void methods(){}

Although, because of many Hybris customizations done in Hybris projects, sometimes this simple-looking implementation method doesn’t work effectively. So, we have one more implementation method.

Implementation 2:

In the Hybris application, extra Database details can be configured in different ways. So, we first need to add a slave database with the following command:

slave.datasource.slave1.db.url=jdbc://replica_hostname

The second step is to enable the read-only database before executing the query in the application:

Tenant tenant = Registry.getCurrentTenantNoFallback();
tenant.activateSlaveDataSource();

// execute some query
tenant.deactivateAlternativeDataSource();

 

Conclusion:

In Hybris, whenever it is felt that DB is getting choked and reaching saturation point, the best option to get rid of this problem is to implement Replica/Reader DB in Hybris. It will not only decrease the load on your primary database but also reduce the chances of deadlocks. In addition to this, Reader DB is the best and easiest way to boost the product’s performance.

For more information, please visit following SAP page:

https://help.sap.com/docs/SAP_COMMERCE_CLOUD_PUBLIC_CLOUD/aa417173fe4a4ba5a473c93eb730a417/d578cbe0d...