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:
###############
Shared memory
###############
Content
To check the size of the shared memory you can execute the following statement:
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.
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
You can see that the biggest part consists of the table category
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):
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
###############
Version History
###############
V1.1 Repaired destroyed pictures
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)
###############
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
###############
Version History
###############
V1.1 Repaired destroyed pictures
- SAP Managed Tags:
