Skip to Content

If you have spent much time with HANA at all, then you will have encountered the Out of Memory error.

[MEMORY_OOM]  Information about current out of memory situation: (2013-12-17 16:24:39 413 Local)

OUT OF MEMORY occurred. Failed to allocate 786815 byte.

This happens, because HANA requires the data on which you’re operating, and all of the temporary space required to do aggregation and calculation, to be available in physical RAM. If there is not enough physical RAM available, your query will fail. Therefore you have three major factors:

– The amount of RAM available to the HANA system

– The volume of data on which you are operating

– The amount of temporary space required by the calculation

The first factor is fixed, and the second two are a factor of the design of your model (and also of concurrency, as each user will require their own temporary space). This is why SAP recommend you retain 50% of RAM for tables, and 50% for calculation memory. Indeed, this can vary depending on the system.

Fortunately there are several things you can do to reduce, or eliminate, Out of Memory Errors. As a bonus, every one of these things will help your overall application design and performance.

1) Upgrade to the latest HANA Revision

Newer HANA Revisions are always more memory efficient, both in how they store tables and how they process data. How much difference a particular Revision will make will depend on a number of factors, but for instance, if you use ODBC/JDBC connections and SQL for aggregations without using Analytic Views, Revision 70 may improve performance by 100x and temporary space by 100x compared to Revision 69.

In addition, Revision 70 seems to be a bit more frugal on memory, and it releases RAM back to the Operating System more aggressively than previous Revisions. Either way, take the time to update regularly – the HANA development team are constantly refining specific scenarios.

2) Migrate the Statistics Server

If you are on HANA SP07, you can now migrate the Statistics Server into the main Index Server process. You can read about this in SAP Note 1917938. This should save you around 2GB of memory, which, if you have a 16GB HANA AWS instance, will be much appreciated.

3) Never use row organized tables

HANA allows row- and column- organized tables, but you should never use row tables. You can double click on a table to check – it should say Type: Column Store. I discuss this in more detail in my blog 6 Golden Rules for New SAP HANA Developers, but the important thing for us here is that row organized tables are not compressed as well as column tables, plus they always reside in memory. You can’t unload row tables, and they are a memory hog.

4) Set your tables not to automatically load

You can configure tables in HANA not to automatically pre-load on startup using: ALTER TABLE table_name PRELOAD NONE. This is good practice in development systems, because it means you can restart the HANA system faster (as tables won’t be preloaded) and HANA uses less memory by default.

5) Partition large tables

HANA loads tables into memory, on demand, on a column-partition basis. I discuss this in some detail in this document How and when HANA SP7 loads and unloads data into memory. If you have a unpartitioned large table with a column with a lot of distinct values – I checked one of the test table in my system and found it had a 12GB column – then that entire column will be loaded into memory the moment any part of it is accessed.

If you partition a table by a sensible key (date, or an attribute like active/inactive), then only those partition-columns that are actually accessed will be loaded into memory. This can dramatically reduce your real-world memory footprint.

6) Redesign your model

HANA doesn’t have a temporary table concept like in a traditional RDBMS like Oracle or MS SQL. Instead, temporary tables required for calculations are temporarily materialized in-memory, on demand. HANA has a lot of clever optimizations that try to avoid large materializations and a well-designed model won’t do this.

There is a need for a new advanced modeling guide but the best one available for now is the HANA Advanced Modeling Guide by Werner Steyn. In particular, you should avoid using Calculation Views until you have mastered the Analytic View – always try to build an Analytic View to solve a problem first. This is because Analytic Views don’t materialize large datasets, unless you materialize the whole Analytic View.

7) Make sure your Delta Merges are working correctly

Nice spot Marcel Scherbinek, I forgot to add this. HANA keeps a compressed “main” store of column tables and an uncompressed “delta” store, for new items. Periodically, a process called mergedog combines the two in a DELTA MERGE process. This is a set of design tradeoffs that ensures fast read, and fast writes, and good compression.

Sometimes however your tables may not be configured to automerge (you can issue ALTER TABLE table_name ENABLE AUTOMERGE), and occasionally mergedog fails and you have to restart HANA. This can cause the delta store to become very large, and because it is uncompressed, this is a big issue if you don’t have much RAM.

If you double click on a table and click the Runtime Information tab, you will see two memory consumption numbers: Main, and Delta. Delta should always be much less than main during correct operation. You can right click a table any time and select “Perform Delta Merge”.

Final Words

HANA requires that your working set of tables (those tables frequently accessed), plus sufficient calculation memory for the temporary tables materialized during your query, are available in-memory. HANA will unload partition-columns of tables on a Least Recently Used basis, when it is out of memory.

If you see OOM errors and you have done (1-5) then the chances are that your model is materializing a large data set. Because temporary tables in HANA aren’t compressed, they can be 5-10x larger than the equivalent column table, which means that you can balloon large volumes of RAM. If you have a 512GB HANA system and a 50GB table, you can easily run out of RAM if you create a bad model.

It feels to me like there’s a need for a new modeling guide for OOM errors for HANA. Maybe that’s the subject of a future blog.

Have I missed anything? What are your tips?

To report this post you need to login first.

15 Comments

You must be Logged on to comment or reply to a post.

    1. John Appleby Post author

      Yep – it’s not been released yet. I guess they didn’t get the migration working for Revision 70, so we have to wait. Watch this space!

      (0) 
  1. Marcel Scherbinek

    Thanks for sharing John! Nice listing of all important (and for me some new) points.

     

    I just want to add that the delta merge can be very memory ‘intensive’ which might also lead to an OOM when you load a larger amount of data to SAP HANA during the delta merge.

     

    You mentioned the ratio for saving space by compression in the different revisions of SAP HANA. Do you also have a source what the savings by compression will be in general? (e.g. comparing a SAP system on Oracle with a SAP system on SAP HANA).

    (0) 
    1. John Appleby Post author

      Thanks, great spot. I added it in as tip #7.

       

      If you get OOM with Delta Merge then you got your partition strategy wrong. Each individual partition needs to be not too big.

       

      Mostly the different revisions are more efficient with how they handle aggregations and temporary space for calculations, though I have seen slight improvements in compression, and definite improvements with memory management.

       

      Your last question is very tough because there are so many factors. Cardinality, data types, use of Advanced Compression Option, number of indexes and aggregates in Oracle, amount of free memory, time since last reorg and Unicode all affect it.

       

      In one case I saw recently, it was 1.2:1 for Oracle to HANA for the initial tables, including Advanced Compression. Then we added Indexes, Aggregates and we ended up closer to 4:1. That’s a best-case scenario for Oracle and in most cases it’s better.

       

      John

      (0) 
  2. Mikhail Budilov

    Thanks  John, good article.

     

    Without any doubts OOM problems in will be one of the main topic in 2014 in HANA spaces. Current OOM handling is not applicable for Prod systems.

     

    I heard that sap development working on it.

    (0) 
  3. Srinivas Kakarla

    Thanks John,

     

    For the valuable insights. Yes, updating to revision 70 reduced the OOM issues with re-structured statistics server consuming very less memory.

     

    Looking forward for the OSS note 1917938.

     

     

    Srinivas K.

    (0) 
  4. Stapleton Matthew

    It looks like the statistics server migration doesn’t work with Rev 72 so it might be an idea to put a big warning about needing to upgrade to Rev 74 before attempting the migration.  Also, if migration is attempted on Rev 72, and then Hana restarted, the legacy statistics server starts, and then says it is disabling itself because the new StatisticsServer is active, but stays in ram.  Is there any way to restart the migration after upgrading to Rev 74 without restoring from backup?

    (0) 
  5. Sunthar Supparmaniam

    Hi John,

     

    Just to clarify this statement “This is why SAP recommend you retain 50% of RAM for tables, and 50% for calculation memory.”, Meaning if I having a 2TB HANA database, I should only load data up to 1 TB, and the rest leave it free as “workspace”  for calculations & aggregation?

     

    Thanx

    (0) 
    1. John Appleby Post author

      Correct.

       

      To be honest as of HANA SPS08, this requirement seems to be slowly relaxing. We more often see customers push to 60:40 or 70:30.

      (0) 
      1. Henrique Pinto

        It all depends on how much temporary data you’re expecting to have from your parallel queries.

        For very intensive batches or for scenarios with a huge amount of concurrent users, you might want to have even over 50%. I’ve heard it was like that for the nba site, for example.

        (0) 
  6. KRISHNA YALAMANCHI

    John Appleby Great blog! Moving to the latest rev would take some time path to prod usually.

     

     

    This is what helped us avoid Out of Memory errors by configuring the memory parameters.

     

    HANA Studio –> Administration View –> Landscape

     

    /wp-content/uploads/2015/09/hana_memory_used_782439.jpg

     

    This setting on the master node for indexserver tells that the peak statement memory used is about 150 GB and it can use approximately 500 GB. It’s important to ensure that your basis team has an incident or an HANA SME review before making these changes.

     

    If you have a 5 TB HANA system one best practice is to use 2.5 TB for storage and 2.5 TB for working / calculation memory.

     

    If that is the case which means we can tune the Effecitve Allocation Limit upto 2.3 TB

     

    /wp-content/uploads/2015/09/memory_trace_782440.jpg

     

     

    Also enabling expensive_statement trace helps and it is done by tuning the below parameters in the global.ini

     

    /wp-content/uploads/2015/09/memory_tracking_782639.jpg

     

    Now if all your settings are correct you should no longer see Out of Memory(OOM) errors

     

     

    This can be validated by going to the HANA Administration View –> Diagnosis Files –> Search for dump

     

    /wp-content/uploads/2015/09/dump_782640.jpg

     

    Now Once you get the results open the Indexserver(Master Node)

     

    Out of Memory trace files are here. If you dont see an Out of Memory trace error for today / yesterday / current  week is good news.

     

    SAP – What other memory parameters have to be tuned to optimally run or is the best practice is not to tune memory parameters?

    (0) 

Leave a Reply