1. Resident memory:
Physical memory: the actual RAM of the system attached on the motherboard.
To check the total physical number in SAP HANA:
----------------------------------------------------------------------------------------------------------------
DO BEGIN
DECLARE V_INSTANCE_NAME varchar(20);
select t1.VALUE into V_INSTANCE_NAME from "SYS"."M_SYSTEM_OVERVIEW" t1
where UPPER(t1.NAME) = 'INSTANCE ID';
select V_INSTANCE_NAME as Instance_id,
round((FREE_PHYSICAL_MEMORY + USED_PHYSICAL_MEMORY)/1024/1024/1024, 3) as "Total Physical memory resource GB size"
from PUBLIC.M_HOST_RESOURCE_UTILIZATION;
END;
------------------------------------------------------------------------------------------------------
Resident memory: the real physical memory that processes utilize in the current state.
The below query give:
how much resident memory is used by the HANA database.
----------------------------------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------------------------------------
*resident memory used outside HANA (OS, 3rd party processes) is typically quite small, not exceeding 2 GB.
in case, to check free physical RAM size using current. Just to crosscheck any other process apart from SAP HANA is consuming more RAM.
the below Linux command, need to run at OS level:
--------------------------------------------------------------------------------------------------------------------
FREE_LINUX_MEMORY=`vmstat -s | grep "free memory" | awk '{print $1}'`
RESULT=`echo "scale=3; $FREE_LINUX_MEMORY / 1024 / 1024" | bc -l`
echo "Free physical memoy : $RESULT GB"
-------------------------------------------------------------------------------------------------------------------
* DBACockpit does currently not consider shared memory as part of the resident memory.
Case -2:
2.If processes outside the database are not contributing significantly to memory usage, 2 cases have to be distinguished in general:
1. An 'out of memory' error has already occurred. - https://launchpad.support.sap.com/#/notes/1984422
What is OOM:
The memory requirements of SAP HANA exceed the available memory resources or a configured memory limit. When SAP HANA requires additional memory and is not able to allocate new memory or reclaim memory otherwise, an out-of memory (OOM) dump is generated and the action is terminated.
* By default OOM dump is written once in 24HR.
*you may modify the parameter oom_dump_time_delta .
Major reasons for causing OOM:
* Expensive queries materializing results,
*too many/too large loaded tables,
*too large internal structures/system caches,
*blocked garbage collection by long-running cursors.
* too many sessions, connections, transactions, statements running.
HOW to access OOM file?
1) Go to Administration -> Diagnosis Files;
2) Search for the Key word - OOM.
3) Consider index server file first.
4)Double click on the file.
5) Show Entire File - option need to select.
How to resolve OOM?
* If multiple OOM dump files are generated you should start with analyzing the index server dump file, because in most cases the index server is responsible for the highest memory consumption.
* If you use the Diagnosis Files tab in SAP HANA Studio you have to make sure that the complete file is displayed (“Show Entire File”), otherwise some information can be missing.
The dump file typically contains the following sections that contain useful information for analyzing OOM issues:
[BUILD] Build information
[THREADS] Running threads
[STACK_SHORT] Short call stacks and pending exceptions of all threads
[PROCESS_INFO] Process information
[OS_MEMORY] Operating system information about memory
[MEMORY_OOM] Information about current out of memory situation
[STAT] Statistical data
Key points:
* time stamp of OOM error.
* version number.
* search for the key word - failed to allocate.
* current callstack - is the cause for OOM.
* global_allocation_limit -
* Top allocators
inclusive_size_in use - including of all sub allocators
exclusive size in use - including of only main allocators of the heap memory.
Generally 3 kinds of situations in OOM:
1. GAL is less then required memory - need to Analysis - * if the table is row table, can convert in to column store table.
* Check the size of table.
* Table partition.
* all other possibilities to increase the performance of access the table.
* At last, we can consider to increase the size of RAM.
2.if GAL is 1000GB
memory required - 975GB
failed to allocate - 25GB
* there is high probability to avoid the OOM in the next occurance.
* Can ignore the OOM in this case.
3. IF some cases, even though the size of GAL is more then the size of required memory size.
in this case also, OOM will created -
GAL - 1000GB
failed to allocate - 0GB
Analysis - check all the OOM occurred at the same time.
- in other case, if the above case is not cause. Then due to the case of previous SQL queries are not been removed from the memory.
-----------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
Case 3
2.An 'out of memory' error was not yet triggered, but further investigation on the current memory usage of the database might be required.
1) Go to Administration -> Diagnosis Files;
2) Select "Diagnosis Information -> Collect...";
3) Set the option 'Create and collect one or multiple sets of runtime environment (RTE) dump file(s)';
4) Set the hosts, amount of samples (sets) and frenquency on which the runtime dumps will be recorded;
5) Finish.
case 4:
Top down approach:
Which HANA process on which host is using most of the memory?
SELECT TOP 3 HOST, PORT, SERVICE_NAME, TOTAL_MEMORY_USED_SIZE
FROM M_SERVICE_MEMORY
ORDER BY TOTAL_MEMORY_USED_SIZE DESC
Case 5:
So far, the sum of used heap and shared memory has been determined. Now, this total has to be split.
Memory that can be accessed by different processes, e.g.:
- Specific row store components (tables, catalog, free)
- Nameserver topology
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
[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)
used (U)
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.
CASE 7:
-----------------------------------------------------------------------------------------------------------------------------------
Change the Global Memory Allocation Limit
The SAP HANA database preallocates a pool of memory from the operating system over time, up to a predefined global allocation limit. You can change the default global allocation limit in the global.ini configuration file. SAP HANA One is pre-configured with 60.5 GB of memory in global_allocation_limit.
Prerequisites
Context
Procedure
The results of shared memory creation can then be viewed on OS level using the ipcs command.
Reason:
Row store grows by allocating a 64MB memory segments and shrinks by freeing empty segments.A segment is internally divided into fixed-size pages.
When a row store table requires more memory to store records, the table takes a free page from existing segments. If no segment has a free page, a new segment is allocated.
Deleting a large number of records may result in a number of sparse segments. In such a case, row store reorganization can be performed for memory compaction.
The pages in sparse segments are moved to other segments and the resultant empty segments are freed.
When to reorganize the row store
Row store reorganization is recommended, if the allocated row store table size is > 10GB and free page ratio is > 30%.
| SELECT HOST, PORT, CASE WHEN (((SUM(FREE_SIZE) / SUM(ALLOCATED_SIZE)) > 0.30) AND SUM(ALLOCATED_SIZE) > TO_DECIMAL(10)*1024*1024*1024) THEN 'TRUE' ELSE 'FALSE' END "Row store Reorganization Recommended", TO_DECIMAL( SUM(FREE_SIZE)*100 / SUM(ALLOCATED_SIZE), 10,2) "Free Space Ratio in %",TO_DECIMAL( SUM(ALLOCATED_SIZE)/1048576, 10, 2) "Allocated Size in MB",TO_DECIMAL( SUM(FREE_SIZE)/1048576, 10, 2) "Free Size in MB" FROM M_RS_MEMORY WHERE ( CATEGORY = 'TABLE' ) and ( ALLOCATED_SIZE > 0 ) GROUP BY HOST, PORT; |
* Check ROW store Reorganization recommended column.
* Free space ratio column
need to consider.
If the result for Reorganization Recommended is TRUE, then row store memory can be reclaimed after row store reorganization.
How to estimate the amount of reclaimable memory
To estimate the maximum possible amount of memory that can be reclaimed, you can use below command:
| SELECT HOST, PORT, FREE_SIZE/1024768 "Estimated Maximum Memory Saving in MB" FROM M_RS_MEMORY WHERE CATEGORY IN ('TABLE') |
To below process will work for SAP HANA 2.0 >=SPS04:
- Online Row Store Reorganization v2
- Connect to the system
- Execute Online Row Store Reorganization using commands:
- ALTER SYSTEM RECLAIM DATA SPACE
or
- CALL REORGANIZE_ROWSTORE('REORGANIZE')
- To monitor the progress, check the check the monitoring view M_JOB_PROGRESS, check the Job Progress Tab in Performance Tab of the HANA Studio, or use the JobProgress in Show Statement Library in Cockpit.
- Check the indexserver trace files after row store reorganization is done. The trace of Online Row Store Reorganization is separately managed in indexserver_<hostname>.<port>.row_store_reorg.<sequence>.trc
- [000000000000][0] 0000-00-00 00:00:00.000000 i RowStoreReor Executor.cc(00033) : ==== Reorganize started ====
- [000000000000][0] 0000-00-00 00:00:00.000000 i RowStoreReor Executor.cc(00241) : Number of target segments: 0
- [000000000000][0] 0000-00-00 00:00:00.000000 i RowStoreReor Executor.cc(00242) : Expected memory saving: 000 (MB)
- …
- [000000000000][0] 0000-00-00 00:00:00.000000 i RowStoreReor Executor.cc(00518) : PostReorg: emptied 0 segments
- Check the memory fragmentation status after reorganization using:
- CALL REORGANIZE_ROWSTORE('DISPLAY_MEMORY_STATUS')
For offline Row Store Reorganization:
please refer Snote - 1813245
9.1
* In most cases, the process of interest is the index server, since it usually allocates the greatest share of the available memory.
the below query is used to show inclusive size to exclusive size:
Pool/parallel/ihm 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. Pool/itab
