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)
- 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'
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
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|
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.