This is the first 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)

SAP HANA Used Memory

###############
 Shared memory
###############

Content

  • Specific row store components (tables, catalog, free)
  • Nameserver topology

Shared Memory: Size Check

To check the size of the shared memory you can execute the following statement:

SELECT 
Service_name, TOTAL_MEMORY_USED_SIZE, SHARED_MEMORY_ALLOCATED_SIZE, 
SHARED_MEMORY_ALLOCATED_SIZE/EFFECTIVE_ALLOCATION_LIMIT*100 
as SHARED_MEMORY_ALLOCATED_PCT, 
SHARED_MEMORY_USED_SIZE/TOTAL_MEMORY_USED_SIZE*100 
as SHARED_MEMORY_USED_PCT,SHARED_MEMORY_USED_SIZE 
FROM M_SERVICE_MEMORY 
where Service_name='indexserver'
SERVICE_
NAME
EFFECTIVE_
ALLO-
CATION_
LIMIT
TOTAL_
MEMORY_

USED_
SIZE
SHARED_
MEMORY_

ALLOCATED_
SIZE
SHARED_
MEMORY_

ALLO-
CATED_
PCT
SHARED_
MEMORY_

USED_
PCT
SHARED_
MEMORY_

USED_
SIZE
indexserver 658.881.925.436 485.778.131.336 11.946.427.360 1,8131 1,5872 7.710.623.336

So we have about 12GB shared memory.
You can see at the procent values that this is one of the smaller memory areas in the system, because very few programs use large blocks of shared memory.


Shared Memory: Categories

If you want to know how the shared memory is splitted, you can run the following statement:
Change the Port 30003 according your instance number 3xx03

select 
CATEGORY,ALLOCATED_SIZE,USED_SIZE,FREE_SIZE 
from "SYS"."M_SHARED_MEMORY" 
where Port=30003 
order by used_size desc
CATEGORY ALLOCATED_SIZE USED_SIZE FREE_SIZE
TABLE 11.435.474.944 7.278.641.152 4.156.833.792
CATALOG 241.467.392 230.466.424 11.000.968
PAGELIST 202.376.160 202.376.160 0
TOPOLOGY 268.368.504 2.683.686 265.684.818
INDEX 0 0 0
VERSION 0 0 0
FREE 67.108.864 0 67.108.864

You can see that the biggest part consists of the table category


Shared Memory: HANA design decision

An early SAP HANA design decision was to use “shared memory” for row-store tables (Incidentally, this memory is not really shared between processes – it is just defined as “shared memory”). As a result, when you use large row-store tables, the shared-memory footprint of SAP HANA can become very large.
If you want to reduce it just check out the row store fragmentation (1813245 – SAP HANA DB: Row store reorganization). I recommend to do this via the offline variant because it is more accurate. Since it’s guaranteed that there are no update transactions during the restart time, it achieves the maximum compaction ratio.
Before you do this check your housekeeping => 706478 – Preventing Basis tables from increasing considerably

Fragmentation check (Rev.52 or higher):

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' OR CATEGORY = 'CATALOG' ) 
GROUP BY HOST, PORT;

 

PORT Row store Reorganization Recommended Free Space Ratio in % Allocated Size in MB Free Size in MB
30.003 TRUE 36,05 11.200 4.038,27

Starting with a fragmentation of 30% you should take a reorg in consideration.

There is a memory leak in some revisions, if you want to get rid of it just execute a reorg as workaround. Solution is to update to 112.06 / 122.02 or higher.
2362759 – Row table memory leak on SPS11 (Rev111~112.05) and SPS12 (Rev120~122.01)

To speed up the start time you can follow note 2159435 – How-To: Keeping SAP HANA Row Store in Memory when restarting
Normally after a shutdown the shared memory will be cleared. That means after a restart of the DB the RS have to be loaded completely from disk. This can take a while depending on speed of the storage and size of the Row Store.
You can define via parameter how long the row store after a shutdown should be held in memory. This happens during the shutdown via hdbrsutil (_not_ hdbnsutil). The shared memory segments will assigned to this process and transfered to indexserver during start procedure.

Back to main blog
Go next to Part 2: Heap memory
Go next to Part 3: tech. changes in SPS12

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply