HANA Dynamic Tiering Sizing , Configuration and Operations

Customer is live with 48TB HANA single node and In order to sustain the DB growth due to transactional volumes, SAP HANA dynamic tiering was implemented in landscape to manage warm data . This is a Technical article on setup ,post setup and Daily operational task along with parameter configuration and tracing. Customer took decision to go with same host Deployment option and SAP HANA dynamic tiering is installed on same host as HANA. Once installation is done the first step is to create extended storage and there some Limitations for the same”

• SAP HANA memory <= 2.5TB: size of dynamic tiering storage should not exceed 4x the size of SAP HANA memory.
• SAP HANA memory > 2.5TB: size of dynamic tiering storage should not exceed 8x the size of SAP HANA memory.
• Initial size cannot exceed 1 TB. The size of any individual dbfile added to a dynamic tiering database cannot exceed 1 TB. You can later increase the size by adding dbspace files in dbspaces

Create Extended Storage

Once the SAP HANA dynamic tiering installation is done, extended store can be created using below command

Example :create extended storage at ‘hostname’ size 1 TB ENABLE DELTA

Due the limitation we created only 1 TB of DB Space and later added additional space using below commands

Example :

Add more DBSPACE for SAP HANA dynamic tiering

Where ‘F5Z’ is the HANA DB name , here I have create 4 user dbspaces of 2TB each and 1 dbspace for temp

• alter extended storage alter dbspace ES_USER ADD FILE ES_USER_1 ‘F5ZESDB.esuser_1’ SIZE 2048 GB;
• alter extended storage alter dbspace ES_USER ADD FILE ES_USER_2 ‘F5ZESDB.esuser_2’ SIZE 2048 GB;
• alter extended storage alter dbspace ES_USER ADD FILE ES_USER_3 ‘F5ZESDB.esuser_3’ SIZE 2048 GB;
• alter extended storage alter dbspace ES_USER ADD FILE ES_USER_4 ‘F5ZESDB.esuser_4’ SIZE 2048 GB;
• alter extended storage alter dbspace ES_TEMP ADD FILE ES_TEMP_1 ‘F5ZESDB.temp_1’ SIZE 2048 GB; 

Parameter settings & Resource Allocation for SAP HANA dynamic tiering

• System is Having a total of 32 socket’s with 572 cores (18 cores/socket) &  48TB  Memory
• We reserved 4 sockets (72 cores) and allocated to DT which is 6TB memory

Recommended parameter Setting for SAP HANA dynamic tiering

[database]

• snapshot_versioning = Row-level
• max_iq_threads_per_connection = 500
• max_iq_threads_per_team = 144
• max_query_parallelism = 64
• [row_engine]
• advance_minimum_on_commit = false

[startup] Memory/Cache parameters:

Delta_memory_mb = 2048

This one is only to fix initialization issue and not relevant if delta is not enabled while creating extended store. By default  delta_memory_mb is calculated as 40% of total available memory on Host . So, its allocating some 16 TB and there is a hardcoded limit of this parameter to be not more than 2 TB.

For any server where the you believe 40% of total memory  will be beyond 2 TB . You might need to adjust this parameter before creating the extended store

• catalog_cache = 2000000000  (25% of total memory allocated for SAP HANA dynamic tiering)
• num_threads = 4096
• load_memory_mb = 1572864
• main_cache_mb = 1572864 (25% of 6TB)
• temporary_cache_mb = 2831155  ( 45% of allocated DT memory (6TB) => 2754 GB)
• heap_memory_mb = 314570
• max_concurrent_connections = 500
• max_concurrent_queries = 500

Sample SQL for setting the above parameters:

• ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’ , ‘SYSTEM’) SET (‘startup’, ‘catalog_cache’) = ‘1000000000’ WITH RECONFIGURE;
• ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’ , ‘SYSTEM’) SET (‘startup’, ‘heap_memory_mb’) = ‘157285’ WITH RECONFIGURE;
• ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’ , ‘SYSTEM’) SET (‘startup’, ‘load_memory_mb’) = ‘786432’ WITH RECONFIGURE;
• ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’ , ‘SYSTEM’) SET (‘startup’, ‘main_cache_mb’) = ‘786432’ WITH RECONFIGURE;
• ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’ , ‘SYSTEM’) SET (‘startup’, ‘temporary_cache_mb’) = ‘1415577’ WITH RECONFIGURE;

DT startup parameters

This a DT startup parameter and need to be maintained in daemon.ini file

(/usr/sap/<SID>/HDB<instance#>/<hostname>/daemon.ini

Example:

[esserver]

arguments = -n esF5Z04 -x tcpip{port=30412} -hes -iqtraceflags 2 -iqnumbercpus 72 -cl 2000M -ch 10000M

iqtraceflags 2 : This will speed up concurrent access/connection to SAP HANA dynamic tiering data , this flag disables the heap memory book keeping ( Planned to be productized in SP03) , NO impact on DT data integrity

Iqnumercpus 72 : will restrict SAP HANA dynamic tiering to not go beyond 144 cores ( but does not honor which cores, any free cores will be used up to the limit)

Cl & CH  increase the catalog cache sizes for large dataset

CPU affinity

Define CPU affinity for DT and HANA separately. This will make sure you are taking up each other CPU resources and causing issues

Step1: run lscpu to get details of NUMA  (32 sockets & hyperthreading OFF):

NUMA node0 CPU(s):     0-17,576-593

NUMA node1 CPU(s):     18-35,594-611

NUMA node2 CPU(s):     36-53,612-629

NUMA node3 CPU(s):     54-71,630-647

NUMA node4 CPU(s):     72-89,648-665

NUMA node5 CPU(s):     90-107,666-683

NUMA node6 CPU(s):     108-125,684-701

NUMA node7 CPU(s):     126-143,702-719

NUMA node8 CPU(s):     144-161,720-737

NUMA node9 CPU(s):     162-179,738-755

NUMA node10 CPU(s):    180-197,756-773

NUMA node11 CPU(s):    198-215,774-791

NUMA node12 CPU(s):    216-233,792-809

NUMA node13 CPU(s):    234-251,810-827

NUMA node14 CPU(s):    252-269,828-845

NUMA node15 CPU(s):    270-287,846-863

NUMA node16 CPU(s):    288-305,864-881

NUMA node17 CPU(s):    306-323,882-899

NUMA node18 CPU(s):    324-341,900-917

NUMA node19 CPU(s):    342-359,918-935

NUMA node20 CPU(s):    360-377,936-953

NUMA node21 CPU(s):    378-395,954-971

NUMA node22 CPU(s):    396-413,972-989

NUMA node23 CPU(s):    414-431,990-1007

NUMA node24 CPU(s):    432-449,1008-1025

NUMA node25 CPU(s):    450-467,1026-1043

NUMA node26 CPU(s):    468-485,1044-1061

NUMA node27 CPU(s):    486-503,1062-1079

NUMA node28 CPU(s):    504-521,1080-1097

NUMA node29 CPU(s):    522-539,1098-1115

NUMA node30 CPU(s):    540-557,1116-1133

NUMA node31 CPU(s):    558-575,1134-1151

STEP2:

As decided earlier 28 sockets are assigned to HANA and 4 are assigned to SAP HANA Dynamic Tiering .

Edit file : /usr/sap/<SID>/HDB<INSTANCE Number>/<HOSTNAME>/daemon.ini

Example : /usr/sap/F5Z/HDBHDB00/<HOSTNAME>/daemon.ini

[esserver]

affinity = 504-575

[indexserver] & [indexserver.AFP]

instances = 0 (only for system)

affinity = 0-503

STEP3:

HANA GAL—NO restart of HANA

We limited the CPU affinity and no more any  table is located in CPU’s allocated to DT as per NUMA stats. So also limit the HANA global allocation limit to 41.5 TB as below or any value less than 42 TB leaving some small buffer to OS.

ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘DATABASE’, ‘<Tenant_SID>’) SET (‘memorymanager’, ‘allocationlimit’) = ‘ 41500000’ WITH RECONFIGURE;

SAP HANA System Replication with Dynamic Tiering & Backup Recovery

Key Points 

• HANA System replication works as normal with SAP HANA dynamic tiering as well. Active/Active is also supported but we cannot read data from secondary in this mode
• We can add the SAP HANA dynamic tiering to primary and secondary with out breaking the ongoing HANA system replication. But Downtime is needed
• We took both primary and secondary down at same time installed SAP HANA dynamic tiering and started both the primary and  secondary .
• HANA Backups are faster compared to SAP HANA dynamic tiering but still we can achieve 1 TB/hour through on SAP HANA dynamic tiering side
• It is not possible to separately  restore HANA and SAP HANA dynamic tiering due to data consistency issues .
• If there is any issue or outage while SAP HANA dynamic tiering full initialization it will start from scratch as of now SAP HANA dynamic tiering is not fault tolerant and won’t resume from where it stopped .
• There is a option to disable backup encryption which will improve the speed of backup by almost 10 times . We were able to backup 40TB of SAP HANA dynamic tiering in 24 Hours approx.

DATA AGING

Preparation and Stats Collection

Before moving of data, we can estimate the timing based on the size of data using below SQL Commands. Scenarios is very big table almost 9 TB in memory with range-range partitions. With second level as month.

For any changes to partitioned table we should use seconds level partition values for Data move to SAP HANA dynamic tiering or calculating the size and  record count kind of tasks.

To see how much data is saved from memory aspect and how much is residing on extended store . We use below SQL’s

HANA-DT Data Move Stats Collection:

Example:

Uncompressed Size(GB)

select
round(sum(a.uncompressed_size)/1024/1024/1024,
0)+ (round(sum(a.uncompressed_size)/1024/1024/1024,
0) * 20/100) as “Uncompressed Size (GB)”
from m_cs_columns a ,
m_cs_partitions b
where a.part_id = b.part_id
and a.table_name = b.table_name
and a.table_name = ‘TABLE_PARTITIONED_WITH_RANGE_RANGE’
and a.schema_name =’SAPSR3′
and b.subrange like ‘20171201-20180101’

Record Count

select
sum(record_count)
from m_cs_tables a ,
m_cs_partitions b
where a.part_id = b.part_id
and a.table_name = b.table_name
and a.table_name like ‘TABLE_PARTITIONED_WITH_RANGE_RANGE’
and a.schema_name = ‘SAPSR3’
and b.subrange like ‘20171201-20180101’

Size in Memory(GB)

select
round(sum(a.memory_size_in_total)/1024/1024/1024)         as “HANA Memory  Size (GB)”
from m_cs_columns a ,
m_cs_partitions b
where a.part_id = b.part_id
and a.table_name = b.table_name
and a.table_name = ‘TABLE_PARTITIONED_WITH_RANGE_RANGE’
and a.schema_name =’SAPSR3′
and b.subrange like ‘20171101-20171201’

This 3 stats gave us information on how much memory is freed up from HANA and also estimation on how much time it took to move data to extended storage.

Views to See Sizes in DT side

M_ES_TABLES

M_ES_DBSPACES

Data Move SQL Syntax

Move Data to SAP HANA dynamic tiering – Aging

ALTER TABLE “SAPSR3″.”TABLE_PARTITIONED_WITH_RANGE_RANGE” ALTER PARTITION (“POSTING_DATE”) USING EXTENDED STORAGE ( 20181201 <= VALUES < 20190101 );

ALTER TABLE “SAPSR3″.”TABLE_PARTITIONED_WITH_RANGE_RANGE” ALTER PARTITION (“POSTING_DATE”) USING DEFAULT STORAGE ( 20181201 <= VALUES < 20190101 );

Once data move is done you will see below update in esserver.out located in trace directory

[00000]{0000000000}[-1/-1] 2018-07-11 15:54:05.544034 i esserver UNKNOWN(0) : Insert for ‘TABLE_PARTITIONED_WITH_RANGE_RANGE’ completed in 12353 seconds.  3483255701 rows inserted.

On HANA studio

Statement ‘ALTER TABLE “SAPSR3″.”TABLE_PARTITIONED_WITH_RANGE_RANGE” ALTER PARTITION (“POSTING_DATE_EXT”) USING EXTENDED STORAGE …’

successfully executed in 4:43:03.305 hours  (server processing time: 4:43:03.482 hours) – Rows Affected: 0

In general we were able to move 1 TB of data per hour or approximately 1 billion records per hour  to DT from HANA

Additional Logs

This are needed only if the SAP HANA dynamic tiering development ask for additional logs.

1. I enabled ZRLOG traces with more trace files  and we need to a restart of SAP HANA dynamic tiering for new parameters to be taken affect  to capture sql’s going to  SAP HANA dynamic tiering while data move is happening.
2. Enable the query plan

IQ Query plans:

1. 1. ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’, ‘SYSTEM’) SET (‘database’, ‘Query_Timing’) = ‘ON’ with reconfigure;
   2. ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’, ‘SYSTEM’) SET (‘database’, ‘Query_Plan’) = ‘ON’ with reconfigure;
   3. ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’, ‘SYSTEM’) SET (‘database’, ‘Query_Plan_as_html’) = ‘ON’ with reconfigure;
   4. ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’, ‘SYSTEM’) SET (‘database’, ‘dml_options10’) = ‘ON’ with reconfigure;
   5. ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’, ‘SYSTEM’) SET (‘database’, ‘Query_Plan_After_Run’) = ‘ON’ with reconfigure;
   6. ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’, ‘SYSTEM’) SET (‘database’, ‘Query_Detail’) = ‘ON’ with reconfigure;
   7. I changed the path of this files to /trace/IQ_QUERY_PLAN using sql “ALTER SYSTEM ALTER CONFIGURATION (‘esserver.ini’, ‘SYSTEM’) SET (‘database’, ‘Query_Plan_As_HTML_Directory’) = ‘<FULL_DIRECTORY_PATH>’ with reconfigure;”
   8. Schedule below OS traces

1. 1. 1. • sudo /usr/sbin/iftop -n -t -s 18000 -i lo > log.txt &
         • top -ibHd 60 > top.out &
         • vmstat 60 > vmstat.out &

Additional Debug traces can be captured  by  enabling FED trace DEBUG mode for Index server server and increase the trace file count so that it’s not overwritten. . Once data move is done disable all trace and copy the logs to some other folder so that they are not overwritten if any analysis is needed

Below is some Stats on Data size in SAP HANA dynamic tiering and HANA and over all time took for DT data move

This will give an overall estimation on SAP HANA Dynamic Tiering data aging capabilities.

Final Conclusion:

There are several options to manage this kind of Big size databases with HANA . Here with this case study we use HANA Dynamic Tiering  and managed  rapidly growing customer database with out impacting any ongoing HANA capabilities

We also captured Data move times , compression factors and memory savings from HANA side as shown in above Table.This will give us an estimate of how to approach of Sizing the HANA Dynamic Tiering ,Planing for maintenance windows for data move and also estimations on memory and CPU resource configuration parameters

Original Article:
https://blogs.sap.com/2020/06/12/hana-dynamic-tiering-setup-and-operations/