In this exercise, I’ll give a walkthrough on how to perform a top down investigation on high & peak HANA memory usage and its associate top memory consumers with the aid of HANA SQL scripts attached to SAP Note 1969700 – SQL Statement Collection for SAP HANA and Statistics data (History / Current) collected by ESS / SSS (Mandatory. Recommended retention = 49 days)
I’m taking below example where I was asked to perform a memory analysis on a productive system where there was a spike of peak memory usage, around 5.9TB.
Since the system is running on scale out environment, I will breakdown the analysis of each memory usage of each node:
Below we noticed that the node01 and node02 are one of those nodes with high peak used memory.
Thus, in this content, I’ll show the memory analysis on node01 and node02 by using the 3 simple questions as quick start, where it provides us a clearer direction moving forward:
Node01:
First, we need to find out the specific day/ time the peak memory happened.
For my case, I knew the peak memory was occurred last week, and hence, I’m using the past week timestamp and below SQL script to figure the exact day/time of the peak memory.
select top 500 HOST, SERVER_TIMESTAMP,round(INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) as "Peak Used GB" from _SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS where HOST = ‘node01’ and SERVER_TIMESTAMP between ‘03.07.2017 00:00:01’ and ‘10.07.2017 23:59:59’order by "Peak Used GB" desc
The result shown that peak memory happened on 7th Jul, around 10:20 – 10:55AM
In addition, you can also utilize script HANA_Resources_CPUAndMemory_History_1.00.74+_ESS attached to note 1969700 if you want to further break down the memory usage of the day: –
( SELECT /* Modification section */ TO_TIMESTAMP(‘2017/07/07 08:00:01’, ‘YYYY/MM/DD HH24:MI:SS’) BEGIN_TIME, TO_TIMESTAMP(‘2017/07/07 23:59:59’, ‘YYYY/MM/DD HH24:MI:SS’) END_TIME, ‘node01’ HOST, ‘ ‘ ONLY_SWAP_SPACE_USED, -1 MIN_MEMORY_UTILIZATION_PCT, -1 MIN_CPU_UTILIZATION_PCT, ‘X’ EXCLUDE_STANDBY, ‘TIME’ AGGREGATE_BY, /* HOST, TIME or comma separated list, NONE for no aggregation */ ‘TS30’ TIME_AGGREGATE_BY, /* HOUR, DAY, HOUR_OF_DAY or database time pattern, TS for time slice, NONE for no aggregation */ ‘TIME’ ORDER_BY /* TIME, HOST */ FROM DUMMY
We’ve narrowed down the time where peak memory happened, now let’s investigate what were those top memory components used on node01 with below script:
– HANA_Memory_TopConsumers_History_1.00.90+_ESS with the selection specific on time 09:00 – 10:35 on node01. You may want to adjust the modification section based on your situation.
( SELECT /* Modification section */ TO_TIMESTAMP(‘2017/07/07 09:00:00’, ‘YYYY/MM/DD HH24:MI:SS’) BEGIN_TIME, TO_TIMESTAMP(‘2017/07/07 10:35:00’, ‘YYYY/MM/DD HH24:MI:SS’) END_TIME, ‘node01’ HOST, ‘%’ PORT, ‘%’ SCHEMA_NAME, ‘%’ AREA, /* ROW, COLUMN, TABLES, HEAP, % */ ‘%’ SUBAREA, /* ‘Row Store (Tables)’, ‘Row store (Indexes)’, ‘Row Store (Int. Fragmentation)’, ‘Row Store (Ext. Fragmentation)’, ‘Column Store (Main)’, ‘Column Store (Delta)’, ‘Column Store (Others)’ or ‘Heap ()’ */ ‘%’ DETAIL, /* Name of table or heap area */ ‘ ‘ ONLY_SQL_DATA_AREAS, ‘ ‘ EXCLUDE_SQL_DATA_AREAS, 200 MIN_TOTAL_SIZE_GB, ‘USED’ KEY_FIGURE, /* ALLOCATED, USED */ ‘%’ OBJECT_LEVEL, /* TABLE, PARTITION */ ‘ ‘ INCLUDE_OVERLAPPING_HEAP_AREAS, ‘DETAIL’ AGGREGATE_BY, /* SCHEMA, DETAIL, HOST, PORT, AREA, SUBAREA */ ‘NONE’ TIME_AGGREGATE_BY /* HOUR, DAY, HOUR_OF_DAY or database time pattern, TS for time slice, NONE for no aggregation */
*The output of the above script gives us an overview of top memory components used, but not the total memory used as allocated shared memory is not included and HOST_HEAP_ALLOCATORS only contains allocators with INCLUSIVE_SIZE_IN_USE > 1 GB, so the sum of used memory displayed is smaller than expected. Don’t be surprised if the total memory on top consumer returned doesn’t match the peak memory usage.
From above, we’ve identified the top contributors were Pool/PersistenceManager/PersistentSpace/DefaultLPA/*, and we know the functionality of this heap allocator is to cache virtual file pages to enhance IO related performance (savepoint, merges, LOB, etc) and make better use of available memory. Thus, once used, they will remain in memory, be counted as "Used memory", and HANA will evict them only if the memory is needed for other objects, such as table data or query intermediate results. This is the general design of HANA and not critical. Our system is not affected by any bugs since we are on rev122.09 and memory should be reclaimed when needed as we don’t see any OOM trace on the system.
You can always refer to 199997 – FAQ: SAP Memory to have better overview on the purpose of each memory components/ allocators.
With above walkthrough, it should give you a clearer picture on how to:
Next, I’ll use the same approach to perform the peak memory RCA on node02
Node02
I’m using below SQL to find out the when’s the peak memory happened, time range from the last system restart until current: 18.06.2017 – 10.07.2017
