Capturing and Replaying Workloads - by the SAP HAN...

dvankempen · ‎05-20-2016

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 Analyze Workload app is included with a standard installation of SAP HANA but the apps Capture Workload and Replay Workload are not. For these, you need to import the delivery unit (DU): HANA_REPLAY.

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

Grant Roles

The following roles are available for capturing and replaying workloads.

Capture and Replay

sap.hana.replay.roles::Capture
sap.hana.replay.roles::Replay

Analyze Workload

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:

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

Use the following values for the parameters:

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

Secure Store Key

In case you are not familiar with secure store keys, or need a refresher, see SAP HANA database interactive terminal (hdbsql) - by the 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.

3. 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.

Analyze Workload

The analyze workload app enables you to analyze current or historic workloads (not related to workload capture and replay). 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.

SAP HANA Academy - SAP HANA SPS 12: What's New? - Capture and Replay Workloads - YouTube

More Information

SAP HANA Academy Playlists (YouTube)

SAP HANA Administration - YouTube

Product documentation

Capturing and Replaying Workloads - SAP HANA Administration Guide - SAP Library

Thank you for watching

The SAP HANA Academy provides free online video tutorials for the developers, consultants, partners and customers of SAP HANA.

Topics range from practical how-to instructions on administration, data loading and modeling, and integration with other SAP solutions, to more conceptual projects to help build out new solutions using mobile applications or predictive analysis.

For the full library, see SAP HANA Academy Library - by the SAP HANA Academy.

For the full list of blogs, see Blog Posts - by the SAP HANA Academy.