What are Throttles used for in Teradata Workload Management?
Throttles are used to limit the number of concurrent sessions, requests or utilities. We use throttles in Teradata workload management to protect critical resources – such as memory, AMP worker tasks, and CPU – from being exhausted. The usage of throttles helps to reduce resource contention on systems with a high level of concurrency, usually increasing the total system performance.
Request throttling limits the number of concurrent requests (queries). The defined request limit is ensured at any point in time. The workload management uses a simple counter to keep track of the number of executing requests. If the limit is reached and additional queries are ready for execution, they are added to the so-called delay queue (this is the most used application) or rejected immediately. As soon as the request counter falls below the limit, the first query waiting in the queue (FIFO) will be executed. Requests being executing never will be moved back to the delay queue.
Request throttles can be defined per workload or on the system level.
Workload throttles enforce the limits for the requests executing on behalf of the workload. They are applied after requests have been classified into the workload.
System throttles enforce limits on the system level. Without any further request classification, the limits will be implemented to each and every single query. Nevertheless, all the classification criteria's we explained in the first part of our workload management series (Teradata Workload Management – The Basics) can be used to restrict limits to a group of queries with common characteristics (source, target, queryband, etc.).
System throttles and workload throttles can be active for a request at the same time.
Enforcing Session Limits
Session limits are used to restrict the number of parallel sessions allowed at one point in time. In contrast to request throttles, enabling the delaying of requests, sessions cannot be delayed. If the session limit is reached, no more sessions can be logged on (logins will be rejected).
Session limits can be applied for the complete system or classification targets (user, account, IP, etc.). Additional classification criteria's, apart from targets, can't be used.
Throttling Best Practice
It's much easier to deal with workload throttles than with system throttles. The impact of workload throttles is overseen able and limited to a group of queries. Workloads collect requests with common characteristics; Workload throttles allow to define limits for each group of requests, and the impact of these limits can be easily identified.
Most times, the same limitations can be achieved much easier with workload throttles. System throttles are much more complicated to implement from a classification point of view.
Nevertheless, system throttles have their place in workload management. Usually, they are used to manage situations which are not related to specific workloads but the overall system condition, such as periods of system recovery or critical backup / restore activities. These are examples when it may make sense to limit requests on the system level.
However, you are defining your throttles: Never apply throttling to you tactical workload (the reason should be obvious).
How to identify Throttling in DBC.DBQLOGTBL
The query log keeps track about all delays caused by either workload or system throttles, helping to identify bottleneck situations and other performance related issues on your system. Two columns are showing us all information we need:
WDDelayTime and DelayTime
WDDelayTime demonstrates the number of seconds a request was a delay in a workload queue. This column is only referring to workload delays.
DelayTime is the total number of seconds the request was delayed, mainly the sum of workload delays and system delays. Unfortunately, as workload delays and system delays can be overlapping for the same request, we can't just subtract WDDelayTime from DelayTime to get the system delays.
There is no way to calculate the system delays if workload delays and system delays happened.
What we know:
- If WDDelayTime equals DelayTime, we are aware that there were no system throttles applied.
- If WDDelayTime is NULL and DelayTime > 0 we are aware the system delay time (as no workload delays have been used)
All workload management related columns are available as well in the DBC view QryLogTDWM.