Teradata Utility Workload – Overview
Our previous articles about Teradata workload management covered how SQL requests are classified into workloads. We talked about throttles, dynamic workload management, and the priority scheduler.
This article discusses utility workloads, or user requests that do not implement the SQL protocol. The current utility jobs include:
- Loads (Fastload, Multiload)
- Unloads (FastExport)
- Backup & Restore (ARCMAIN, BAR)
The aforementioned tasks are executing their respective protocols. The utility jobs are distinct from SQL workloads due to their specialized and optimized nature. As a result, they do not require advanced workload classification criteria like “query characteristics” (e.g. product joins, ALL-AMP). Nevertheless, workload management systems (TASM, TIWM) provide unique additional features specific to this workload.
Teradata implements the below protocols to cover utility workload:
- The FastLoad Protocol
- The MultiLoad Protocol
- The FastExport Protocol
- The Backup & Restore Protocol
Multiple implementations exist for each utility protocol, such as the FastLoad protocol, which supports the Fastload Utility, TPT Load operator, and JDBC Fastload.
Third-party utilities can utilize these protocols through TPT, although certain workload management capabilities may not exist.
During execution, all utilities initiate at least one SQL session and several utility sessions, except BAR, which does not require any utility sessions. The control session, which is compulsory, executes all SQL statements pertaining to the utility.
Load and unload utilities may use a second SQL session (the auxiliary session) to maintain a restart log.
Managing utility resource usage involves:
- 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.
Teradata Utility Workload – Concurrency Management
Throttles enable the enforcement of concurrency limits and can be established at both the system and workload levels.
Teradata workload management enforces hard limits that cannot be surpassed by the user-defined utility or workload throttles.
Limit for Fastloads + Multiloads + FastExports: 60
The limit for Fastloads + Multiloads: 30
The limit for Fastloads: 30
The limit for Multiloads: 30
The limit for Fastexports: 60
Limit for Backup & Restore: 350
Despite the 60-load-and-unload-utility maximum, there are additional constraints. Specifically, executing over 30 Fastloads simultaneously is unfeasible, regardless of load and unload utility utilization.
The absence of user-defined utility or workload throttle with the reject option inevitably results in delayed hard limits.
The maximum concurrency limit also includes a cap on AMP worker assignments. Services can utilize a maximum of 60% of the AWTs currently accessible.
AWT limits may delay or reject utility jobs, even if concurrency limits are unmet. It’s important to note that AWT limits are solely checked for FastLoad, Multiload, and FastExport without Spool.
Utility throttles, specified by the user, regulate resource allocation at the system level. Limits, such as “permitting a maximum of 15 Fastloads simultaneously,” can be established. It is imperative that these throttles fall within the range of the aforementioned strict limits.
You can define utility workloads and their throttles to organize your utility workload in greater detail.
To achieve this, include “utility” as a classification criterion in your workload. Further details about the workload can be provided by employing additional measures such as “request source,” “target,” and “query band.”
As previously stated, SQL protocol-specific criteria, such as “query characteristics,” cannot be used for classification.
Teradata Utility Workload – Session Management
Restricting the quantity of utility sessions is an additional technique for managing system resources.
Teradata workload management automatically manages sessions unless user-defined session limits override default concurrency limits set for utilities.
Effective workload management categorizes data as small, medium, or large. The default expectation is for data of medium size.
Adjust the default limits for loading data, whether it’s a small or large amount, using the query band below:
SET QUERY_BAND = “UtilityDataSize=SMALL/MEDIUM/LARGE;”;
Here are the standard guidelines for utility session limits:
Fastload and Multiload:
Medium Sized Data:
IF (“Number of AMPs” <= 20) THEN “Number of AMPs.”
ELSE MIN(20 + “Number of AMPs” / 20), 100)
Small Sized Data:
“Medium Sized Data” * 0.5
Large Sized Data:
MIN(“Medium-Sized Data” * 1.5, “Number of AMPs”)
FastExport:
Medium Sized Data:
IF (“Number of AMPs” <= 4 THEN “Number of AMPs” ELSE 4
Small Sized Data:
“Medium Sized Data” * 0.5
Large Sized Data:
MIN(“Medium Sized Data” * 1.5, 2 * “Number of AMPs”)
To customize session limits, users can define their own workload parameters. The criteria for classifying this workload include:
- Utility Name
- Request Source
- Query Band
- Data Size (via the “UtilityDataSize” query band)
To ensure the proper functionality of your utility jobs, it is recommended that you begin by designing your utility rules at the system level. Only specific workloads that cater to unique situations and requirements should be taken into consideration.
