What are Throttles used for in Teradata Workload Management?
Throttles restrict concurrent sessions, requests, or utilities to safeguard vital resources, such as CPU, memory, and AMP worker tasks, from depletion. Their implementation in Teradata workload management minimizes contention for resources on highly concurrent systems, thus bolstering overall system performance.
Request throttling restricts the number of concurrent queries to a defined limit at all times. A straightforward counter is employed for workload management to monitor the number of executing requests. When the maximum limit is attained, any additional queries awaiting execution are either rejected immediately or added to the delay queue, which is the most commonly utilized application. The first query in the queue (FIFO) is executed once the request counter falls below the limit. Once execution begins, requests are not moved back to the delay queue.
Throttles for requests can be defined either for individual workloads or at the system level.
Workload throttles limit requests executed for a specific workload category. These limits are applied after requests have been categorized.
System throttles enforce limits on the system level. Without any further request classification, the limits will be implemented for each and every single query. Nevertheless, we explained all the classification criteria 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.).
Both system and workload throttles may concurrently affect a request.
Enforcing Session Limits
Unlike request throttles which can delay requests, session limits restrict the number of concurrent sessions. Once the session limit is met, any further logins are rejected.
Session limits can be imposed on the entire system or specific targets such as users, accounts, or IP addresses. Other classification criteria beyond these targets are not permissible.
Throttling Best Practice
Managing workload throttles is simpler than managing system throttles, as their impact is confined to a specific set of queries. Workloads are comprised of requests that share similar features. Implementing workload throttles enables the establishment of individual capacity limits for each set of requests, simplifying the identification of potential limitations.
Workload throttles are often a simpler solution to achieve the same limitations. Implementing system throttles is more complex when considering classification.
System throttles are useful for workload management, particularly in situations that do not pertain to specific workloads but rather to the system’s general condition. This includes times of system recovery and critical backup or restores activities. In such cases, limiting requests on a system level can be beneficial.
Avoid applying your throttles to your tactical workload due to the potential implications when configuring them.
How to identify Throttling in DBC.DBQLOGTBL
The query log records delays resulting from either workload or system throttles, enabling the identification of bottleneck situations and other performance-related issues. Two columns provide all the necessary information.
WDDelayTime and DelayTime
WDDelayTime demonstrates the number of seconds a request was delayed 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 and system delays can overlap for the same request, we can’t subtract WDDelayTime from DelayTime to get the system delays.
Calculating system delays is impossible when both workload and system delays occur.
Our only knowledge:
- If WDDelayTime equals DelayTime, we know that no system throttles were applied.
- If WDDelayTime is NULL and DelayTime > 0, we are aware of the system delay time (as no workload delays have been used)
The DBC view QryLogTDWM includes all columns related to workload management.