In our previous articles, we discussed Teradata workload management, focusing on SQL request classification, throttles, dynamic workload management, and the priority scheduler. This article delves into utility workload management in Teradata, which handles non-SQL protocol tasks. We will cover the types of utility tasks, protocols, and techniques used to manage resources and concurrency.
Understanding Teradata Utility Workload
Utility jobs are user requests that do not implement the SQL protocol. Currently, there are four primary utility tasks:
Loads (Fastload, Multiload)
Unloads (FastExport)
Backup & Restore (ARCMAIN, BAR)
These tasks implement their own specialized and optimized protocols, and Teradata supports the following protocols for utility workload management:
The FastLoad Protocol
The MultiLoad Protocol
The FastExport Protocol
The Backup & Restore Protocol
Several implementations exist for each utility protocol (e.g., FastLoad protocol is implemented by the Fastload Utility, the TPT Load operator, and the JDBC Fastload). Non-Teradata utilities (3rd party) can apply these protocols via TPT, but some workload management features may be unavailable.
Managing Utility Resource Usage
Concurrency Levels: Defines the maximum number of utilities that can be executed simultaneously. If the limit is exceeded, arriving utility jobs will be delayed or rejected.
Utility Session Limit: Defines the minimum and maximum number of sessions each utility job uses.
Concurrency Management in Teradata Utility Workload
Concurrency limits are implemented using throttles, which can be defined at the system and workload levels. Teradata workload management also implements hard limits that user-defined utility or workload throttles cannot exceed:
Limit for Fastloads + Multiloads + FastExports: 60
Limit for Fastloads + Multiloads: 30
Limit for Fastloads: 30
Limit for Multiloads: 30
Limit for Fastexports: 60
Limit for Backup & Restore: 350
Additionally, Teradata has a limit for AMP worker tasks (AWTs), allowing utilities to use only 60% of the available AWTs at one time. AWT limits may cause utility jobs to be delayed or rejected, even if concurrency limits are not reached.
User-defined utility throttles can manage resources at the system level, and utility workloads using their throttles can be defined for more detailed workload management by adding "utility" as a classification criterion, along with other measures like "request source," "target," and "query band."
Session Management in Teradata Utility Workload
Limiting the number of utility sessions is another way to manage system resources. Teradata workload management automatically handles sessions unless user-defined session limits overwrite them. Workload management distinguishes between small, medium, and large data amounts, with medium-sized data being the default expectation.
Default session limits can be adjusted using the query band, and user workloads can be defined with specific classification criteria, such as Utility Name, Request Source, Query Band, and Data Size (via the "UtilityDataSize" query band).
Teradata utility workload management is crucial for managing non-SQL protocol tasks and ensuring optimal system performance. By understanding the types of utility tasks, protocols, and resource management techniques, users can design efficient utility rules and workloads to address unique situations and requirements.
Understanding resource allocation rules and best practices in Teradata Workload Management is crucial for optimizing system performance and effectively managing workloads in a Teradata system. By following the hierarchical structure and implementing the safety mechanism, you can ensure that resources are efficiently allocated to all workloads according to their priority.