Before proceeding with analysing the different HANA specific memory
areas, it has to be clarified that indeed the database and not processes
running outside the SAP Software are responsible. This involves
calculating how much resident memory is used by the HANA database. On
the HANA appliance, resident memory used outside HANA (OS, 3rd party
processes) is typically quite small, not exceeding 2 GB.
HANA Studio and
DBACockpit provide 2 different views on the resident memory:
HANA Studio:
The following SQL statements are behind this output:
'Database Resident':
SELECT SUM(PHYSICAL_MEMORY_SIZE) FROM M_SERVICE_MEMORY
'Total Resident':
SELECT T1.HOST, T1.USED_PHYSICAL_MEMORY + T2.SHARED_MEMORY_ALLOCATED_SIZE
FROM M_HOST_RESOURCE_UTILIZATION AS T1
JOIN (SELECT M_SERVICE_MEMORY.HOST, SUM(M_SERVICE_MEMORY.SHARED_MEMORY_ALLOCATED_SIZE) AS SHARED_MEMORY_ALLOCATED_SIZE
FROM SYS.M_SERVICE_MEMORY
GROUP BY M_SERVICE_MEMORY.HOST) AS T2
ON T2.HOST = T1.HOST;
Since the difference between 'Total Resident' and 'Database Resident' is well below 2 GB, there is no indication that processes outside the database contribute significantly to memory consumption.
DBACockpit:
This is effectively the output of the following statement:
SELECT HOST,
ROUND(USED_PHYSICAL_MEMORY/1024/1024/1024, 2) AS "Resident GB",
ROUND((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024, 2) AS "Physical Memory GB"
FROM PUBLIC.M_HOST_RESOURCE_UTILIZATION
DBACockpit does currently not consider shared memory as part of the resident memory, hence the difference to what
HANA Studio reports.
If processes outside the database are not contributing significantly to memory usage, 2 cases have to be distinguished in general:
- An 'out of memory' error has already occurred.
- An 'out of memory' error was not yet triggered, but further investigation on the current memory usage of the database might be required.
For case (1), the foundation for further analysis is the oom trace file which follows the naming convention <processname>_<hostname>.<number>.rtedump.<number>.oom.trc (for example indexserver_hdbnode1.39503.rtedump.72290.oom.trc).
In case (2), such a trace file is not yet available and an rte dump has to be generated which has a structure similar to the oom trace. SAP note
1813020 contains the necessary instructions regarding how to get the rte dump. For case (2), further analysis can also be done 'top-down', making use of the information provided by the different administrative frontends (HANA Studio / DBACockpit) and monitoring views.
SQL Statement:
SELECT TOP 3 HOST, PORT, SERVICE_NAME, TOTAL_MEMORY_USED_SIZE
FROM M_SERVICE_MEMORY
ORDER BY TOTAL_MEMORY_USED_SIZE DESC
DBACockpit:
HANA Studio:
The values in the 'Used Memory' column are calculated in the following way:
SELECT ( (CODE_SIZE + SHARED_MEMORY_ALLOCATED_SIZE + HEAP_MEMORY_USED_SIZE ) / 1024 / 1024) AS "USED MEMORY"
FROM M_SERVICE_MEMORY
It is important to note that even though the column is called 'Used Memory', the overall allocated shared memory is used for the calculation and not the value of SHARED_MEMORY_USED_SIZE. The reason for this is that the a shared memory segment (see point 4) can only be completely attached to the address space of the process and not just the used part.
Due to changes in the calculation of CODE_SIZE, there might be cases where the used memory increased after an revision upgrade. This can be seen in the following example:
SELECT SERVICE_NAME, CODE_SIZE
FROM M_SERVICE_MEMORY
rev49:
| SERVICE_NAME |
CODE_SIZE |
| nameserver |
627.855.360 |
| preprocessor |
687.079.424 |
| indexserver |
733.585.408 |
| statisticsserver |
727.781.376 |
| xsengine |
725.540.864 |
rev54 and later:
| SERVICE_NAME |
CODE_SIZE |
| nameserver |
4.498.731.008 |
| preprocessor |
4.526.575.616 |
| indexserver |
5.256.380.416 |
| statisticsserver |
4.956.565.504 |
| xsengine |
5.230.637.056 |
Before HANA revision 54, the code size was incorrectly reported. This problem is explained in SAP note
1826139. The majority of the CODE_SIZE is however shared between the processes, so the assumption that in total 20 GB of memory is consumed by CODE_SIZE is therefore not correct. Starting with HANA SP 06, an instance wide CODE_SIZE will be reported. Additionally, also the license check will then consider this new instance wide CODE_SIZE (which is expected to be around 5 GB).
Due to changes in the calculation of the code size (see above) the calculation for 'Used Memory' was changed. It can now be retrieved with the following statement:
SELECT INSTANCE_TOTAL_MEMORY_USED_SIZE
FROM sys.m_host_resource_utilization
Starting with SP6, instace_total_memory_used_size is exposed via the view m_host_resource_utilization.
So far, the sum of used heap and shared memory has been determined. Now, this total has to be split:
SQL Statement:
SELECT TOP 3 HOST, PORT, SERVICE_NAME, HEAP_MEMORY_USED_SIZE, SHARED_MEMORY_USED_SIZE, TOTAL_MEMORY_USED_SIZE
FROM M_SERVICE_MEMORY
ORDER BY SHARED_MEMORY_USED_SIZE DESC
HANA Studio:
If it is shared memory, proceed as outlined in section 'Shared Memory Usage', otherwise, go to section 'Heap memory usage'.
The following sections of those trace files are usually relevant:
[IPMM_MEMORY]
The first part of this section lists the local (heap) memory the processes that make up the HANA database are currently using:
[0] PID=34884, SId=42309944, compactors active, alive, process name: hdbnameserver
AB=2220466176b (2.06gb), UA=0b, U=2015851859b (1.87gb), FSL=0b, PAL=487738426982b (454.24gb), TPAL=2220466176b (2.06gb), W=100
[1] PID=35049, SId=42310545, compactors active, alive, process name: hdbpreprocessor
AB=365772800b (348.82mb), UA=0b, U=362430594b (345.64mb), FSL=0b, PAL=487738426982b (454.24gb), TPAL=365772800b (348.82mb), W=100
[3] PID=35094, SId=42310846, compactors active, alive, process name: hdbstatisticsse
AB=17623138051b (16.41gb), UA=0b, U=14624613181b (13.62gb), FSL=268435456b (256mb), PAL=27096579276b (25.23gb), TPAL=17757355779b (16.53gb), W=100
[4] PID=35114, SId=42310947, compactors active, alive, process name: hdbxsengine
AB=2270855168b (2.11gb), UA=0b, U=2136436039b (1.98gb), FSL=0b, PAL=487738426982b (454.24gb), TPAL=2270855168b (2.11gb), W=100
[5] PID=33976, SId=171197412, compactors active, alive, process name: hdbindexserver
AB=240495694077b (223.97gb), UA=0b, U=260528715346b (242.63gb), FSL=0b, PAL=487738426982b (454.24gb), TPAL=270062153728b (251.51gb), W=100
For each process, the allocation limit (PAL), the amount of memory currently allocated (AB) and used (U) are displayed. An AB value that is significantly larger than an U value does not necessarily mean a problem: If necessary, allocated, but not used memory will be released. Of particular interest are lines where AB, U and PAL have approximately the same value. This particular process has then reached its allocation limit. Further analysis based on the output of M_HEAP_MEMORY in section [PROCESS_INFO] has to be done then.
The second part of section [IPMM_MEMORY] contains information regarding the shared memory:
GLOBAL_MAX_ALLOCATION_LIMIT=487738426983b (454.24gb), cached sum of allocation limits=487604209255b (454.11gb)
#checks for cached allocation limit sum=838859, #terminated processes since last reset=7
#processes=5, sum of temp process allocation limits=292676603651b (272.57gb), cached sum=487604209255b (454.11gb), weight sum=500, cached weight sum=500
SHARED_MEMORY=194927605604b (181.54gb), temp shared memory allocation limit=194927605604b (181.54gb)
IPMM reservation=0b, emergency reservation=134217728b (128mb)
112 early exits since creation of IPMM, provide-memory-counter=50279
Provide memory 50267 failed, only 109174531b were free.
Users respected in shm calculation: [302]
Shared Memory is used for various purposes. For practical purposes, it is sufficient to assume that mainly row store tables occupy shared memory. In case of the 'early exits since' output in the oom trace, the error was triggered by the inability to further increase the TPAL (temporary allocation limit) of the failing process. The allocation limit of a process is not set to the maximum right at process start, but increased (and decreased) over time. In this specific case, 'sum of temp process allocation limits' + SHARED_MEMORY exceeds the GLOBAL_MAX_ALLOCATION_LIMIT which consequently causes the error.
[MEMORY_OOM]
Before any detailed analysis is done, the information from section [MEMORY_OOM] has to be reviewed as well. It might be the case that the request was that large that it was anyway impossible to fulfill it:
[MEMORY_OOM] Information about current out of memory situation:
OUT OF MEMORY occurred.
Failed to allocate 2405843009873693952 byte.
A known issue regarding such large request is solved with revision 50. In case this occurs in newer revisions, please contact SAP Support.
The following checks can then be done based on the information available so far:
Contrary to heap memory, which is allocated using the 'malloc' system call, shared memory is provided using the 'shmget' call. The results of shared memory creation can then be viewed on OS level using the ipcs command. The following approach can be used to display the shared memory of one particular HANA process on operating system side:
- Get the process pid: ps -ef | grep <HANA process>
- ipcs -p | grep <pid> then displays all segments that were created by this particular process:
ipcs -p | grep 4221
86999065 hanadm 4221 4221
87064602 hanadm 4221 4221
87130139 hanadm 4221 4221
87195676 hanadm 4221 4221
87261213 hanadm 4221 4221
87359519 hanadm 4221 4221
88309819 hanadm 4221 4221
88375356 hanadm 4221 4221
88440894 hanadm 4221 4221
- The size of a particular segment can then be further examined using the command ipcs -m -i <id>
ipcs -m -i 86999065
Shared memory Segment shmid=86999065
uid=60000 gid=100 cuid=60000 cgid=100
mode=01600 access_perms=0600
bytes=8929752 lpid=4221 cpid=4221 nattch=1
att_time=Tue May 14 14:09:13 2013
det_time=Tue May 14 14:09:13 2013
change_time=Tue May 14 14:09:13 2013
The sum of all those shared memory segments is then equivalent to the output of the statement:
SELECT SHARED_MEMORY_ALLOCATED_SIZE
FROM M_SERVICE_MEMORY
WHERE PROCESS_ID = '4221'
- The row store is organized in 64MB segments. Deleting large number of rows can lead to sparse segments and unnecessary use of memory. SAP note 1813245 outlines problems and strategies to solve this problem.
- Indirectly, high memory usage of the row store can cause problems when parameter client_distribution_mode is set to 'off' in distributed environments. When default value 'statement' is used, the statement is sent to the node where the table is located. With the setting 'off', the statement might then also be directed to the master node. Since all row store tables are usually located on the master node and consequently, the row store uses a lot of memory, materializing a large amount of data on the master node (from a table that is actually located on another node) can then simply be too much. Therefore, in case of problems with resolving internal and external hostnames, parameter client_distribution_mode should not be turned off, but instead the recommendations from SAP note 1780950 are to be followed.
- Too many large tables were created as row store tables. The largest tables currently contained in the row store can be retrieved by the following SQL Statement:
SELECT TOP 50 *
FROM M_RS_TABLES
ORDER BY (ALLOCATED_FIXED_PART_SIZE +
ALLOCATED_VARIABLE_PART_SIZE) DESC
It is however important to keep in mind that in many scenarios, switching a table from rowstore to columnstore and vice versa must not be done. This applies to Suite on HANA / BW systems which are delivered with particular settings for different table types. At any case, it makes sense to check whether the system is currently adhering to the recommended configuration or not. SAP note
1659383 contains a list of tables that are supposed to be in the rowstore and a check that is based on this list is part of report
RSDU_TABLE_CONSISTENCY. This report also contains a repair option when a deviation is recognized.
If it can be ruled out that the rowstore is responsible for high memory consumption, the heap memory allocation of the individual processes has to be analyzed next. In most cases, the process of interest is the indexserver, since it usually allocates the greatest share of the available memory. The following possibilities exist to do this:
- If an oom error has already occurred or a rte dump was explicitly triggered, the content of the system view M_HEAP_MEMORY (section[PROCESS_INFO]) that was written into the trace file can be used.
- If an ad-hoc analysis of the heap memory consumption should be done, the view M_HEAP_MEMORY can be queried directly, for example using HANA Studio.
In case of (1), the output can be directly copied into a csv file and further be analyzed for example using excel. Each line represents an allocator, which is a facility for allocating heap memory in HANA. The following statements can be used on M_HEAP_MEMORY then:
SELECT TOP 15 MS.HOST, MS.SERVICE_NAME,MH.CATEGORY, MH.INCLUSIVE_SIZE_IN_USE, MH.EXCLUSIVE_SIZE_IN_USE
FROM M_HEAP_MEMORY MH, M_SERVICES MS
WHERE MH.PORT = MS.PORT AND
MH.HOST = MS.HOST AND
MS.SERVICE_NAME = 'indexserver'
ORDER BY 4 DESC
Of interest are those pools that are on the top of the list and have an inclusive size close to the exclusive size. Depending on the pool names, the following known issues exist:
There are cases where this allocator used 90% of the available memory. If such a problem is observed, the system should be upgraded to revision 54 when available.
This allocator is needed for the intermediate result of join operation and translation table used for join operation. This could indicate use of a suboptimal model.
A known issue is associated with high memory usage that is solved with revision 53. FEMS compression is used in BW systems to keep the size of the dataset that is transferred to the application server as small as possible.
This is the allocator which keeps the complete resultset in uncompressed form. A known issue has been solved with revision 55 which addresses memory used by this allocator not released after oom situations, thus steadily increasing over time. The details are documented in SAP note
1852425.
Translation tables are created for caching join column entries of join engine queries. The number of translation tables that are kept in memory is determined by the the following parameter in indexserver.ini:
[joins]
translator_cache_size = n
The default value is 2000. In order to verify how many translation tables currently exist in the system, the following statement can be used:
select * from sys.M_DEV_JOIN_TRANSLATION_TABLES_
order by TRANSLATION_TABLE_MEMORY_SIZE desc
This statement sums up the memory usage of those tables:
select host, port, transtable_memory from sys.m_dev_joinengine_
In case of excessive memory usage for this allocator, the parameter can be set to 500 as a starting point. Lowering the value can have an impact on query performance though. However, in live systems where this was done, no negative side effects were observed sofar.
A memory leak was introduced with revision 54 that can lead to a huge growth of this allocator when BW queries containing lots of OR terms instead of IN lists are executed. A possible workaround from application side is the implementation of SAP note
1786777. From HANA side, the problem is solved starting with revision 60 (HANA SP6). See also SAP note
1880274.
Even though the statisticsserver can also run into oom errors, it should not consume a large share of the available memory. The statisticsserver is supposed to have a much smaller allocation limit compared to the indexserver. This following two configuration parameters are relevant:
statisticsserver.ini
-> memorymanager
allocationlimit
minallocationlimit
The allocation limit of the statisticsserver is either a percentage of the availably RAM or just the value of minallocationlimit, depending on what is the bigger value. In case of oom errors that indicate that the statisticsserver has reached its process allocation limit, it can make sense to increase the allocationlimit to 10% or 15% to have at least a workaround for the time being. However, further analysis regarding the heap memory usage has to be conducted (see above). In the oom trace file, such a situation would look like this:
[3] PID=23840, SId=320543005, compactors active, alive, process name: hdbstatisticsse
AB=6755876864b (6.29gb), U=7135415505b (6.64gb), FSL=0b, PAL=6772669235b (6.30gb), TPAL=6772669235b (6.30gb), W=100
AB, PAL and TPAL are on the same values. Also in this case, further investigation regarding the involved pools is necessary, either using the information contained in the trace files or by using the following SQL statement:
SELECT TOP 15 MS.HOST, MS.SERVICE_NAME,MH.CATEGORY, MH.INCLUSIVE_SIZE_IN_USE, MH.EXCLUSIVE_SIZE_IN_USE
FROM M_HEAP_MEMORY MH, M_SERVICES MS
WHERE MH.PORT = MS.PORT AND
MH.HOST = MS.HOST AND
MS.SERVICE_NAME = 'statisticsserver'
ORDER BY 4 DESC
