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.
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).
The following roles are available for capturing and replaying workloads.
Capture and Replay
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
listeninterface = .global
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:
|controlhost||database host name|
|controlinstnum||database instance number|
|controladminkey||user name and secure store key (separated by comma)|
|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
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.
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.
In the video tutorial below, I will show you in less than 10 minutes the whole process, both preparation and procedures.
SAP HANA Academy Playlists (YouTube)
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.