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.
- 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.
- Enable the query plan
IQ Query plans:
- ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('database', 'Query_Timing') = 'ON' with reconfigure;
- ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('database', 'Query_Plan') = 'ON' with reconfigure;
- ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('database', 'Query_Plan_as_html') = 'ON' with reconfigure;
- ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('database', 'dml_options10') = 'ON' with reconfigure;
- ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('database', 'Query_Plan_After_Run') = 'ON' with reconfigure;
- ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'SYSTEM') SET ('database', 'Query_Detail') = 'ON' with reconfigure;
- 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;”
- Schedule below OS traces
- 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 e
nabling 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
Record Count |
Uncompressed Size(GB) |
Size in HANA Memory (GB) |
Data Move time(S) |
Size on DT(GB) |
8,071,997,130 |
7711.2 |
524 |
25872 |
1297 |
7,954,719,530 |
7632 |
513 |
24115 |
1250.42 |
3,168,726,599 |
2697.6 |
272 |
10374 |
500.57 |
3,225,036,132 |
2,740.80 |
266 |
7264 |
581.71 |
3,219,748,756 |
2738.4 |
267 |
19649 |
585.21 |
3,483,255,691 |
2,966.40 |
286 |
12353 |
617 |
3,850,697,533 |
3,277.20 |
323 |
10800 |
483.7 |
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
