How to use correctly statement_memory_limit_treshold?

[#1]

Hi, Lately, we are facing memory issues related to huge consumption of memory caused by Webis executed by end users in our BI Platform via BI Launchpad.

We have two class of users:

- BI dept, which are who design reports (Webi Rich client)

- End users, who execute those reports for relevant information.

For the first ones, who are designing via Webi but also from Hana Modeler, I had limited the maximum memory consumption at 30GB.

I used Hana Studio, I got into Users and for each 5 users, I had used the "User Parameter" tab to limitate the consumption using the parameter STATEMENT MEMORY LIMIT and filling the value to 30. For the second ones, the end user I need to have a different approach to the solution. They are about 500 users of HANA DB that are requesting information via webis (BI Launchpad).

They don't have any limitation in Webi for selecting huge amount of data (for example, they can ask for information about the last two years without receiving any advise that maybe will by problems in our systems). So, my company doesn't want to put a limitation in Webi, and I, as a BASIS Admin, I need to control those requests using a parameter. I'd found that there was a parameter called statement_memory_limit_treshold... so, apparently, that parameter will solve my problem of generating a OOM.

SAP documentation at Help SAP says about this parameter: Respect statement_memory_limit only if total memory usage is high: Parameter statement_memory_limit is respected only if total used memory exceeds the global allocation limit by statement_memory_limit_threshold percent. Default value is 0% (of the global_allocation_limit, so statement_memory_limit is always respected).

I'd asked directly via Incident but the answer that I'm getting doesn't help me too much.

My question is: is this parameter a good way to solve the OOM's?

When a person from the first group (which have each one the parameter statement memory limit to 30) tries to overpass the 30GB they get a message that the request is not possible and I get a composite oom trace which gives me the advise.

But maybe, having a treshold parameter it would be better, because if I configure the treshold, the limit of requesting users would be flexible depending on the memory that had our HANA in that moment. For example, if we are at 70GB from our 128GB, maybe the 30GB parameter could be a little more highers if I had used the treshold instead of limiting to "just" 30GB.

Is it okay?

So... If this would be a good solution, which treshold should I configure? 90%? 90% of 128GB are 115GB.

Maybe this parameter would be fine. If we are starting from 70GB of used memory the user will can run a webi that requires up to 45GB??

Another question that I had, related to this one, is it if there is any SQL or any Hana Studio view that helps me to see how GB consumes a Webi launched in our BO. I can see the tab SQL Plan Cache that seems to be ok, because we had paralelitzation in our BO, and when BO asks to HANA we have a paralelitzation of 8 requests at the same time. I need to see globally for that webi that are up to 8 paralelitzations, in which moment it consumes the maximum amount of memory. Thanks for your collaboration David

[bryan_bingay]

We used the patterns found in this HANA guide, Setting a Memory Limit for SQL Statements - SAP Help Portal. We found that none of our reports consume more than 50GB.

[david_fernndez2]

Hi Bryan,

Thank you very much for your help. I appreciate it.

David

[bryan_bingay]

Hi David, there is a lot to talk about here. I will do my best to share how we are doing it. We had a similar problem with Webi and 3rd party applications that connect to HANA, running it out of CPU and memory. We decided not to set a global allocation limit since this will affect the applications like ERP, CRM, and BW. Instead, we have all our HANA users set with a statement memory limit of 50GB. We also have adapted workload classes to control CPU and Memory. We have 2 classes called VIP and Bouncer, you can name them anything you want. VIP pretty much gets everything and Bouncer is limited. We then create mappings and assign them to the workload classes. Mappings let you control by application name, DB user. There are other ways to configure the mappings but this is what we have done. I hope this helps, when HANA runs out of memory it is a bad situation to be in :-).

Bryan

Managing Workload with Workload Classes - SAP Help Portal

2222250 - FAQ: SAP HANA Workload Management - SAP ONE Support Launchpad

[david_fernndez2]

Thanks Bryan for your help.

How you had analyzed and decided to set up the maximum of 50GB per user? As a basis, it's hard to decided how maximum memory a report launched from BI Launchpad should be.

I will analyze the workload classes, it could be a good way to separate and control.