[Solved] How to handle HANA Alert 29: 'Size of delta storage of column-store tables

Alert Description:

Determines the size of the delta storage of column tables.

Symptom

You receive HANA alert  ‘Size of delta storage of column-store tables’ (Alert ID 29). The alert is triggered for individual tables.
In case of mass changes or when there is a resource bottleneck in the system due to high load, several occurrences of this alert may occur in parallel.
In revisions lower then 83 the alert often goes along with alert ‘Record count of delta storage of column-store tables’ (Alert ID 19). Both alerts can be processed together since they reflect mostly the same root cause.
Alert ID 19 is going to be suspended in the new Embedded Statistics Server.
Environment
This KBA is created based on SAP HANA revision 82 (SPS 08).

 

Cause

Many changes to a table have been performed after the last delta merge operation. This is typically due to:

  1. Mass write operations from an application (e.g. initial load, batch jobs):
    Code is not optimized, e.g. data is not appropriately filtered upfront, so that new data is added and than deleted in a subsequent step.
    Recommended Action: Optimization from Application Side to be considered.
  2. High system load is causing a temporary bottleneck:
    Delta merges may not be performed to the extend that seems to be required.
    Recommended Action: Usually no action is required, since delta merge will be performed automatically when workload decreases. Increase of hardware resources to be considered.
  3. Errors occured indicating failure of the delta merge operation or some of its steps:
    Database Errors may have occurred.
    Recommended Action: Analyze issue based on indexserver trece files. Consider temporary increase of trace levels for delta merge operation.
  4. Total Size of a table is too large, so that delta merge cannot be performed:
    RTEdumps due to Out-of-Memory occur.
    Table merge is not started.
    Recommended Action: Partition the table to reduce memory consumption of delta merge  or implement measures to reduce memory footprint of the table in main memory.

Resolution

Procedure

  1. Check HANA Alert status
  2. Check configuration of mergedog in ini-files
  3. Check information of affected table in system view M_CS_TABLES
  4. Check merge statistics of the table
  5. Ensure that auto-merge is not switched-off for the affected table
  6. Check if available memory is big enough to perform a delta mergeCheck if available memory is big enough to perform a delta merge
  7. Perform Sizing Review
  8. Define and execute Table Partitioning
  9. Perform Optimization of Memory Footprint
  10. Perform detailed analysis of delta merges as required

Path

  1. SAP HANA Studio – SAP HANA Administration Console – Alerts – Current Alerts
  2. SAP HANA Studio – SAP HANA Administration Console – Configuration
  3. SAP HANA Studio – SAP HANA Administration Console – System Information > Merge Statistics
  4. SAP HANA Studio – SAP HANA Administration Console – SQL Console
  5. SAP HANA Studio – SAP HANA Administration Console – Overview
  6. SAP Solution Manager – transactiopn: SM_Workcenter – SAP Engagement and Service Delivery – Services
  7. SAP Solution Manager – transactiopn: SM_Workcenter – Data Volume Management

How-to

1. Check HANA Alert status

Go to the Alerts tab in the SAP HANA Studio and filter for “delta storage”.

  1. Check whether the alert is raised for a small number of tables or whether it is raised for multiple tables.
  2. Focus on tables where the alert has high priority.
  3. 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.

If there are only few alerts, then proceed with step 2 directly.
If there are many alerts then delete the filter and check for priority high alerts related to performance. Afterwards it is recommended to proceed with step 2 and then with to gain an overview on overall merge statistics and errors first and analyze specific tables afterwards.
The following alerts indicate that the high amount of delta storage may be due to a resource bottleneck in the system or due to a configuration issue:

Alert ID Alert Name Issue Comment Reference
to KBA
5 Host CPU Usage Resource Bottleneck  1909670
10 Delta merge (mergedog) configuration Configuration – AUTOMATIC DELTA MERGE is SWITCHED-OFF HANDLE FIRST 1909641
40 Total memory usage of column-store tables Size of a single table may be too large to allow successful merge  1977268
43 Memory usage of services Resource Bottleneck  1900257
44 Licensed memory usage Resource Bottleneck – usually not just temporary issue Consider Sizing Review 1899511
45 Memory usage of main storage of column-store tables Size of a single table may be too large to allow successful merge HANDLE SECOND 1977269
46 RTEdump files Resource Bottleneck  1977099
55 Columnstore unloads Resource Bottleneck  1977207

2. Check configuration of mergedog in ini-files

In the Configuration tab of SAP HANA Studio filter for ‘mergedog’. Expand the section of indexserver.ini and check if parameters are configured as default:

  • active = Yes
  • check_intervall = 60000 (corresponding to one trigger of mergedog per minute)

To correct the parameter value, double-click on the parameter name and choose Restore Default. This will delete all custom values on system and host level and restore the default value system-wide.
Note: All other parameters in mergedog configuration should only be changed based on recommendations from SAP Support after analyzing your specific case. Typically such a change is not required.

3. Check attributes of affected table in system view M_CS_TABLES

Information regarding the delta merge operation on specific tables can be obtained from the system view M_CS_TABLES. You can use the following simple statement:
SELECT * FROM SYS.M_CS_TABLES where table_name='<name of table>’ and schema_name='<name of schema>’
Check the following attributes:

    • LAST_MERGE_TIME
    • MERGE-COUNT
    • READ_COUNT, WRITE_COUNT
    • RAW_RECORD_COUNT_IN_MAIN
    • RECORD_COUNT
    • MEMORY_SIZE_IN_MAIN
    • MEMORY_SIZE_IN_DELTA
  1. 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 indicates that the table has not been compressed properly. Note that compression is not triggered when a merge is triggered from SQLScripts, but only in case of AUTO-, SMART- or CRITICAL- Merge.A high WRITE_COUNT indicates that many insert, update and delete operations occured. If the occurrence of the delta merge problem is rare, then it usually will be sufficient to trigger the merge for this table manually. Use one of the following SQL statements:
          – MERGE DELTA OF ‘<table_name>’      – MERGE DELTA OF ‘<table_name>’ WITH PARAMETERS (‘FORCED_MERGE’ = ‘ON’)Note: 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. For large tables a forced merge at high system load will likely lead to Out-of-memory dumps.
  2. 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’);
  3. Confirm if 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
  4. 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. The 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.

4. Check merge statistics for a specific table

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.
Alternatively you can use SQL statement HANA_ColumnStore_Merges_Rev70+ (HANA_ColumnStore_Merges for earlier revisions) from SAP Note 1969700. You have to put a filter on the table name in the modification section.

  1. Check for error codes and apply recommendations from SAP HANA Troubleshooting and Performance Analysis Guide (Chapter 3.6.5. Failed Delta Merge)
  2. Create a ticket to SAP Support if further assistence in analyzing the root cause of error codes is required (Component HAN-DB)

5. Ensure that auto-merge is not switched-off for the affected table

If table has not been merged for a long time, then  verify if auto merge is deactivated for the affected table, execute the following statement in SQL console: SELECT * FROM “SYS”.”TABLES” where TABLE_NAME = ‘<Table Name>’.
If the value of column ‘AUTO_MERGE_ON’ is set to ‘FALSE’ please follow the next steps:

  1. Validate the parameter auto_merge of a specific table is off. Use the following SQL:SELECT * FROM SYS.TABLES WHERE TABLE_NAME = <table name>
  2. Log on in the HANA studio with the schema owner of the table and execute the alter statement:
    ALTER TABLE <table_name> WITH PARAMETERS ( AUTO_MERGE=’ON’)

6. Check if available memory is big enough to perform a delta merge

For large tables where the sum of MEMORY_SIZE_IN_MAIN and MEMORY_SIZE_IN_DELTA is larger than 10-20% of the memory allocation limit, the available memory may not be enough to complete a successful delta merge. Get information on memory allocation limit and used memory from the overview screen in HANA Studio and apply the following formula (ff a table is partitioned then this rule applies to each partition individually):
ALLOCATION LIMIT – Used Memory – 2 * (MEMORY_SIZE_IN_MAIN and MEMORY_SIZE_IN_DELTA) > 0.

7. Define and execute Table Partitioning

Table partitioning allows you to optimize the size of tables in memory and their memory consumption as each partition has its own delta storage. 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.
To define an appropriate partitioning, see recommendations in SAP HANA Administration Guide and KBA 2044468 – FAQ: SAP HANA Partitioning. Product specific recommendations may be found in SDN, operation guides and further SAP notes.
If available memory is not enough, then

  1. Define a partitioning of the table.
  2. Schedule a downtime for end-users.
  3. Calculate the amount of additional memory plus 5-10 % safety margin.
  4. Identify largest column store tables based on SAP Early Watch Alert report or by using SQL Statment ‘HANA_Memory_TopConsumers’ from SAP Note 1969700.
  5. Unload large tables to the extent required for releasing used memory.
  6. Perform Partitioning according to SAP HANA Administration Guide and KBA 1909742
  7. Load previously unloaded tables.

8. Perform Sizing Review

Consider to perform a sizing review and to increase hardware if required. You can proceed HANA sizing along the Guided Selfservice ‘Sizing for SAP HANA’ which is available in SAP Solution Manager SAP Engagement and Service Delivery workcenter. You can create the service locally and execute it for getting sizing recommendations.
KBA 1999997 – FAQ: SAP HANA Memory: … Question 11. How can I judge if the available memory is sufficient for the current system and a projected future growth? provides recommendations on a more quick check.

9. Perform Optimization of Memory Footprint

KBA 1999997 – FAQ: SAP HANA Memory: … Question 10. Which options exist to reduce the risk of SAP HANA memory issues? provides an overview about technical measures to reduce the memory footprint of SAP HANA.
Consider to apply suitable recommendations to get rid of short-term issues.
Based on expected data growth in your system it is strongly recommended to implement or update procedures for data volume management (‘DVM’) on application side. Best Practices are documented on the Information Lifecycle Management pages on SAP Service Market Place http://service.sap.com/ilm. Several tools incorporating these best practices are available in SAP Solution Managter to support automation of DVM.

10. Perform detailed analysis of delta merges as required

Check in system view M_DELTA_MERGE_STATISTICS if delta merges are performed without issues. Use SQL statement HANA_ColumnStore_Merges_Rev70+ (HANA_ColumnStore_Merges for earlier revisions) from SAP Note 1969700.
Note the various options in the modification section that help to adjust this statement to specific needs. Consider to create an SAP incident (SV-BO-DB-HAN) to get further help with analysis.

SELECT
START_TIME,
HOST,
PORT,
LPAD(NUM, 4) NUM,
TYPE,
SCHEMA_NAME,
TABLE_NAME,
LPAD(TO_DECIMAL(DURATION_S, 10, 2), 10) DURATION_S,
LPAD(ROUND(RECORDS_MERGED), 11) ROWS_MERGED,
LPAD(TO_DECIMAL(MAP(RECORDS_MERGED, 0, 0, DURATION_S / RECORDS_MERGED * 1000), 10, 2), 15) TIME_PER_ROW_MS,
LAST_ERROR
FROM
( SELECT
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘TIME’)   != 0 THEN TO_CHAR(M.START_TIME, BI.TIME_AGGREGATE_BY) ELSE ‘any’                                           END START_TIME,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘HOST’)   != 0 THEN M.HOST                                      ELSE MAP(BI.HOST, ‘%’, ‘any’, BI.HOST)               END HOST,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘PORT’)   != 0 THEN TO_CHAR(M.PORT)                             ELSE MAP(BI.PORT, ‘%’, ‘any’, BI.PORT)               END PORT,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘SCHEMA’) != 0 THEN M.SCHEMA_NAME                               ELSE MAP(BI.SCHEMA_NAME, ‘%’, ‘any’, BI.SCHEMA_NAME) END SCHEMA_NAME,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘TABLE’)  != 0 THEN M.TABLE_NAME                                ELSE MAP(BI.TABLE_NAME, ‘%’, ‘any’, BI.TABLE_NAME)   END TABLE_NAME,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘TYPE’)   != 0 THEN M.TYPE                                      ELSE ‘any’                                           END TYPE,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘ERROR’)  != 0 THEN M.LAST_ERROR                                ELSE ‘any’                                           END LAST_ERROR,
COUNT(*) NUM,
MAP(BI.AGGREGATION_TYPE, ‘MAX’, MAX(M.DURATION_S), ‘AVG’, AVG(M.DURATION_S), ‘SUM’, SUM(M.DURATION_S)) DURATION_S,
MAP(BI.AGGREGATION_TYPE, ‘MAX’, MAX(M.RECORDS_MERGED), ‘AVG’, AVG(M.RECORDS_MERGED), ‘SUM’, SUM(RECORDS_MERGED)) RECORDS_MERGED,
BI.ORDER_BY
FROM
( SELECT
BEGIN_TIME,
END_TIME,
HOST,
PORT,
SCHEMA_NAME,
TABLE_NAME,
MIN_MERGE_DURATION_S,
MIN_RECORDS_MERGED,
ONLY_FAILED_MERGES,
AGGREGATE_BY,
MAP(TIME_AGGREGATE_BY,
‘NONE’,        ‘YYYY/MM/DD HH24:MI:SS.FF7’,
‘HOUR’,        ‘YYYY/MM/DD HH24’,
‘DAY’,         ‘YYYY/MM/DD (DY)’,
‘HOUR_OF_DAY’, ‘HH24’,
TIME_AGGREGATE_BY ) TIME_AGGREGATE_BY,
AGGREGATION_TYPE,
ORDER_BY
FROM
( SELECT                                                /* Modification section */
TO_TIMESTAMP(‘1000/01/01 00:00:00’, ‘YYYY/MM/DD HH24:MI:SS’) BEGIN_TIME,
TO_TIMESTAMP(‘9999/12/31 23:59:00’, ‘YYYY/MM/DD HH24:MI:SS’) END_TIME,
‘%’ HOST,
‘%’ PORT,
‘%’ SCHEMA_NAME,
‘%’ TABLE_NAME,
-1 MIN_MERGE_DURATION_S,
-1 MIN_RECORDS_MERGED,
‘X’ ONLY_FAILED_MERGES,
‘NONE’ AGGREGATE_BY,           /* TIME, HOST, PORT, SCHEMA, TABLE, TYPE, ERROR and comma-separated combinations, NONE for no aggregation */
‘HOUR’ TIME_AGGREGATE_BY,      /* HOUR, DAY, HOUR_OF_DAY or HANA time pattern, NONE for no aggregation */
‘SUM’ AGGREGATION_TYPE,       /* MAX, AVG, SUM */
‘TIME’ ORDER_BY              /* TIME, TABLE, OCCURRENCES, DURATION */
FROM
DUMMY
)
) BI,
( SELECT
HOST,
PORT,
SCHEMA_NAME,
TABLE_NAME ORIG_TABLE_NAME,
TABLE_NAME || MAP(PART_ID, 0, ”, ‘ (‘ || PART_ID || ‘)’) TABLE_NAME,
PART_ID,
START_TIME,
EXECUTION_TIME / 1000 DURATION_S,
TYPE || ‘ (‘ || MOTIVATION || ‘)’ TYPE,
GREATEST(0, MERGED_DELTA_RECORDS) RECORDS_MERGED,
MAP(LAST_ERROR, ‘0’, ‘0’, LAST_ERROR || CHAR(32) || ERROR_DESCRIPTION) LAST_ERROR
FROM
M_DELTA_MERGE_STATISTICS
) M
WHERE
M.HOST LIKE BI.HOST AND
M.SCHEMA_NAME LIKE BI.SCHEMA_NAME AND
M.ORIG_TABLE_NAME LIKE BI.TABLE_NAME AND
TO_CHAR(M.PORT) LIKE BI.PORT AND
M.START_TIME BETWEEN BI.BEGIN_TIME AND BI.END_TIME AND
( BI.MIN_MERGE_DURATION_S = -1 OR M.DURATION_S >= BI.MIN_MERGE_DURATION_S ) AND
( BI.MIN_RECORDS_MERGED = -1 OR M.RECORDS_MERGED >= BI.MIN_RECORDS_MERGED ) AND
( BI.ONLY_FAILED_MERGES = ‘ ‘ OR M.LAST_ERROR != ‘0’ )
GROUP BY
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘TIME’)   != 0 THEN TO_CHAR(M.START_TIME, BI.TIME_AGGREGATE_BY) ELSE ‘any’                                                                  END,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘HOST’)   != 0 THEN M.HOST                                                             ELSE MAP(BI.HOST, ‘%’, ‘any’, BI.HOST)                        END,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘PORT’)   != 0 THEN TO_CHAR(M.PORT)                                             ELSE MAP(BI.PORT, ‘%’, ‘any’, BI.PORT)                        END,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘SCHEMA’) != 0 THEN M.SCHEMA_NAME                                               ELSE MAP(BI.SCHEMA_NAME, ‘%’, ‘any’, BI.SCHEMA_NAME) END,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘TABLE’)  != 0 THEN M.TABLE_NAME                                                    ELSE MAP(BI.TABLE_NAME, ‘%’, ‘any’, BI.TABLE_NAME)       END,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘TYPE’)   != 0 THEN M.TYPE                                                              ELSE ‘any’                                                                    END,
CASE WHEN BI.AGGREGATE_BY = ‘NONE’ OR INSTR(BI.AGGREGATE_BY, ‘ERROR’)  != 0 THEN M.LAST_ERROR                                                 ELSE ‘any’                                                                    END,
BI.AGGREGATE_BY,
BI.AGGREGATION_TYPE,
BI.ORDER_BY
)
ORDER BY
MAP(ORDER_BY, ‘TIME’, START_TIME) DESC,
MAP(ORDER_BY, ‘TABLE_NAME’, TABLE_NAME),
MAP(ORDER_BY, ‘OCCURRENCES’, NUM, ‘DURATION’, DURATION_S) DESC

 

Leave a Reply