Workload in the SAP HANA

Options for Managing Workload

Workload management can be configured at multiple levels: at the operating system-level, by using global initialization settings, and at the session level.

There are a number of things you can do to influence how workload is handled:

• Outside the SAP HANA system on the operating system level you can set the affinity of the available cores.
• You can apply static settings using parameters to configure execution, memory management and peak load situations.
• You can influence workload dynamically at system runtime by defining workload classes.

All of these options have default settings which are applied during the HANA installation. These general-purpose settings may provide you with perfectly acceptable performance in which case the workload management features described here may not be necessary. 

Before you begin with workload management, you should ensure that the 

System generally is well configured

• SQL statements are tuned
• Distributed environment tables are optimally distributed and that indexes have been defined as needed.

Area	Possible Actions
CPU	Settings related to affinity are available to bind server processes to specific CPU cores. Processes must be restarted before these changes become effective.
CPU Thread Pools	Global execution settings are available to manage CPU thread pools and manage parallel execution (concurrency).
Memory	Global memory manager settings are available to apply limits to the resources allocated to expensive SQL statements.
Admission Control	Global admission control settings can be used to apply system capacity thresholds above which SQL statements can be either rejected or queued.
Priority and Dynamic Workload Class Mapping	A more targeted approach to workload management is possible by setting up pre-configured classes which can be mapped to individual user sessions. You can, for example, map an application name or an application user to a specific workload class. Classes include the option to apply a workload priority value.

1. Controlling CPU Consumption

SAP HANA can exploit the processor’s NUMA architecture to optimize performance by applying NUMA core affinity settings to server processes and by specifying NUMA node location preferences for specific database objects.

Applying NUMA core affinity settings to server processes.

Specifying NUMA node location preferences for specific database objects

Note: As an alternative to applying CPU affinity settings you can achieve similar performance gains by changing the parameter [execution] max_concurrency in the global.ini configuration file. Changing the configuration file may be more convenient and doesn’t require the system to be offline.

Examining the System Topology

• The lscpu command returns a listing of details of the system architecture. 
• hdbcons

The database management client console hdbcons can also be used to retrieve topology information

Information about the system topology and the CPU configuration is available in system views

select * from SYS.M_HOST_INFORMATION where key in ('cpu_sockets','cpu_cores','cpu_threads');

select MAX_NUMA_NODE_COUNT, MAX_LOGICAL_CORE_COUNT from SYS.M_NUMA_RESOURCES; select HOST, NUMA_NODE_ID, NUMA_NODE_DISTANCES, MEMORY_SIZE from SYS.M_NUMA_NODES;

Applying Affinity Configuration Changes

You can use the affinity configuration parameter to restrict CPU usage of SAP HANA server processes to certain CPUs or ranges of CPUs.You can set restrictions for the following servers: nameserver, indexserver, compileserver, preprocessor, and xsengine ,Few examples for restrictions 

ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'SYSTEM') SET ('nameserver', 'affinity') = '0,16'

ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'SYSTEM') SET ('indexserver', 'affinity') = '8-15,24-31'

2. Controlling Parallel Execution of SQL Statements

You can apply ini file settings to control the two thread pools SqlExecutor and JobExecutor that control the parallelism of statement execution.The settings described here should only be modified when other tuning techniques like remodeling, repartitioning, and query tuning have been applied. Modifying the parallelism settings requires a thorough understanding of the actual workload since they have impact on the overall system behaviour. Modify the settings iteratively by testing each adjustment.

You can set a limit for both SqlExecutor and JobExecutor to define the maximum number of threads. You can use this for example on a system where OLAP workload would normally consume too many CPU resources to apply a maximum value to the JobExecutor to reserve resources for OLTP workload.

Lowering the value of these parameters can have a drastic effect on the parallel processing of the servers and reduce the performance of the overall system. Adapt with caution by iteratively making modifications and testing. For more information, see Understand your Workload and SAP Note 2222250 - FAQ SAP HANA Workload Management which contains more details of the workload configuration parameters.

The following SqlExecutor parameters are in the sql section of the indexserver.ini file. sql_executors - sets a soft limit on the target number of logical cores for the SqlExecutor pool.

max_sql_executors - sets a hard limit on the maximum number of logical cores that can be used.

Caution

SAP HANA will not accept new incoming requests if the limit is exceeded. Use this parameter with extreme care.The following JobExecutor parameters are in the execution section of the global.ini or indexserver.ini.

max_concurrency - sets the target number of logical cores for the JobExecutor pool.

max_concurrency_hint - limits the number of logical cores for job workers even

if more active job workers would be available 

default_statement_concurrency_limit - restricts the actual degree of parallel execution per connection within a statement.

3. Setting a Memory Limit for SQL Statements

• statement_memory_limit - defines the maximum memory allocation per statement in GB After setting this parameter, statements that exceed the limit you have set on a host are stopped by running out of memory.
• statement_memory_limit_threshold - defines a percentage of the global allocation limit. Parameter statement_memory_limit is respected only if total used memory exceeds the global allocation limit by this threshold percentage. The default value is 0% (of the global_allocation_limit) so statement_memory_limit is always respected.

Example 

4. Managing Peak Load (Admission Control)

The admission control feature is enabled by default Use the admission control feature to apply processing limits and to decide how to handle new requests if the system is close to the point of saturation.Limits can be applied at two levels so that 

• Request placed in the queued
• Higher threshold for maximum workload

and the related threshold values and configurable parameters are available in the indexserver.ini file.

Parameter	Default	Detail
enable	True	Enables or disables the admission control feature.
queue_cpu_threshold	90	The percentage of CPU usage above which requests will be queued. Queue details are available in the view M_ADMISSION_CONTROL_QUEUES. The value 0 or 100 means that no requests are queued.
queue_memory_threshold	0	The percentage of memory usage above which requests will be queued. The value 0 or 100 means that no requests are queued.
reject_cpu_threshold	0	The percentage of CPU usage above which requests will be rejected. The default value 0 means that no requests are rejected, but may be queued.
reject_memory_threshold	0	The percentage of memory usage above which requests will be rejected. The default value 0 means that no requests are rejected, but may be queued.

There are many other parameters which is related to Queue Management such as

• queue_wait_time_threshold
• record_limit
• max_queue_size
• dequeue_interval
• dequeue_size
• queue_timeout
• Queue_timeout_check_interval

5. Managing Workload with Workload Classes

You can manage workload in SAP HANA by creating workload classes and workload class mappingsYou can classify workloads based on user and application context information and apply configured resource limitations (related to statement memory or thread limits) or a priority value. Workload class settings override other ini file configuration settings which have been applied and also override user parameter settings workload class settings only apply for the duration of the current session, whereas changes applied to the user persist

Properties for Workload Classes

Priority

This prioritizes statements in the current execution. Priority values of 0 (lowest priority) to 9 (highest) are available; the default value is 5.

CPU and Memory Limits

STATEMENT THREAD LIMIT

default_statement_concurrency_limit

STATEMENT MEMORY LIMIT

statement_memory_limit_threshold

TOTAL STATEMENT THREAD LIMIT

default_statement_concurrency_limit

TOTAL STATEMENT MEMORY LIMIT

total_statement_memory_limit

Timeout Values

STATEMENT TIMEOUT

WRITE TRANSACTION LIFETIME

IDLE CURSOR LIFETIME