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 covers the utility workload. Utility jobs are user requests which are not implementing the SQL protocol. Currently, there are the following ones:
- Loads (Fastload, Multiload)
- Unloads (FastExport)
- Backup & Restore (ARCMAIN, BAR)
Above mentioned tasks are implementing their protocols. The difference between SQL workload and utility jobs is that utility workload is highly specialized and optimized. Therefore, the more advanced workload classification criteria, such as “query characteristics” (product joins, ALL-AMP, etc.), are not available (and not needed). Still, workload management (TASM, TIWM) offers additional features unique 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
For each utility protocol, several implementations exist (for example, the 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 individual workload management features may be missing.
During execution, all utilities log on at least one SQL session and several utility sessions (except BAR, which does not use any utility session). The mandatory SQL session, called the control session, executes all SQL statements belonging to the utility.
Load and unload utilities may use a second SQL session (the auxiliary session) to maintain a restart log.
Utility resource usage can be managed by:
- 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 a maximum number of sessions each utility job uses.
Teradata Utility Workload – Concurrency Management
The usage of throttles implements concurrency limits. They can be defined at the system-level and workload levels.
Additionally, Teradata workload management implements hard limits which never can be exceeded 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
Although the overall hard limit for load & unload utilities together is 60, there are even more strict limits; For example, it’s impossible to execute more than 30 Fastloads at one point in time, even if the total limit of 60 loads & unload utilities is not reached.
The hard limits always lead to a delay if no user-defined utility or workload throttle with the reject option is defined.
A limit for AMP worker tasks accompanies the hard limits for concurrency. Utilities can use only 60% of the available AWTs at one point.
Even if concurrency limits are not reached, the AWT limits may cause a utility job to be delayed or rejected (important: AWT limits are only checked for FastLoad, Multiload, and FastExport without Spool).
User-defined utility throttles are used to manage resources on a system level. You can define limits such as “Allow 15 Fastloads at one point in time”. These throttles always have to be set in the range of the hard limits mentioned above.
If you need to arrange your utility workload on a more detailed level, utility workloads using their throttles can be defined.
This is achieved by adding at least “utility” as classification criteria to your workload. Additional measures can be used to detail further the workload, such as “request source,” “target” and “query band.”
As mentioned earlier, SQL protocol-specific criteria, such as “query characteristics,” are not available for classification.
Teradata Utility Workload – Session Management
Limiting the number of utility sessions is another way of managing system resources.
In the same way, as default concurrency limits are defined for utilities, the Teradata workload management takes care of sessions automatically if user-defined session limits do not overwrite the limits.
Workload management distinguishes between small, medium, and large amounts of data. By default, medium-sized data is expected.
If you are loading small or large amounts of data, the default limits can be adjusted using the query band below:
SET QUERY_BAND = “UtilityDataSize=SMALL/MEDIUM/LARGE;”;
These are the default rules 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”)
If the default session limits are insufficient, user workload can be defined.
Classification criteria for the user-defined workload are:
- Utility Name
- Request Source
- Query Band
- Data Size (via the “UtilityDataSize” query band)
You should always first design your utility rules on the system level to ensure the healthy behavior of your utility jobs. Specific workloads should only be considered to cover unique situations and requirements.