HANA Resident memory HEX OOM（out of momery）限制Resident memory无限增长

对HANA数据库Resident memory无限增长的控制后记（2021.5.14）：

我用了1年多的时间来经历这个问题：

2020年1~5月，我们的HANA数据库运行在500GB内存的临时环境，驻留内存Resident memory一路飙升至最大物理内存。

在时间点1：2020年6月，hana数据迁移至正式HA环境，1.5T的内存主机，驻留内存Resident memory一路飙升至最大物理内存。

在时间点2：HA软件发现物理内存用完，HANA数据库没有响应，对数据库做了重启。

在时间点3：管理员开始干预，对驻留内存Resident memory相关参数做了调整，从最后的曲线来看，驻留内存Resident memory的使用被彻底控制住了。

 

 

 

 

对HANA数据库Resident memory无限增长的控制调整：

Out of momery 记得第一次听到这个词是SAP的哥们陈清超在2012年告诉我的，今天终于和OOM有了亲密的一次接触，文章NOTES后面有一帮BASIS老哥们的详细讨论。

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('hex', 'enable_interpreter_cache') = 'false' WITH RECONFIGURE COMMENT 'SAP Note 2808956.'

 

SAP NOTES 2808956 - Increased Used Memory Size due to Pool/L/llang/Debuggee

Symptom

You see a constant increase of used memory in HANA. Most of the memory is used by the allocator Pool/L/llang/Debuggee.

In situations of memory shortage or after HANA restart, this memory is released.

Other Terms

HEX, out of memory, OOM

Reason and Prerequisites

Issue number 232277

Reason:

HEX queries will get interpreted when executed the first two times. The interpreted plans are cached and stored using the allocator Pool/L/llang/Debuggee. After executing the statement the third time, they get compiled and the interpreted plans are evicted from cache. If many different queries are only executed 1-2 times, they will remain in the cache and cause high memory consumption. Memory utilzation of this allocator is controlled by the resource manager. So in case of memory shortage, memory from this cache gets released with highest priority. In case a high number of elements have to be evicted from the cache to handle the memory request, it can sporadically come to very short delays in execution of such a query, while sufficient elements are released.

Affected Releases:

• SAP HANA 2:
  • Revisions >= 033.00 (SPS03)
  • Revisions >= 040.00 (SPS04)

Prerequisites:

• There are complex queries executed in HEX engine.

Solution   从NOTES上看，数据库版本0.50以上是没有这个问题的

• SAP HANA 2:
  • Revisions >= 050.00 (SPS05)
  • and higher

Workaround:

• On SAP HANA 2.0 SPS04 Revision 43 and higher you can switch off caching of interpreted HEX query plans by parameter indexserver.ini -> [hex] -> enable_interpreter_cache
  • You can set this parameter by:
    ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM' ) SET ('hex', 'enable_interpreter_cache') = 'false' WITH RECONFIGURE COMMENT 'SAP Note 2808956.';
  • This parameter may have slightly bad performance impact of HEX queries which are rarely executed. Queries which are frequently executed, will not use the interpreter cache after the second execution anymore and therefore there is almost no performance impact expected for those.
  • After setting the parameter, memory usage of allocator Pool/L/llang/Debuggee will not immediately be released. This will only happen during memory shortage situations, resman shrink or after HANA restart.
     
• Or you can use the parameter: global.ini -> [memoryobjects] -> unload_upper_bound to configure a threshold at which data is unloaded to prevent memory shortage.
  • You can set this parameter with the following command:
    ALTER SYSTEM ALTER CONFIGURATION('global.ini', 'SYSTEM') SET ('memoryobjects', 'unload_upper_bound') = '<value>' WITH RECONFIGURE
  • The value (in byte) of this parameter can be set using the following rule, based on the recommendation in SAP Note 2600030: 
    MAX(column store size * 1.2, 50 % of global allocation limit)
     
• Alternatively you can release memory via the hdbcons command resman shrink -s. See SAP Note 2301382 for details.

Remark:

The parameter unload_upper_bound does not specifically unload the objects of the Pool/L/llang/Debuggee allocator only, but will start shrink activities if the total memory allocation is above the threshold. But cached HEX execution plans are evicted with highest priority. If you set the parameter as a workaround, please make sure that this setting will be kept until your HANA is upgraded to the revision which contains the new enhancement.