This is the second part of the blog series of HANA memory usage details and changes in SPS12
For now only the “used memory” part is interesting. May be you heard about “resident memory” => this is the OS view which is a not up-to-date value in cause of the deferred garbage collection / release of memory and can’t be taken as indicator.
“used memory” consists of:
- Table Data (Row Store + Column Store + System Tables)
- Code and Stack
- Database Management / Working Space (heap and shared memory)
##############
Heap memory
##############
Content
- Column store
- Row store indexes
- Intermediate results
- Temporary structures
- SAP HANA page cache
You can check the usage of each area with SQL (see note 1969700 for details) : “HANA_Memory_TopConsumers” (statement adjustment: search for the term “modification section” and adjust AGGREGATE_BY = ‘AREA’):
| AREA | DETAIL | NUM | SIZE_GB | SIZE_PCT | CUM_SIZE_PCT |
| Column store | any | 37691 | 251.89 | 54.35 | 54.35 |
| Heap area | any | 2692 | 192.32 | 41.50 | 95.85 |
| Row store | any | 1645 | 12.58 | 2.71 | 98.56 |
| Code | any | 6 | 1.79 | 0.38 | 98.94 |
| Stack | any | 6 | 0.46 | 0.10 | 99.04 |
So the second big area behind the main data inside the CS is heap memory with about 200GB (~42% of the used memory). That is a pretty big part, isn’t it? But what is behind it? Why HANA need the heap? Ok, let’s go deeper:
SELECT
Service_name, EFFECTIVE_ALLOCATION_LIMIT,
TOTAL_MEMORY_USED_SIZE, Code_Size,Stack_size,
HEAP_MEMORY_ALLOCATED_SIZE, HEAP_MEMORY_USED_SIZE
FROM M_SERVICE_MEMORY
where Service_name='indexserver'| SERVICE_NAME | EFFECTIVE_ ALLOCATION_LIMIT |
TOTAL_MEMORY_ USED_SIZE |
CODE_SIZE | STACK_SIZE | HEAP_MEMORY_ ALLOCATED_SIZE |
HEAP_MEMORY_ USED_SIZE |
| indexserver | 658.880.535.373 | 486.267.281.081 | 1.907.929.088 | 227.573.760 | 632.883.404.800 | 472.412.924.633 |
This statement shows the values of col store + heap area as heap memory. If you talk about heap you really mean CS + page cache + Intermediate results + MVCC + Delta Merge areas. With the normal memory overview statement you are not able to determine which area belongs together besides you read this blog or study a lot of notes and guides 😉
The area to analyse are the 192GB. To go deeper we need another statement.
You can check the usage of each area with SQL “HANA_Memory_TopConsumers_1.00.85” (statement adjustment: search for the term “modification section” and adjust ‘HEAP’ AREA)
| HOST | PORT | AREA | DETAIL | NUM | SIZE_GB | SIZE_PCT | CUM_SIZE_PCT |
| any | any | HEAP | Pool/PersistenceManager/PersistentSpace/DefaultLPA/Page | 3 | 146.06 | 31.49 | 31.49 |
| any | any | HEAP | Pool/PersistenceManager/UnifiedTableContainer | 1 | 9.23 | 1.99 | 33.48 |
| any | any | HEAP | Pool/Statistics | 6 | 7.20 | 1.55 | 35.03 |
| any | any | HEAP | Pool/RowEngine/SQLPlan | 6 | 4.12 | 0.88 | 35.91 |
| any | any | HEAP | AllocateOnlyAllocator-unlimited/FLA-UL<3145728,1>/MemoryMapLevel2Blocks | 6 | 3.65 | 0.78 | 36.69 |
The most parts of the heap have a good reason to be allocated, but there is also a area which called “Pool/PersistenceManager/PersistentSpace/DefaultLPA/Page”
This one is called the SAP HANA page cache. It is in this example the biggest part with about 150GB of 192GB.
It works similar to a file system cache of an unix system. Data which are retrieved from disk will be stored in this area. When this area will be shrinked is a decision of the system itself. It can take a long time and till this happens the allocated memory will be counted as “used memory”.
But this memory will be reused in case if the memory is needed for other objects, such as table data or query intermediate results.
This speeds up the work in some cases, but due to a bug the size of this cache can be unnecessary large with Revisions 110 to 122.04.
There is a workaround:
2301382 – High “Used memory” in Pool/PersistenceManager/PersistentSpace/DefaultLPA/Page after upgrade to HANA SPS11 or a higher SPS
Rev. 110 – 122.01: regular “resman shrink” scheduling
=> I don’t recommend this one because some minutes (depends on the workload) later the allocation is high again
SAP HANA 122.02 – 122.05: Set the unload_upper_bound parameter using the generated <UNLOAD_UPPER_BOUND_COMMAND>
=> after the limit is hit an automatically shrink will be triggered
! Both are only workarounds !
=> it can have incluence of your performance because shrinking the resource container can potentially evict objects other than virtual file pages from memory, for example, table data or query intermediate results
=> It can lead to column store table unloads
A working solution was released in Rev 122.06. Currently some customers reported high usage despite a higher revision. Therefore:
On newer Revisions increased sizes of the page cache is typically caused by other effects:
- SAP Note 2427897: Increased page cache due to takeover / recovery
- SAP Note 2403124: Optimization of SAP HANA page cache usage
The following setting turns off the optimization of keeping column table main persistent pages in memory:
Disabling Page Cache
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('persistence', 'internal_caching_for_main') = 'false' WITH RECONFIGURE ;LOBs optimization
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('persistence','disposition_lob_read') = '1' with reconfigure;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('persistence','disposition_lob_read_small') = '1' with reconfigure;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('persistence','disposition_lob_write') = '1' with reconfigure;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('persistence','disposition_lob_write_small') = '1' with reconfigure;
To check out the different areas you can use hdbcons
> pageaccess a ### DynamicPageAccess ### PageType SizeCls Disposition hasRefs Count MemorySize ModifiedCount ModifiedDiskSize ConvIdxPage 256k Temporary yes 1 524984 1 262144 ConvLeafPage 256k Temporary yes 220 65484320 83 21757952 FileIDMappingPage 256k Temporary yes 175 45997000 0 0 FileIDMappingPage 256k Shortterm yes 141 37060440 1 262144 ContainerDirectoryPage 256k Longterm yes 1204 316459360 15 3932160 ContainerNameDirectoryPage 256k Longterm no 7 1839880 0 0 UndoFilePage 64k Shortterm yes 489 32387448 92 6029312 VirtualFilePage 4k InternalShortterm no 28685 137458520 84 344064 VirtualFilePage 16k InternalShortterm no 25911 442559880 81 1327104 VirtualFilePage 64k InternalShortterm no 29514 1954771248 81 5308416 VirtualFilePage 256k InternalShortterm no 20549 5401099160 52 13631488 VirtualFilePage 1M InternalShortterm no 13979 14667773288 12 12582912 VirtualFilePage 4M InternalShortterm no 6546 27460470000 0 0 VirtualFilePage 16M InternalShortterm no 5762 96674328944 0 0 VirtualFileLOBPage 4k Shortterm no 106524 510463008 0 0 VirtualFileLOBPage 16k Shortterm no 12473 213038840 0 0 VirtualFileLOBPage 64k Shortterm no 1462 96831184 0 0 VirtualFileLOBPage 256k Shortterm no 179 47048360 0 0 VirtualFileLOBPage 1M Shortterm no 9 9443448 0 0 VirtualFileLOBPage 4M Shortterm no 1 4195000 0 0 TableContainerPage 4k Longterm no 509 2439128 0 0 TableContainerPage 4k Longterm yes 126540 606379680 2 8192 TableContainerPage 4k NonSwappable no 119 570248 0 0 TableContainerPage 4k NonSwappable yes 12819 61428648 100 409600 UnifiedTableDelta 4k Shortterm no 295 1413640 0 0 UnifiedTableDelta 4k Shortterm yes 615 2947080 14 57344 UnifiedTableDelta 16k Shortterm no 123 2100840 0 0 UnifiedTableDelta 16k Shortterm yes 65 1110200 6 98304 UnifiedTableDelta 64k Shortterm no 180 11921760 0 0 UnifiedTableDelta 64k Shortterm yes 43 2847976 3 196608 UnifiedTableDelta 256k Shortterm no 584 153498560 0 0 UnifiedTableDelta 256k Shortterm yes 90 23655600 0 0 UnifiedTableDictionary 4k Shortterm no 67 321064 0 0 UnifiedTableDictionary 4k Shortterm yes 7519 36031048 8 32768 UnifiedTableDictionary 16k Shortterm yes 174 2971920 1 16384 UnifiedTableDictionary 64k Shortterm yes 32 2119424 0 0 UnifiedTableDictionary 256k Temporary yes 212 55722080 12 3145728 UnifiedTableDictionary 256k Shortterm yes 382 100404880 8 2097152 UnifiedTableDictionary 1M Shortterm yes 1 1049272 0 0 UnifiedTableDictionary 4M Shortterm yes 1 4195000 0 0 UnifiedTableMVCC 256k Shortterm yes 30492 8014517280 24 6291456 total count = 434693 total referenced count = 181215 total modified count = 680 total memory size = 146.4GB total referenced memory size = 8977.2MB total modified disk size = 74.1MB
Here we can see that the biggest part was spent by the different block sizes of VirtualFilePage. This feature was introduced with SPS11 and higher. Some specific types of persistency pages (virtual file pages) are more likely to be added to the HANA page cache to improve performance.
You can see that the VirtualFilePages are kept with disposition “InternalShortterm” and VirtualFileLOBPage with “Shortterm” which is a kind of priority.
The mechanism that evicts objects from memory when needed will consider objects with the disposition “InternalShortterm” with higher priority than other objects, such as table data and query intermediate results. This eviction happens automatically before any out-of-memory (OOM) situation.
So the new caching behaviour will not lead to OOM situations.
As you can see this new feature which can lead to an increased “used memory” is not an issue, but it is not transparent if the sizing is wrong and there is really an issue, because the alerting also reacts to the threshold of 80% (default) of a service. In the overview of the HANA Administrator or the DBACOCKPIT you will see that the memory consumption is pretty high but not why.
It also can lead to a licensing issue, because this depends on the peak memory usage.
Can there be fragmentation in the heap memory? Yes of course, but normally HANA itself takes care with the garbage collection. You can check it with command:
hdbcons 'mm poolallocator'A fragmentation up to 15% is acceptable. If you want to trigger the GC manually you can use the following command:
hdbcons 'mm gc -f'
Back to main blog
Go back to Part 1: Shared memory
Go next to Part 3: tech. changes in SPS12
####################
History
####################
V1.1 added heap fragmentation
V1.2 added new version 2301382 and some hints
V1.3 repaired destroyed pictures
V1.4 added new version of 2403124
Are you sure a solution was released in 122.05? We are in 122.05 and we have to add the parameter unload_upper_bound with the value calculated by the script but we are getting unloads, maybe we will need to increase the parameter value.
If solution was released in 122.05 which should be that solution as we have high memory issues without this parameter.
Thanks.
According to the note, the fix was added to rev 122.06
Hi Joze,
you are right. They changed the note 2301382 after writing this blog. It should be fixed since 122.06 but I got feedback from some customers which told me that it is still not fixed. Have a look at note 2403124. There you can completely disable the caching feature which can result in a lower performance.
Regards,
Jens