Capturing and Replaying Workloads - by the SAP HANA Academy

Introduction

One of the new SPS 12 features for monitoring and managing performance in SAP HANA is the ability to capture and replay workloads. This feature enables you to take a performance snapshot of your current system -- a captured workload -- and then execute the same workload again on the system (or another system from backup) after some major hardware or software configuration change has been made. This will help you evaluate potential impacts on performance or stability after, for example, a revision upgrade, parameter modifications, table partition or index changes, or even whole landscape reorganisations.

In this blog, I will describe the required preparation and the operational procedures.

Preparation

Import Delivery Unit

To capture, replay and analyze workloads you use the three new apps in the equally new SAP HANA Performance Monitoring tile catalog of the SAP HANA cockpit.

The apps are not included with a standard installation of SAP HANA but are provided as a delivery unit (DU): HANA_REPLAY.

You can import the DU using the SAP HANA Application Lifecycle Management (ALM) tool, which is part of SAP HANA cockpit. Alternatively, you can use the ALM command line tool (hdbalm) or use SAP HANA studio (File > Import > SAP HANA Content > Delivery Unit)

Grant Roles

The DU adds the following roles:

• sap.hana.replay.roles::Capture
• sap.hana.replay.roles::Replay
• sap.hana.workloadanalyzer.roles::Administrator
• sap.hana.workloadanalyzer.roles::Operator

Typically, you would grant a user with system administration privileges the Capture replay and Replay replay role. This could be the same user or a different user.

The workloadanalyzer roles are granted to users who need to perform the analysis on the target system. Operators have read-only access to the workload analysis tool.

Configure SAP HANA cockpit

The Analyze Workload app is added automatically to the SAP HANA cockpit if you have any of the two workloadanalyzer roles. The Capture Workload and Replay Workload apps need to be added manually from the tile catalog.

Configure Replayer Service

On the target system you need to configure and start the replayer service before you can replay a workload.

For this, you need to have access as the system administrator the SAP HANA host and create the file wlreplayer.ini in directory $SAP_RETRIEVAL_PATH, typically /usr/sap/<SID>/HDB<instance_number>/<hostname>.

This file needs to contain the following lines

[communication]

listeninterface = .global

[trace]

filename = wlreplayer

alertfilename = wlreplay_alert

Next, start the replayer service with the hdbwlreplayer command:

dbwlreplayer -controlhost hana01 -controlinstnum 00 -controladminkey SYSADMIN, HDBKEY -port 12345

Use the following values for the parameters:

Paramenter	Description
controladminkey	user name and secure store key (separated by comma)
controldbname	optionally, database name in case of multitenant database container system
controlhost	database host name
controlinstnum	database instance number
port	available port

Secure Store Key

In case you are not familiar with secure store keys, or need a refresher,  SAP HANA Academy or the video SAP HANA Academy - SAP HANA Administration: Secure User Store - hdbuserstore [SPS 11] - YouTube

Procedure

Once you have performed the preparation steps, the procedure is simple.

1. Capture Workload

Connect with SAP HANA cockpit to the system, open the Capture Workload app and click Start New Capture in the Capture Management display area. Provide a name and optional description and use the ON/OFF switches to collect an explain plan or performance details. The capture can be started on-demand or scheduled. Optionally filter can be set on the name of the application name, database user, schema user, application user, client or statement type (DML, DDL, procedure, transaction, session, system). Also, a threshold duration can be set and the passport trace level.

When done, click Stop Capture.

Optionally, you can set the capture destination, trace buffer size and trace file size for all captures with Configure Capture.

2. Replay Workload: Preprocess

Once one or more capture have been taken, open the Replay Workload app from the HANA cockpit to preprocess the capture. The captured workloads are listed in the Replay Management display area. Click Edit and then click Start Preprocessing on the bottom right.

2. Replay Workload

Once the capture has been preprocessed, you can start the replay from the same Replay Workload app.

First select the (preprocessed) replay candidate that you want to replay, then select Configure Replay.

In the Replay Configuration window, you need to provide

• Host, instance number and database mode (Multiple for a multitenant database container system) of the HANA system
• Replay Admin user (with role sap.hana.replay.roles::Replay) with either password or secure store key
• Replay speed: 1x, 2x, 4x, 8x, 16x
• Collect Explain plan
• Replayer Service
• User authentication from the session contained in the workload

When the Replay has finished, you can select Go to Report to view replay statistics.

3. Analyze Workload

Third and final step is to analyze the workload. For this start the Analyze Workload app from the SAP HANA cockpit. You can analyze on different dimensions like Service, DB User, Application Name, etc.

Video Tutorial

In the video tutorial below, I will show you in less than 10 minutes the whole process, both preparation and procedures.

Source: scn.sap.com