last updated: 2023-01-09 18:00 CEST
Ok, you already read part I of the deep dive series of HANA workload management. You are waiting for more insides after you identified some HANA resource bottlenecks? Then you are on the right track. In this part I will describe how you can get an overview of your systems workload and how to handle it with different methods.
It is important to know the behavior and the load if the system. Every system is used in another way. Means you have to analyze, interpret and understand the workload. You have to know if it was just a one-time occurrence or a frequent behavior.
Most common scenarios:
- The system is overloaded and cannot fulfill the request - means the DB can no longer respond
- Performance issues for certain queries
- System is not using the defined thresholds
HANA workload management deep dive part II
General workload management
There are multiple ways to consume your resources efficiently. I will only describe the common methods and go not too deep into details of NUMA and CPU affinities.
If a statement is not limited by a parameter or a workload class, it can use all resources if the optimizer choose a execution plan with parallel threads. This resources will be granted directly after the connect of the session.
First of all how to identify such resource bottlenecks? Again please use and study the scripts of Martin Frauendorfer in note 1969700. It is worth to spend the time to know how to use them.
Identify the time frames when the CPU goes over 70% for a long-time span is a good indicator. Please keep in mind that there might be some short critical time frames which are not included in the thread sampling of the HANA. It is not exactly documented by SAP how the HANA is getting the resource details from the OS process and how grading works. But by default if the tracking (service_thread_sampling_monitor_enabled) is activated in newer revisions (Rev. 55), but as the name says it is just sampling and no exact measurement. This means short peaks below 2-3 seconds can get lost, but the admission control can be activated due to another measurement method. Here an expert monitoring is needed. But back to 90% of the cases:
There is CPU time divided into CPU(host and indexserver) and SYS (System).
CPU on host level means the CPU time outside of HANA, e.g. third-party tools like AV, FS Backup etc.
CPU on indexserver means the CPU time of the indexserver without the interrupts and OS kernel time.
SYS is including the interrupts (I/O, network, etc.).
Other tools like nmon, top or the SQL collection will only break it down to CPU and SYS. Means indexserver CPU as CPU and all third party and interrupts as SYS.
HANA_LoadHistory_Services_*
|
It is hard to read this format, but I just want to give you a feeling of what you will get if you are using the SQL collection. You have to import it in excel and create your own graphs. You can also use the HANA cockpit but you will never get such detailed information as you get it from the SQL scripts.
Find the critical time frames where the CPU is over 70%. Here you can also see the number of SQLs and threads at this point in time. When the ratio of ACT_SQL and WAIT_SQL is over 50% you should investigate more time for this time frame. In this example we can identify the timeframe from 04:14 to 04:47 as critical. Means the system was under high load. But why?
We can see a lower number of statements per second which is the consequence of the resource bottleneck. We also see a high number of threads. But was it only one statement which was not limited by the systems parameter or have we found a situation with multiple SQLs which running at the same point in time and block all resources although limited by the default_statement_concurrency_limit or even multiple workload classes?
Situation one is easy – just configure your system correctly with default parameter and/or workload classes.
Situation two needs more time for investigation. You have to find out the root cause. To be more accurate you need the statement hash, user or application name. With the latest version of the script you also have the possibility to check if the admission control was active at the point in time. This will be described in more detail in the associated chapter (part III).
Live monitoring of the dyn. Workload Management
In part I we already talked about the dyn. workload management
If you want to know how it is currently working in the system, check your current setup via SQL script.
HANA_Jobs_Executors_2.00*:
-----------------------------------------------------------------------------------------------------------------------------------
|HOST |PORT |GROUP|CONC_CONFIG|CONC_CURR|TOTAL|BUSY|PARKED|FREE|SYSWAIT|JOBWAIT|IMM_MS|SHORT_MS|NORMAL_MS|SQL_BUSY|OTH_BUSY|
-----------------------------------------------------------------------------------------------------------------------------------
|saphana10000|30001| 0| 16| 16| 16| 0| 1| 12| 3| 0| 0.00| 0.02| 0.01| 0| 0|
|saphana10000|30001| 1| 16| 16| 16| 0| 1| 15| 0| 0| 0.00| 0.00| 0.02| 0| 0|
|saphana10000|30002| 0| 16| 16| 16| 0| 1| 15| 0| 0| 0.00| 0.00| 0.00| 0| 0|
|saphana10000|30002| 1| 16| 16| 14| 0| 0| 14| 0| 0| 0.00| 0.00| 0.00| 0| 0|
To understand what this results shows you, you have to understand the different values:
CONC_CONFIG: Configured max_concurrency value
CONC_CURR: Currently used max_concurrency value (can be smaller than configured one in case of dynamic reductions)
TOTAL: Total number of JobWorkers
BUSY: Running JobWorkers
PARKED: JobWorkers parked for future use
FREE: Idle JobWorkers
SYSWAIT: JobWorkers waiting for synchronization
JOBWAIT: JobWorkers waiting for other JobWorkers
IMM_MS: Average time an immediate job waits in some queue
SHORT_MS: Average time a short running job waits in some queue
NORMAL_MS: Average time a normal job waits in some queue
SQL_BUSY: Running SqlWorker and SqlExecutor threads
OTH_BUSY: Running threads different from SqlWorker, SqlExecutor and JobWorkerA repeatedly increased number of queued jobs can indicate that the JobWorker load (i.e. parallelized activities) is higher than the available JobWorker threads.
The internally used max_concurrency limitation for JobWorker threads can differ in some cases unintentionally from the setting of the related parameter, e.g. because an UNSET of the parameter wasn't properly considered by the running system. If the used concurrency is much smaller than the intended one, the number of JobWorker threads is limited and throughput can reduce. In this case you can as a workaround manually set max_concurrency to a reasonably high explicit value.
Admission Control
Source: © 2022 SAP SE or an SAP affiliate company. All rights reserved.
The Admission Control should be the last bastion to prevent your system from overloading. You should not use or define it as workload management.
In this blog we will only cover the admission control part. In the next blog we will focus on the resource control.
How does it work?
You set up your thresholds at which workload the system should activate this control function. This can be CPU or memory percentage value. By default 90% of CPU – means it never gets active for memory. If you keep this default, it means when the system is reaching this threshold for a certain time (here is a gap inside the documentation how the sampling is working => averaging_factor, EWMA without a known time range) it is a) the control function is active and will do the following:
Source: © 2022 SAP SE or an SAP affiliate company. All rights reserved.
- Collection of statistics values (statistics_collection_interval; default 1000ms)
- weighting of the collected statistic value (averaging_factor; default 70; 100 means no averaging)
The decision manager will admit a session or queue/dequeue it. When one of the thresholds (CPU/memory) is reached queueing is active. It is important to know if a session has passed the check and become active it will not be reduced or limited in its resources afterwards.
If queueing is reaching a defined number of requests (max_queue_size; default: 10000) or the configured values (reject_memory_threshold / reject_cpu_threshold ; default: 0) rejecting is active.
What happens if a request is queued? When will it be released?
This depends on when the request was queued and which priority it has.
If the weighted statistics value drops below the threshold which will be checked every 1000ms by default (dequeue_interval)
A set of 50 (default; dequeue_size) requests will be released and assigned to free resources. If you have only small requests which are running faster than 1000ms you will wait also if there are more free resources.
This means you can increase your dequeue size that more requests can be dequeued in 1000ms or you can lower the dequeue interval that the dequeueing will only wait e.g. 500ms and not 1000ms. |
Which variant you should prefer depends on the number of cores and your workload. If you have over 100 cores you can increase the dequeue size. If you have a workload like BW with a high runtime you should not lower the dequeue interval.
What happens if a request is rejected?
Rejecting means also that a SAP work process cannot connect to the DB and is not ready to work in the application server.
Even administration SQLs may not connect anymore. This means identifying the root cause of the issue can be difficult, if no SQL session can connect.
To check the latest events (be careful the view shows only 10000 events => controlled by indexserver.ini -> [admission_control_events] -> record_limit):
HANA_Workload_AdmissionControlEvents_2.00.010+ HANA Cockpit Admission control Monitor |
Summary Admission Control
The admission control should only be active in unknown load scenarios preventing your system from overloading. It should not be used as a tool which is frequently active and acting as a bouncer/doorkeeper.
If the admission control is frequently active, check the root cause of the situation:
- Was it caused by a known SQL / program
- Is the system still sized correctly for the SQL requirements?
- Are the parameters for memory and CPU correct?
- Are there any user parameters active?
- Is the workload class mapping correct?
For all important workloads you should create workload classes to control and guarantee the needed resources for the priority of the workloads. Best example is if you have OLAP/BW workload on your OLTP/ERP system. The main workload should be OLTP, but due to the high degree of parallelism there can be a bottleneck in resources which will affect the OLTP load as well.
Exactly this to identify and to manage is one of the core responsibilities of HANA DBA. Know your system, your peaks and react early if there is something wrong with your resources. If you can successfully prioritize and manage the current system resources for tasks which are really needed, you won't need to buy new expensive resources in the near future. Not always more hardware is the right answer. Use the existing hardware in the right manner.
backt to part I
continue with partIII
- SAP Managed Tags:
4 Comments
