PLANTA COMERCIALIZADORA DE ALIMENTOS SANTA FE
CAPITDLO 8.- LIMPIEZA
This section covers troubleshooting of delta merge problems.
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 Store 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 main storage.
In addition the merge operation on a large data volume may cause bottleneck situations, since the data to be merged is hold 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)
3.6.1 Inactive Delta Merge
In case the delta merge is set to inactive, Alert 10 Delta Merge (mergedog) Configuration is raised. In a production system this alert needs to be handled with very high priority in order to avoid performance issues.
Context
Whenever issues with delta merge are suspected, this alert should be checked first. An inactive delta merge has a severe performance impact on database operations.
Figure 8: Delta Merge Set To Inactive
Procedure
1. Check the current parameter value in the Configuration tab of SAP HANA studio and filter for the parameter mergedog.
Check the value of active in the mergedog section of the indexserver.ini.
Figure 9: Check Mergedog Active 2. To correct the value, double-click on active and choose Restore Default.
This will delete all custom values on system and host level and restore the default value system-wide.
Figure 10: Restore Defaults
Note
Depending on the check frequency (default frequency: 15 minutes) the alert will stay in the Alert inbox until the new value is recognized the next time the check is run.
3.6.2 Retrospective Analysis of Inactive Delta Merge
Retrospective analysis of the root cause of the parameter change that led to the configuration alert requires the activation of an audit policy in SAP HANA that tracks configuration changes
Other sources of information are external tools (for example, SAP Solution Manager) that create a snapshot of configuration settings at regular intervals.
For details about configuring security auditing and for analyzing audit logs, refer to the SAP HANA Security Guide.
Related Information
SAP HANA Security Guide
3.6.3 Indicator for Large Delta Storage of Column Store Tables
If the delta storage of a table gets too large, read operations on the table will slow down. This usually results in degraded performance of queries reading from the affected table.
When the delta storage of a table gets too large, the Alert Size of delta storage of Column Store tables (Alert 29) can be raised.
Alert 29 is raised when the amount of memory consumed by the delta storage exceeds the configured
thresholds. The thresholds can be customized in the SAP HANA studio to take into account the configured size
of the delta storage. Note that if the alerts are not configured properly, the symptoms can occur without raising an alert, or there may be no symptoms, even though an alert is raised. For each affected table a separate alert is created.
Usually this problem occurs because of mass write operations (insert, update, delete) on a column table. If the total count of records (record count * column count) in the delta storage exceeds the threshold of this alert before the next delta merge, the alert Check delta storage record count * table column count will be triggered.
Corrective action needs to be taken in one of the following areas:
● Change of an application
● Changed partitioning of the table
● Configuration of delta merge
3.6.4 Analyze Large Delta Storage of Column Store Tables
Analyze and interpret issues related to delta storage with help from alerts in SAP HANA studio.
Procedure
1. If an alert was raised, go to the Alerts Tab in the SAP HANA studio and filter for "delta storage".
Check if the alert is raised for a small number of tables or if it is raised for multiple tables. Focus on tables where the alert has high priority. Alerts raised with low or medium priority usually don’t need immediate action, but should be taken as one indicator for checking the sizing. Also these alerts should be taken into account when specific performance issues with end-user operations on these tables are reported, since read-access on delta storage may be one reason for slow performance.
Figure 11: Check Alert Details
2. Double-click on an alert and check the alert details about its previous occurrences.
a. If the alert occurred several times, check since when this started.
b. Check whether it occurs regularly at a certain time.
This may indicate a specific usage pattern from application side that might have room for
optimization. For example, when many inserts and deletes are performed during a load process, it
might be possible to replace these operations with a suitable filter in the source system. To determine the usage of the table by applications, the data in the Expensive Statements Trace and Load monitor can be employed.
3. Check the time stamp of the alert if it is current, then start with checking current attributes of this table.
Information regarding the delta merge operation on specific tables can be obtained from the system view M_CS_TABLES.
Figure 12: M_CS_TABLES Information
SELECT * FROM SYS.M_CS_TABLES where table_name='<name of table>' and schema_name='<name of schema>';
If no alert was raised, you can check for the tables with the most records in the delta.
SELECT * FROM SYS.M_CS_TABLES where record_count>0 order by raw_record_count_in_delta desc;
4. Check the following attributes:
○ LAST_MERGE_TIME
○ MERGE-COUNT
○ READ_COUNT, WRITE_COUNT
○ RECORD_COUNT
○ RAW_RECORD_COUNT_IN_MAIN
○ RAW_RECORD_COUNT_IN_DELTA
○ MEMORY_SIZE_IN_MAIN
○ MEMORY_SIZE_IN_DELTA
a. If MERGE_COUNT is high then this is an indicator that the delta merge works properly, while a low MERGE_COUNT suggests a need for corrective action.
A large difference between RAW_RECORD_COUNT_IN_MAIN and RECORD_COUNT suggests that the table has not been compressed properly. Note that compression is not triggered when a merge is triggered from an SQLScript, but only in case of AUTO-, SMART- or CRITICAL- Merge.
A high WRITE_COUNT suggests that many insert, update and delete operations occur. If the
occurrence of the delta merge problem is rare, then it usually will be sufficient to trigger the merge for this table manually. See Perform a Manual Delta Merge Operation in the SAP HANA Administration Guide.
b. If there are many deleted records, it is also required to trigger a compress of the table with the following command:
UPDATE <name of table> WITH PARAMETERS('OPTIMIZE_COMPRESSION'='YES');
c. Confirm the delta merge operation has succeeded in the following ways:
Open the table definition in the table editor and on the Runtime Information tab and check the relevant values:
○ LAST_MERGE_TIME
○ MERGE_COUNT
○ RAW_RECORD_COUNT_IN_DELTA
○ LAST_COMPRESSED_RECORD_COUNT
5. If WRITE_COUNT is low, check the threshold value of "Check currently utilized percentage of main memory" in Configure Check Settings of the Alerts tab in SAP HANA studio.
Unless other recommendation has been provided by SAP the default values shall be applied to the system.
Default values are:
○ Low: 800,000,000
○ Medium: 1,600,000,000
○ High: 4,000,000,000
If you find other (lower) settings, then it is likely that the alert occurred due to incorrect configuration of the alerting rather than due to issues with tables, applications or delta merge functions. To resolve this, change the settings back to the default values:
Figure 13: Configure Check Settings
6. If problems with the delta storage re-occur frequently for a specific table, check Merge Statistics for this table. This can be done in System Information > Merge Statistics, where you can put a filter on the table name and schema name.
Figure 14: Merge Statistics in SAP HANA studio
Alternatively you can run the following SQL statement and perform the following checks:
select * from SYS.M_DELTA_MERGE_STATISTICS where table_name='<name of table>' and schema_name='<name of schema>';
a. Check column SUCCESS for records with value other than TRUE.
b. Check the column LAST_ERROR for records with value other than 0. A typical error is 2048 and ERROR_DESCRIPTION shows error 2484 which indicates that there was not enough memory to compress the table after the merge.
For other error codes please refer to the SAP HANA Administration Guide.
c. Check the columns START_TIME, EXECUTION_TIME, MOTIVATION and MERGED_DELTA_RECORDS.
For cases where MERGED_DELTA_RECORDS becomes excessively large the trigger function for the MOTIVATION type should be reviewed and the LOAD should be analyzed for that time frame
( Performance Load ). A value of MERGED_DELTA_RECORDS = -1 suggests that no records were merged but that a compression optimization was performed.
7. If you need to analyze the delta merge statistics for a longer period, than use the equivalent select on table HOST_DELTA_MERGE_STATISTICS of the statistics server:
SELECT * FROM _SYS_STATISTICS.HOST_DELTA_MERGE_STATISTICS where table_name='<name of table>' and schema_name='<name of schema>';
The delta merge configuration can checked in the SAP HANA studio by opening Configuration indexserver.ini mergedog
Figure 15: Merge Dog Parameter
Since the default value for the frequency of delta merges is already 1 minute (check_interval = 60.000 ms), optimization with regards to memory consumption can only be done by adjusting the decision function of the corresponding merge type and the corresponding priority function. However, changes should be done very carefully and always with involvement of experts from SAP. Parameters of the functions are documented in the SAP HANA Administration Guide.
Related Information
SAP HANA Administration Guide M_CS_TABLES
3.6.5 Failed Delta Merge
If many cases are identified where auto merge has failed, the error codes need to be analyzed in more detail.
Note that the merge only failed if SUCCESS is not TRUE. In any other case the error code describes a non-critical condition during a successful merge.
To analyze the error codes, you should increase the trace level to INFO for the components mergedog and mergemonitorin the INDEXSERVER section of the Database Trace.
To change the trace configuration go to the Trace Configuration tab in SAP HANA studio and change the configuration of the Database Trace.
Note
You need to select the Show all components checkbox to display the mentioned trace components.
Figure 16: Database Trace
The following table lists error codes and typical corrective actions.
Table 7: Error Codes
Error Description Recommended Action
1999 General error (no further information
available)
Check the indexserver trace for more errors regarding the exception
2450 Error during merge of delta index oc
curred
Check in diagnosis files for an Out-Of-Memory dump that occurred during the delta merge operation
2458 Table delta merge aborted as cancel
was manually requested by a kill ses
sion call.
No action required.
2480 The table in question is already being
merged.
No action required.
2481 There are already other smart merge
requests for this table in the queue.
No action required.
Error Description Recommended Action
2482 The delta storage is empty or the evalu
ation of the smart merge cost function indicated that a merge is not neces
sary.
No further action required if this occurs occasionally.
If it happens frequently:
Check M_DELTA_MERGE_STATISTICS and review smart merge cost function with SAP experts.
(parameter
smart_merge_decision_func)
2483 Smart merge is not active (parameter
smart_merge_enabled=no)
Change the parameter
smart_merge_enabled=yes)
2484 Memory required to optimize table ex
ceeds heap limit (for failed compres
sion optimization operations, TYPE=SPARSE, SUCCESS=FALSE).
No further action required if this occurs occasionally.
If it happens frequently:
A) Analyze change operations on the table and consider table partitioning to minimize the size of the delta storage.
If no knowledge about application is available, Hash Partitioning with a size of 500.000.00 records is a good initial choice.
B) Analyze change operations on the table and consider adjusting the pa
rameter
auto_merge_decision_func C) Increase delta storage
D) Review sizing
6900 Attribute engine failed Internal error. Check the indexserver
trace for more errors regarding the ex
ception.
29020 ltt::exception caught while operating on
$STORAGEOBJECT$
Internal error. Check the indexserver trace for more errors regarding the ex
ception.
3.6.6 Delta Storage Optimization
Table partitioning allows you to optimize the size of tables in memory and their memory consumption as each partition has its own delta storage.
The memory consumption of a table in memory during a merge operation depends on the number of records, the number and memory size of columns and the memory size of the table. While the number of records can be kept low by triggering a smart merge from the application, optimization with regards to the size of the table
can be achieved by table partitioning. This is due to the fact that each partition holds a separate delta storage.
When a merge is performed, the data from the main storage has to be loaded into memory which is a considerably less amount when only a single partition is handled rather than the full table.
When considering partitioning it is recommended to analyze the typical usage of this table. Partitions should be created in a way that avoids as much as possible that single statements need to access multiple partitions.
If no application knowledge is available, then hash partitioning with a partition size of about 500.000.000 records is a good initial choice.
See, Table Partitioning in the SAP HANA Database in the SAP HANA Administration Guide.
Related Information
SAP HANA Administration Guide