[HANA] 2780767 - Hesitantly locking Delta Merge and Optimize Compression

Delta merge의 성능 영향도를 줄이기 위해

   1. Hesitantly locking 사용

   2. Partition 조정

 

https://launchpad.support.sap.com/#/notes/2780767

https://launchpad.support.sap.com/#/notes/2780767

---

 

2780767 - Hesitantly Locking Delta Merge and Optimize Compression

Version 7 from 2020.02.11 in EnglishShow Changes

DescriptionSoftware Components

References

Languages

 

 

 

 

 

 

 

 

 

Symptom

Lock contention during exclusive phases of delta merge and optimize compression

Delta merge and optimize compression need to synchronize with parallel queries and DML statements operating on the same table, when activating a new version of delta or main. For this purpose they acquire an exclusive table lock (index handle). While acquiring the exclusive lock, they need to wait for already running queries and DML statements, which hold a shared lock. While they are waiting for the exclusive lock, new queries and DML statements operating on the same table are blocked until delta merge and optimize compression get the exclusive lock and finally release it again.

In case delta merge and optimize compression have to wait for the lock for a long time, since a long running query does not release the shared lock quickly, this lock contention may have a high impact on system performance and responsiveness.

Other Terms

INSERT UPDATE DELETE UPSERT SELECT

Solution

Hesitantly locking delta merge and optimize compression

Starting with HANA 2 SPS 04 the locking behavior of delta merge and optimize compression has been changed. They only wait for the exclusive lock for a short time. In case they do not get the lock during this time new queries are unblocked immediately and can continue. Delta merge and optimize compression retry to get the lock within the short wait time in a loop. Only when a threshold value for the overall loop time is exceeded, they fall back to the previous locking behavior, and wait for the exclusive lock with blocking new queries and DML statements.

Delta merge and optimize compression also monitor, whether new queries have been blocked, while they were waiting for the lock within the loop. In case new queries have been blocked, they will suspend the loop for some time, such that the probabilty of a new query being blocked for the short wait time will be only 10% on average.

The short wait time and the threshold value for the overall loop time are configurable. These are the configuration parameters in indexserver.ini with the default values set in HANA 2 SPS04:

[mergedog]
max_lock_wait_time=1000
max_lock_retry_period=3600000

Parameter description:

1. max_lock_wait_time: Maximum wait time for hesitant acquire of exclusive lock in milliseconds
   
   When set to a positive value, delta merge and optimize compression will wait at most for the specified time to get the exclusive lock on a column store table (the index handle lock). In case this fails, this is retried in a loop. During each wait for the lock new read transactions on the the same table will be blocked for the specified time only, and will continue immediately, when the wait fails.
   
   The default value of 1000 corresponds to 1 second. When the parameter is set to a value <= 0, hesitant locking is disabled.
   
   
2. max_lock_retry_period: Maximum retry period for hesitant acquire of exclusive lock in milliseconds
   
   Delta merge and optimize compression will wait for the exclusive lock on a column store table without time limitation after exceeding the specified period. New read and DML transactions on the same table will be blocked until delta merge resp. optimize compression get the exclusive lock and finally release it again.
   
   The default value of 3600000 corresponds to 1 hour.

Both parameters can be changed at any time. Parameter changes are consumed by already running delta merge and optimize compression threads, and also have effect for threads, which are already waiting for the lock in a loop.

SAP HANA 2 SPS04:

The above described behavior is available as default with SAP HANA 2 revision 040.00 (SPS 04).

SAP HANA 2 SPS03:

The above behavior is available from SAP HANA 2 revision 037.01 (SPS03) on. But it is not activated by default. You can activate it by setting the above 2 parameters in indexserver.ini:

[mergedog]
max_lock_wait_time=1000
max_lock_retry_period=3600000

You can do so by executing:

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM' ) SET ('mergedog', 'max_lock_wait_time') = '1000',  ('mergedog', 'max_lock_retry_period') = '3600000' WITH RECONFIGURE COMMENT 'Enable Hesitant Locking of Delta Merge';

Further improvements:

• During hesitant locking of delta merge new DML statements may still be blocked on SAP HANA 2 revision 040.00 (SPS04).
  
  This is resolved in:
  
  • SAP HANA 2
  • Revisions >= 037.01 (SPS03)
  • Revisions >= 041.00 (SPS04)
  • or higher
    
    
  • or higher
     
• After delta merge could not acquire the lock for the first exclusive phase withing max_lock_retry_period, a second hesitant locking strategy was used for a maximum of another max_lock_retry_period which only allowed SELECT statements to proceed. DMLM statements were blocked during this second hesitant locking strategy.
  
  Starting from the following revisions, the second hesitant locking strategy is not being used anymore. Instead delta merge will wait for the exclusive lock without time limitation immediately after exceeding max_lock_retry_period:
  
  
  • SAP HANA 2
  • Revisions of SPS03 : SAP is currently working on a permanent solution for this issue. This SAP Note will be updated as soon as a revision containing the solution is available
  • Revisions of SPS04 : SAP is currently working on a permanent solution for this issue. This SAP Note will be updated as soon as a revision containing the solution is available
  • or higher
    
    
  • or higher

Software Components

Software ComponentFromToAnd Subsequent

HDB

2.00

2.00

References

This document refers to

NumberTitle

2757584	SAP HANA 2.0 SPS 04 Database Revision 040
2380229	SAP HANA Platform 2.0 - Central Note

This document is referenced by

NumberTitle

2600030	Parameter Recommendations in SAP HANA Environments
1999998	FAQ: SAP HANA Lock Analysis
2057046