fbpx

Optimal Use Of Teradata Utility Workload

By Roland Wenzlofsky

September 14, 2015


Teradata Utility Workload – Overview

Our previous articles about Teradata workload management covered the basics of how SQL requests are classified into workloads. We talked about throttles, dynamic workload management, and the priority scheduler.

This article is covering the utility workload. Utility jobs are user requests which are not implementing the SQL protocol. Currently, this is 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, the workload management (TASM, TIWM) offers additional features unique to this kind of workload.

Teradata implements 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) are allowed to 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 is not making use of any utility session at all). The mandatory SQL session, called the control session, is used to execute 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 at one point in time.
    If the limit is exceeded, arriving utility jobs will be delayed or rejected.
    .
  • Utility Session Limit: Defines the minimum and maximum number of sessions being used by each utility job.

Teradata Utility Workload – Concurrency Management

The usage of throttles implements concurrency limits. They can be defined at the system-level and workload level.

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
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 AWT’s at one point in time.

Even if concurrency limits are not reached, the AWT limits may cause that a utility job is 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 we 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.”

Like 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 is taking care of sessions automatically, if user-defined session limits do not overwrite the limits.

The workload management is distinguishing 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 not sufficient, user workload can be defined.
Classification criteria’s for the user-defined workload are:

  • Utility Name
  • Request Source
  • Query Band
  • Data Size (via the “UtilityDataSize” query band)

You should always at first design your utility rules on the system level to ensure a healthy behavior of your utility jobs. Specific workloads should only be considered to cover unique situations and requirements.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>