SAP HANA Delta Merge
While working on SAP HANA systems, many times we across the term delta merge. This is a very key feature in SAP HANA which ensures effective memory use for your system. But what is this SAP HANA Delta Merge? Let’s Check it out.
What is Delta Merge in SAP HANA
The column store uses efficient compression algorithms to keep relevant application data in memory. Write operations on the compressed data are costly as they require reorganizing the storage structure and recalculating the compression. Therefore write operations in column stores do not directly modify the compressed data structure in the so-called main storage. Instead, all changes are at first written into a separate data structure called the delta storage and at a later point in time synchronized with the main storage. This synchronization operation is called delta merge.
From an end-user perspective, performance issues may occur if the amount of data in the delta storage is large because read times from delta storage are considerably slower than reads from the main storage.
In addition, the merge operation on a large data volume may cause bottleneck situations, since the data to be merged is held twice in memory during the merge operation.
The following alerts indicate an issue with delta merges:
- Delta merge (mergedog) configuration (Alert 10)
- Size of delta storage of column store tables (Alert 29)
Performance Impact of Delta Merge
The minimum memory requirement for the delta merge operation includes the current size of the main storage plus the future size of the main storage plus the current size of delta storage plus some additional memory. It is important to understand that even if a column store table is unloaded or partly loaded, the whole table is loaded into memory to perform the delta merge. The delta merge operation can therefore be expensive for the following main reasons.
- The complete main storages of all columns of the table are re-written in memory. This consumes some CPU resources and at least temporarily duplicates the memory needed for the main storages (while Main 1 and Main 2 exist in parallel).
- The complete main storage is persisted to disk, even if only a relatively small number of records were changed. This creates disk I/O load.
This potentially negative impact on performance can be mitigated by splitting tables. The size of the main storage can be reduced by splitting the table into multiple partitions, each with its own main and delta storage. The delta merge operation is performed at the partition level and only for partitions that actually require it. This means that less data needs to be merged and persisted. Note that there are disadvantages to partitioning tables that should also be considered, this we will cover in a separate post.
Also Read
SAP HANA – FATAL_OUT_OF_MEMORY
Delta Merge Types
The request to merge the delta storage of a table into its main storage can be triggered in several ways. These are called merge motivations, also referred to as merge types.
Auto Merge
The standard method for initiating a merge in SAP HANA is the auto-merge. A system process called mergedog periodically checks the column store tables that are loaded locally and determine for each individual table (or single partition of a split table) whether or not a merge is necessary based on configurable criteria (for example, size of delta storage, available memory, time since last merge, and others).
Auto merge is active if the active parameter in the mergedog section of the indexserver.ini file is set to yes.
Note
- Auto merge can be activated and deactivated for an individual table in the system view TABLES (SYS). The value in the column AUTO_MERGE_ON can be changed to TRUE or FALSE.
- Deactivating auto merge for a table implicitly disables automatic compression optimization as well (column AUTO_OPTIMIZE_COMPRESSION_ON set to FALSE). This is the case even if the AUTO_OPTIMIZE_COMPRESSION_ON column is set to TRUE in the system view TABLES. For tables with auto-merge disabled, compression optimization must be triggered manually.
Smart Merge
If an application powered by SAP HANA requires more direct control over the merge process, SAP HANA supports a function that enables the application to request the system to check whether or not a delta merge makes sense now. This function is called smart merge. For example, if an application starts loading relatively large data volumes, a delta merge during the load may have a negative impact both on the load performance and on other system users. Therefore, the application can disable the auto-merge for those tables being loaded and send a “hint” to the database to do a merge once the load has been completed.
When the application issues a smart merge hint to the database to trigger a merge, the database evaluates the criteria that determine whether or not a merge is necessary. If the criteria are met, the merge is executed. If the criteria are not met, the database takes no further action and only a subsequent hint from the application will trigger another evaluation of the criteria.
Smart merge is active if the smart_merge_enabled parameter in the mergedog section of the indexserver.ini file is set to yes.
Caution
For tables that you want to merge with the smart merge, you should disable the auto-merge. Otherwise, the auto merge and smart merge may interfere with each other.
Hard and Forced Merges
You can trigger the delta merge operation for a table manually by executing the SQL statement MERGE DELTA OF “”. This is called a hard merge and results in the database executing the delta merge for the table either immediately if sufficient system resources are available, or as soon as sufficient system resources become available. The hard merge is therefore subject to the merge token control mechanism.
If you want the merge to take place immediately regardless of system resource availability, you can pass an optional parameter. A forced merge may be useful in a situation where there is a heavy system load, but a small table needs to be merged or if a missed merge of a certain table is negatively impacting system performance. To execute a forced merge, execute the SQL statement MERGE DELTA OF ” WITH PARAMETERS (‘FORCED_MERGE’ = ‘ON’).
Note
- Unlike system-triggered delta merge operations, all of the manually-executed delta merge operations listed here do not subsequently trigger an optimization of the compression of the table’s new main storage.
- If the table was compressed before the delta merge operation, it remains compressed with the same compression strategy afterward.
- If it was not compressed before the delta merge operation, it remains uncompressed afterward.
- After a manual delta merge, you must therefore trigger compression optimization manually.
Critical Merge
The database can trigger a critical merge in order to keep the system stable. For example, in a situation where auto merge has been disabled and no smart merge hints are sent to the system, the size of the delta storage could grow too large for a successful delta merge to be possible. The system initiates a critical merge automatically when a certain threshold is passed.
Reference
- help.sap.com
- SAP Note 1977314
