Classification Criteria
TIWM and TASM use classification criteria to map incoming queries to one of the existing workloads based on their technical characteristics and additional information such as user ID and login account. Query characteristics can be grouped into three main categories:
The Source of the Query: User, Account, Application, ClientID, Profile, Queryband, and IP Address.
The Target of the Query: Database, Table, View, Function, and Stored Procedure.
The Technical Characteristics of the Query: Join Type, Estimated runtime, Statement Type, Estimated Final Row Count, Percentage of rows accessed, Load Utility Type (Fastload, Multiload, etc.), and Number of AMPs involved.
Filters
Filters can be employed to prevent inappropriate queries from the execution or to issue warnings that help identify problematic queries. For instance, a filter can be set up to block reporting user logins between midnight and 6 am, leading to an error message if any reporting user query is attempted during this time frame.
Another example is using a filter to log a warning in the DBC.DBQLOGTBL table when a request consumes more than 100 CPU seconds. This filter allows administrators to monitor queries requiring significant processing power, helping them identify potential performance bottlenecks and optimize resource allocation for better system performance.
Throttles
In Teradata Workload Management, throttles are vital in concurrency control and performance optimization. Throttles limit the number of concurrent sessions, requests, or utilities to protect critical resources such as memory, AMP worker tasks, and CPU from being exhausted. This article delves into the different types of throttles, their applications, and how they contribute to better performance and resource management in Teradata systems.
Throttling Requests
Request throttling limits the number of concurrent requests or queries, ensuring that the defined request limit is maintained at any given time. When the request limit is reached and additional queries are ready for execution, they are either added to the delay queue or rejected immediately. Workload management uses a simple counter to track the number of executing requests. As the request counter falls below the limit, the first query waiting in the queue is executed.
Request throttles can be defined at the workload or system level. Workload throttles enforce limits for requests executing on behalf of the workload and are applied after requests have been classified into the workload. On the other hand, system throttles enforce limits on the system level, applying to every query without further request classification.
Enforcing Session Limits
Session limits restrict the number of parallel sessions allowed at one time. Unlike request throttles, sessions cannot be delayed. No more sessions can be logged on when the session limit is reached and logins are rejected. Session limits can be applied for the complete system or classification targets, such as user, account, or IP.
How Delay Queues are Designed in Teradata
Teradata has undergone significant changes in managing delay queues to streamline the throttling experience in more recent releases. This article provides an in-depth look at how these changes affect the DelayTime field in DBQL and what it means for interpreting delay times in Teradata moving forward.
Single Delay Queue in Recent Teradata Releases
In previous releases, delay queues were set up independently by type of throttle, with each workload throttle having its dedicated queue. However, more recent releases introduced a single delay queue for all throttles, meaning that queries delayed by system throttles reside in the same queue as queries delayed by workload throttles.
While this change streamlines the throttling process, it also requires reevaluating the DelayTime field in DBQL, which now has a slightly different interpretation than in earlier releases.
WDDelayTime, representing the time a query was delayed due to a workload throttle, remains unchanged in DBQL delay reporting for these newer releases. This field is not found in the DBC.QryLog view can be accessed using the QryLogTDWM view and other TDWM fields, such as WDID.
In the more recent Teradata releases, DelayTime is the total time a query spends in the delay queue, regardless of whether it is due to one or more system throttles, a workload throttle, or a combination of both. The reason for the delay or the number of throttles contributing to the delay is not considered; only the total wall clock time is recorded.
However, subtracting WDDelayTime from DelayTime to determine when a system throttle delays are no longer possible. In earlier releases, workload and system throttles had separate delay queues, allowing this calculation. However, with the introduction of a single delay queue, queries can be delayed by both types of throttles simultaneously, resulting in an overlap of delay times.
Handling Delays by Both Types of Throttles
When a query is only delayed by a workload throttle, DelayTime, and WDDelayTime metrics will be the same. If a system throttle only delays a query, WDDelayTime will be null, and the time reported in DelayTime is solely due to system throttles.
However, if both types of throttles delay a query, it is impossible to determine each throttle's contribution to the delay accurately. For instance, if DelayTime indicates an hour and WDDelayTime shows 20 minutes, the system throttle may have mandated a delay between 40 minutes and 1 hour, as both throttles could be keeping the query in the delay queue simultaneously.
A question often raised by users is why the DelayTime and WDDelayTime columns in Dbqlogtbl allow null values instead of zero for queries that are not delayed. The reason behind this design choice lies in the logging efficiency of DBQL tables. These tables contain many fields, and the priority has always been to ensure efficient data logging. As a result, the philosophy has been to avoid logging a field if unnecessary.
Most fields in the DBQLogTbl are initialized to zero at the beginning of a request. The code responsible for logging values checks if there are any values greater than zero in a field before logging the data. Consequently, fields with zero values do not get logged and report NULLs. DelayTime and WDDelayTime columns fall into this category.
Difference between Zero and NULL DelayTime
When looking at the DelayTime values in Teradata, it's essential to understand what each value represents.
A NULL DelayTime indicates that the request has not been delayed. In other words, the query faced no delays due to workload or system throttles.
A zero DelayTime value represents a delay greater than zero but less than one second. Teradata used to report DelayTime in hundredths of a second in the past. However, DelayTime is reported at one-second intervals in the current releases, as it has an integer data type.
The reason behind Zero DelayTime
The Teradata system calculates a DelayTime value if it finds that hundredths of a second are greater than zero, as initial values are stored internally in hundredths of a second. Any value from 0.01 to 0.99 seconds will be zero with integer division.
This means that when you see a zero DelayTime value, it indicates a delay, but it was so minimal (less than one second) that it was rounded down to zero when converted to an integer value.
Throttling Best Practices
Dealing with workload throttles is generally easier than with system throttles, as the impact of workload throttles is limited to a group of queries. Workload throttles allow for defining limits for each group of requests, making it simple to identify the impact of these limits. System throttles, however, are more complicated to implement from a classification perspective.
System throttles are best suited for managing situations unrelated to specific workloads but rather the overall system condition, such as periods of system recovery or critical backup and restore activities. In any case, never apply throttling to tactical workloads.
Throttles in Teradata Workload Management are essential for concurrency control and performance optimization. By limiting the number of concurrent sessions, requests, and utilities, throttles help protect critical resources and reduce resource contention, ultimately improving system performance. Understanding the different types of throttles, their applications, and how to identify them in the query log is crucial for managing and optimizing Teradata systems effectively.
Common Workload Priorities
Tactical Tier:
This tier has access to all system resources and should be used cautiously, typically for single AMP index accesses. An exception handler automatically assigns any resource-intensive query to a lower priority to protect the system from long-running queries mistakenly assigned to the tactical tier.
Teradata Tactical Workload Exceptions are essential to protect against tactical queries that consume excessive resources. It's necessary to have this protection because the super-priority and almost unlimited access to resources given to work running in the Tactical tier with SLES 11 are easy to abuse.
A Tactical workload exception consists of two different exceptions bundled into one: one exception is on CPU, and the other is on I/O usage. When either of these exceptions is detected, the query running in the Tactical tier will be demoted to a non-tactical workload. Each exception has two threshold variants: a per-node threshold and a sum over all node thresholds.
Each node has its instance of the operating system and the priority scheduler, which only knows about the activities on that one node. Each node's priority scheduler instance monitors its per-node exception thresholds for CPU and I/O. The sum of all node exceptions is monitored and detected by workload management (TASM or TIWM).
To see if a tactical exception has occurred across all nodes, look at the FinalWDID field in the DBQlogTbl. The easiest way to monitor the frequency of tactical exceptions is to check the TacticalCPUException and TacticalIOException fields in the DBQLogTbl.
It is important to monitor the frequency of tactical exceptions and cautiously change the exception thresholds. It's recommended that thresholds for tactical events remain low at the default settings or below.
Tactical queries are expedited, which means they will use reserved AMP worker tasks if such reserves have been defined on the platform. Large numbers of demotions out of tactical could contribute to the misuse of reserved AMP worker tasks and could deplete their number sooner than expected. For that reason, if tactical queries are hitting the exception thresholds often, that should be a tipoff that either the tactical application requires tuning or that the workload belongs in a non-tactical tier.
In conclusion, Teradata Tactical Workload Exceptions are crucial for protecting against excessive resource consumption by tactical queries. It is important to monitor the frequency of tactical exceptions, change the exception thresholds cautiously, and ensure that the tactical queries do not deplete the reserved AMP worker tasks. By following these guidelines, you can ensure that your system runs efficiently and smoothly.
Timeshare Tier:
This tier consists of fixed priorities - Top, High, Medium, and Low. The Top priority receives eight times more resources than the Low priority, four times more resources than the Medium priority, and twice as many resources as the High priority. However, the Timeshare Tier only receives the resources not consumed by the Tactical Tier.
Teradata Tactical and Timeshare Tiers together are powerful tools for optimizing system performance and managing resources in a Teradata Database environment. Administrators can effectively manage workloads by understanding the classification criteria, filters, throttles, and workload priorities, ensuring optimal performance and resource allocation for various tasks and user requirements.