This tutorial will walk us through the end-to-end steps in SAP HANA capture and replay tool.
What can I do with SAP HANA capture and replay?
Capture and replay tool could capture the real workload from source SAP HANA system and replay the captured workload to a target SAP HANA system. Both source system and target system should be on premise system.
The tool could be used to detect, analyze, or verify any potential issues for various scenarios, such as:
◉ Hardware change
◉ SAP HANA revision upgrade
◉ SAP HANA ini file change
◉ Table partitioning change
◉ Index change
◉ Landscape reorganization for SAP HANA scale-out systems
Apply HINT to queries
The tool supports statements based performance comparison and result verification.However, the tool does not offer workload level comparison between the capture time from the source system and the replay time to the target system.
End-to-end Hands-On
Hands-on environment
SAP HANA Cockpit 2.0 SP13
Source system : SAP HANA 2.0 version 48
Control system : SAP HANA 2.0 version 54
Target system : SAP HANA 2.0 version 54
1) Create users for each database & register in the Cockpit
The first step is creating users for each database system(source, control, and target) and register each system in the Cockpit. All 3 database systems should be registered in the Cockpit to use SAP HANA capture and replay tool.
Privileges as a Technical User to register the resource in SAP HANA Cockpit
◉ CATALOG READ
◉ SELECT on the _SYS_STATISTICS
Database user privilege in source system:
◉ WORKLOAD CAPTURE ADMIN
◉ BACKUP ADMIN
◉ OPTIMIZER ADMIN
SQL commands:
CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL FOREVER;
GRANT CATALOG READ TO <username>;
GRANT SELECT on SCHEMA _SYS_STATISTICS TO <username>;
GRANT WORKLOAD CAPTURE ADMIN TO <username>;
GRANT BACKUP ADMIN TO <username>;
GRANT OPTIMIZER ADMIN TO <username>;
Database user privilege in control system:
◉ WORKLOAD CAPTURE ADMIN
◉ WORKLOAD REPLAY ADMIN
◉ WORKLOAD ANALYZER ADMIN
SQL commands:
CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL FOREVER;
GRANT CATALOG READ TO <username>;
GRANT SELECT on SCHEMA _SYS_STATISTICS TO <username>;
GRANT WORKLOAD REPLAY ADMIN TO <username>;
GRANT WORKLOAD CAPTURE ADMIN TO <username>;
GRANT WORKLOAD ANALYZE ADMIN TO <username>;
Database user privilege in target system:
◉ WORKLOAD REPLAY ADMIN
◉ USER ADMIN
Database recovery in step 5 will remove all target database users created before the recovery. there are 4 options for target database replay user.
Option 1 : Reuse source system SYSTEM user in the Target Database
Option 2 : Create a new user in the Target Database after recovery
SQL commands:
CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL FOREVER;
GRANT CATALOG READ TO <username>;
GRANT SELECT on SCHEMA _SYS_STATISTICS TO <username>;
GRANT WORKLOAD REPLAY ADMIN TO <username>;
GRANT USER ADMIN TO <username>;
Option 3 : Reuse the Source Database user in the Target Database with granting replay privileges
SQL commands:
GRANT WORKLOAD REPLAY ADMIN TO <username>;
GRANT USER ADMIN TO <username>;
Option 4 : Create a new user in the Source Database before starting capture and backup
SQL commands:
CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL FOREVER;
GRANT CATALOG READ TO <username>;
GRANT SELECT on SCHEMA _SYS_STATISTICS TO <username>;
GRANT WORKLOAD REPLAY ADMIN TO <username>;
GRANT USER ADMIN TO <username>;
2) Source Database : Capture
To start capture, please go to the HANA Cockpit System Overview page of the source database and choose Start New Capture directly in the Capture Workload card.
To change the capture file destination (*.cpt file extension), choose Configure Capture on the bottom right of the Capture Management page.
The captured workload file is stored by default in the trace directory. (e.g.,/usr/sap/<SID>/HDB##/<hostname>/trace/)
In Capture Configuration, please select Usage and enter the name of the new capture. Please also customize other optional settings before you start the capture. Turning Create Backup on is recommended, the option could be used together with Synchronize Replay with Backup option for the replay.
In general, for most use cases, select Replay in Usage and enable Create Backup option is enough.
For other options, please refer to the SAP HANA Cockpit help document linked from the question mark icon at up right corner of the tool.
Once the capture is started, the information will be shown in the Capture Workload card, Capture Management Page and Capture Monitor page. Capture Monitor page shows the detailed information.
Please choose Stop Capture on the bottom right of the Capture Monitor page to finish the capture.
3) Control Database : Preprocess a Captured Workload
To preprocess a capture workload, the captured workload file should be copied or moved to the control database directory (e.g., /usr/sap/<SID>/HDB##/<hostname>/trace/) with proper file permissions.
Then please go to the SAP HANA Cockpit System Overview page of the control database and choose Start New Replay in the Replay Workload card.
Please choose Start in SAP HANA Cockpit Replay Management page to preprocess the capture file.
4) Control Database : Run Replayer
Once preprocessing is finished, please configure hdbuserstore entries and run replayer in the control database by accessing its OS level using <sid>adm.
Configure Secure User Store Keys
The following Secure User Store Keys should be configured to run the replayer and replay workloads.
Control Database user
This Secure User Store Key will be used for running the replayer.
OS command :
hdbuserstore SET <KEY> “<control_DB_Hostname:port>” <USERNAME> <PASSWORD>
Once control database user is registered in the Secure User Store, the key could be used to run the replayer.
OS command :
hdbwlreplayer -controlhost <control_DB_Host> -controlinstnum <control_DB_InstanceNumber> -controladminkey <USERNAME>,<KEY> -controldbname <control_DB_Name> -port <listenPortNumber>
Example:
<listenPortNumber> can be any port which is not in use.
Replayer should be up and running during until the end of the replay.
Replayer can be stopped by Ctrl+C or kill <pid> in OS.
Once replayer is running, it can be reachable from SAP HANA Cockpit Replay Configuration page by clicking the refresh icon.
Target SYSTEM user
This Secure User Store Key will be used for User Authentication in step 6.
OS command :
hdbuserstore SET <KEY> “<target_DB_Hostname:port>” SYSTEM <PASSWORD>
Target Database user
This Secure User Store Key will be used for Replayer Authentication in step 6.
As SYSTEM user can be used for this as well, if SYSTEM user will be used for Replayer Authetication during the replay, this step is not needed.
OS command:
hdbuserstore SET <KEY> “<target_DB_Hostname:port>” <USERNAME> <PASSWORD>
5) Target Databse : Recover from Source Database Backup
Recovering the target database with the source database backup is required to ensure that the source database and the target database are in a consistent state for capture and replay.
Performing a database backup right after starting the capture by using Create Backup option in step 2 together with using Synchronize Replay with Backup option in step 6 is highly recommended.
Please follow 10 steps and 3 steps in below 2 pictures to finish the target database recovery from source database backup. Before starting the recovery, source database data backup file should be copied or moved to the target database directory (e.g., /usr/sap/<SID>/HDB##/<hostname>/trace/) with proper file permissions.
For copy database steps explanation, please refer to the SAP HANA Cockpit help document linked from the question mark icon at up right corner of the tool.
6) Control Database : Replay
General Information
Please define the replay name and select Target Host Name. If Create Backup option in step 2 is enabled, enable Synchronize Replay with Backup option in step 6 is highly recommended.
Replayer Authentication
Replay Authentication is for assigning the running replayer that will be used to connect to the target system and facilitate the replay.
Replayer List: Select the replayer in Replayer List started in step 4.
Replayer Authentication: Target database User Name and Secure User Store key created in step 4 should be used. If the user credential is correct, green text “Validated credentials” will appear.
User Authentication
User Authentication is needed for including workloads from all required users.
System User: Secure User Store Key of Target database SYSTEM user created during step 4 should be used. If the key is correct, the text box color will become green.
Technical User: Enabled in case target technical user appeared in capture to authenticate the user (e.g., source technical user name is equal to target technical user name)
Database Users: For a stable replay, resetting the passwords of the database users is recommended and for this, you have the following options:
◉ Reset Password: Reset all passwords for all users except for the SYSTEM user and the technical user. On the Reset Password window enable the confirmation box and choose Confirm.
◉ User List: You can authenticate users manually by creating the Secure User Store Keys for the users in step 4.
Choose Start Replay once everything is ready.
Once the replay is started, choose Details to check the information in the HANA Cockpit Replay Monitor. Please choose Go to Report once the replay is finished.
7) Control Database : Replay Report Analysis
In the Replay Report, the first thing to check is availability of the Load chart menu. If it is missing, please choose Capture Workload card and make sure that the workload is loaded. Load chart is useful when comparing two replays.
Overview Tab
Result Comparison Card
In Result Comparison card, the functional correctness can be checked based on the number of record.
Identical: Row counts of individual SQL statement from source and target are identical
Different: Row counts are different
Failed: Execution has failed
Dummy Skipped: Execution has been skipped due to dummy request (result verification is also skipped)
Actual Skipped: Execution is done but result verification is skipped due to reasons other than dummy request
Different Statements Card
Statement: Individual SQL statement where the number of record is different. The full SQL statement can be checked by clicking More
Execution Count: The number of execution of the individual SQL statement
Deterministic: The number of execution that should be consistent
Difference Count: The number of execution with the different row count between source and target
Verification Skipped Card
In Verification Skipped card, details of skipped statements can be checked.
Multiple reasons can be appeared at the same time, in that case the reasons are concatenated with comma.
Performance Comparison Card
In Performance Comparison card, SQL execution time of individual statement can be compared.
Comparable: SQL performance is comparable between capture and replay
Faster: SQL performance is faster during replay comparing to capture
Slower: SQL performance is slower during replay comparing to capture
Failed: Execution has failed
Dummy Skipped: Execution has been skipped due to dummy request (result verification is also skipped)
Actual Skipped: Execution is done but result verification is skipped due to reasons other than dummy request
Comparable is related to Tolerance Ratio(10% by default) and Replay Report Threshold(100ms by default). Please refer to the examples below to know how SQL performance is categorize as Comparable.
Slower Statements Card
Statement: Individual SQL statement where the average difference is higher than the threshold. The full SQL statement can be checked by clicking More
Execution Count: The number of execution of the individual SQL statement
Average Difference Time: Average of elapsed time difference (between capture and replay) of the individual SQL statements
Maximum Elapsed Time: Maximum elapsed time of execution on target system of the individual SQL statements
Replay Failed Card
In Replay Failed card, details of failures can be checked.
Authentication failed: when invalid credential was given or failure during authentication
No connection to execute statements with: when only FetchCursor was captured without ExecutePrepared or ExecuteStatement
Invalid table name: FOUND_INIFILE_CONTENTS: line 1 col 12 (at pos 11): Missing table during CALL statement execution
Invalid table name: could not find table/view: missing table during SQL statement execution
Other reasons: unique constraints violated, out of memory, and any failure can happen depending on the situation
All error messages from replay can be shown here (any possible errors from the statement execution)
Load Tab
Load tab can be used to compare database KPIs between different captures and replays.
Performance Comparison Tab
Performance Comparison tab can provide detailed performance information of each captured and replayed SQL.
Result Comparison Tab
Result Comparison tab can provide detailed result verification information of each captured and replayed SQL.
Best Practice for Avoiding Issues
Always start with a small capture (e.g., 10-30 minutes) and test its replay
After clearing issues, then proceed with longer captures
If there are any issues, please carefully review the traces (e.g., authentication failures)
In case of authentication failure, check if credential is valid or database users are locked down due to the previous invalid login attempts
Don’t get confused with what has to be done on which system (e.g., control or target system)
Best Practice for troubleshooting
Collect runtimedumps of replay target system thrice
Collect target indexserver and wlreplayer traces
If reproducible, collect traces again with
◉ wlreplayer.ini/[trace]/workloadreplayer=info
◉ indexserver.ini/[trace]/workloadreplay=info(in SPS05 or higher),stmtdep_mgr=debug(in SPS04 or lower)
Raise a ticket with the collected traces and source/control/target revision information (component: HAN-CPT-CPT2-CNR)
It would be best if customer can provide the capture or preprocess file for investigation (we don’t need backup files in general)
Do’s and Don’ts
No comments:
Post a Comment