Delta Merge Duration

[Former Member]

Experts

One of our Big table JVSO1 has performance issue during delta merges. We have 4 HANA nodes ( 4th being standby ). We did the table partition and spread across three nodes. So we have one partition on Hana1 node, 1 on Hana 2 node and 2 on Hana 3 node.

Whenever there is a Delta merge occurring on Hana1 node, we see lot of performance issues. Sometimes DB connections from our SLT and ECC servers gets timed out. Also the Delta merge time is higher for no of records compared to same in other nodes.

We have 40 CPU Cores on this host with processing power 2400 MHz.  Hana1 is our Master node. I see the CPU bumped up to 100% during this period of time. How does system calculates as how many threads it allocates to Delta Merge Process

How do we start troubleshooting this issue?

Mahesh Shetty

[Former Member]

Hi Mahesh,

In general, for larger tables Delta merge in system generally utilizes more resources and creates a heavy load on the system. Let me try to explain how it calculates.

1. It is generally based on number of merge tokens

2. Each merge token uses one CPU

3. load balancing cost function (load_balancing_func) determines the actual number of available merge     tokens

4. The number of token that needs to be will be calculated by system.

5. By default, there will be two tokens available for each tale.

Coming to your case, You can try start troubleshooting in this way

** TRY IN DEV/QUALITY SYSTEM BEFORE TRYING IN PRODUCTION **

1. Try to perform manual delta merge and check if it is taking the same time.

2. Change the value of default value of token_per_table to 1 and check if it is helping.

3. Try reducing number of parallel threads in system and perform manual merge for the same table and check if those are help full.

4. You can activate some trace on that and see if there is an issue with the table.

Regards,

Pavan Gunda

[Former Member]

All

We made some changes for past few weeks with respect to tables and see some difference.

1. We moved the JVSO1 partition from HANA1 to HANA3. The Merge is doing better after the move.

2. There are some Unique Indexes copied from ECC when we replicated the table using SLT. These Indexes were very large and consumed large amount memory.

3. We had to identify and delete already existing secondary indices from HANA. 

4. Note helped to resolve our issue 2070029 - Secondary Unique Indices created by SAP LT Replication Server in target HANA database

Mahesh Shetty

[lucas_oliveira]

Hello Mahesh,

In order to be performed a Delta merge operation requires the whole table partition to be loaded into memory. If there's any column that is not fully into memory, disk read operations will have to kick in and get that column.

If the IO key figures of your host is not as in shape as the other hosts then it makes sense for it to perform worse on that particular host.

In that regard you can perform an IO Analysis and verify if the read operations are within the expected KPIs. See SAP Note 1999930 in that regard.

Queries "HANA_IO_Savepoints" and "HANA_IO_KeyFigures_Total" should be handy as well.

That's where I'd start looking.

BRs,

Lucas de Oliveira

[Former Member]

Lucas

I followed your note and other relevant notes for I/O operations and also for save point durations. The note says "If the Critical Phase time" is more than 2 s repeatedly, then its I/O issue. I see some savepoints which are more than 2 Sec. I see 5-6 iterations in the span of 24 hours. Should I consider that as an I/O issue.

I checked Trigger Write Ration and Trigger Read ratio and they seems to be below 0.5 which means my I/O read/write seems to be correct.

I just had another Delta Merge Alert again on Another Node Hana 3 for table ZZJVA which took almost 1100 Sec for 1.5 M records. So I am assuming it is system wide issue.

I will try to open a message with SAP and see if they can help us with the issue.

Mahesh Shetty