Chapter 19 - TASM
“Regret for wasted time is more wasted time.”
- Mason Cooley
Three Levels of Workload Management
The three levels of workload management begin with Viewpoint Workload Designer in order to create workloads for pre-execution rules. The second level is Priority Scheduler which then has instructions for CPU usage based on the user or workload. The third level is the Database Query Log (DBQL) which allows analysis on which queries were run.
Pre- execution, Query Execution, and Post-execution
Use Viewpoint Workload Designer to classify queries into workloads. When a query is run, the system determines which workload it belongs to, and assigns the appropriate CPU allocation to control the speed. DBQL is the audit trail of what queries have been run on the system. The combination of all three levels allows a Teradata customer to plan, execute and re-examine their environment to optimize their system.
What is TASM?
Teradata Active System Management (TASM) is made up of two major products/tools that help the DBA in defining the rules that control the allocation of CPU resources to workloads running on a system. These rules include filters, throttles, and “workload definitions”.
Query Management compared to Workload Management
The purpose of “delaying” queries is to limit the number of CPU resources that are tied up in running low priority or long running DSS queries. This is also to establish Service Level Agreements (SLA’s) for tactical queries that must run in sub-second time. Even though a query is delayed, it is still part of the user’s session. Workload management takes “like” queries grouped in a named workload and establishes rules for how and when to process them.
The Active Workload Management Concept
ASM allows the DBA to visualize the current operational environment and compare that to long-term trending analysis. This allows the DBA to set Service Level Goals (SLGs ). Then the DBA can monitor to see if the SLGs are met, or they can take the necessary steps to better ensure they are met.
Active Events
Active Events notify the DBA of something important, Teradata presents the DBA with recommendations for the actions they should take. Business events are applied against business rules as current and historical data are analyzed in order for the DBA to take operational actions to correct the problem.
What is the Secret Sauce for Query Management?
The purpose of “delaying” queries is to limit the number of CPU resources that are tied up in running low priority or long running DSS queries. This is also to establish Service Level Agreements (SLA’s) for tactical queries that must run in sub-second time. The DBA can now control what queries run on the system at a specific point in time.
The life of a Query
The brilliant piece of Teradata system management is that it doesn’t matter what application submitted the query, but Teradata can still follow the rules that have been set up to execute, delay or reject the query.
What is a Workload?
Queries grouped into a workload can establish rules to manage the workload queries.
Workload Examples
There are Four Types of Query Rules
There are three sets of workload management rules that are available. Each set can be enabled or disabled.
1. System-wide query management filters
2. System-wide query management throttles
3. Workload Definitions
Common Sense Examples of Filters and Throttles
Performance Period Examples
Object Access filters, Query Resource filters, and Object throttles can specify the types of SQL requests to which the rule applies. For example, you can specify ALL, DDL, DML, or SELECT in your rules.
The Scoop on Object Throttles
Load Utility Throttles
Above is some more detailed information about Load Utility throttles.
Creating Workloads
When creating workload definitions, the Database Query Log will show what queries have been run over the past couple of months, and the Priority Scheduler will have captured settings in their Priority Definition sets. Then you can use Workload Analyzer to analyze these two sources. You can also decide to create workloads by hand after analyzing the DBQL log.
When Creating Workloads the “WHO” is your Foundation
By creating workloads with the “Who”, you can easily create workloads of common interest, which makes adding “What” and “Where” later less complicated.
After the “WHO” comes the “WHERE”
The “WHERE” criteria will come after you create the workload definitions based on the foundation of “WHO”. Why? “WHO” criteria has lower overhead than “WHERE” and “WHAT” because “WHO” is determined once per session logon, whereas, “WHERE” and “WHAT” are determined once per query.
After the “WHO” and the “WHERE” comes the “WHAT”
By creating workloads with the “Who”, you can easily create workloads of common interest which makes adding “What” and “Where” later less complicated.
Exception Actions
Teradata Workload Analyzer
Teradata Workload Analyzer Identifies classes of queries and recommends workload definitions (WD) and operating rules:
1. Recommends workload to allocation group mappings and Priority Scheduler weights.
2. Provides recommendations for workload Service Level Goals (SLG).
3. Can migrate existing Priority Scheduler Definitions into new workloads.
Workload Analyzer provides the conversion of existing Priority Scheduler Definitions (PD Sets) into brand new workloads. This collection of data includes the resource partition, allocation group, period type, and other definitions that control how Priority Scheduler will manage and schedule execution.
Teradata Workload Analyzer
Any query exception is automatically logged in the DBC.TDWMExceptionLog.
Skew is NOT calculated synchronously at the end of query steps but, instead Skew is checked asynchronously at the configurable time interval.
Pre- execution, Query Execution, and Post-execution
Priority Scheduler comes into play when the query executes. Each query is given a CPU weight which controls how many CPU resources are dedicated to the query. This will allow queries designed to be fast to get the resources they need, without worrying about long-running DSS queries effecting important queries designed to run in sub-seconds. Priority Scheduler is designed to bring a sense of control on Teradata systems with a lot of users and a wide mix of queries.
Why use Priority Scheduler?
Priority Scheduler will break the Teradata system in Resource Partitions and then within Resource Partitions further define Performance Groups, which will be assigned CPU weights that control the amount of CPU processing power. It is like hitting the gas on a car to go fast and the brake to slow down depending on the need for speed.
The Concept of a Resource Partition
A Large Teradata Enterprise Data Warehouse System
Imagine you have two different major organizations that make up your company. For example, you have a company that is made up of the Insurance Division and the Financial Division. Both divisions decide to bring in a Teradata data warehouse, but decide they want to have both parts of the company separated logically. A Resource Partition could logically and physically provide resources 50/50 to both divisions.
Resource Partitions
Imagine two friends catching a giant fish. They each get 50% of the fish. Now each person takes their portion of the catch to their own family for dinner. The baby only needs a little but mom and dad need more.
The Clever Idea behind Resource Partitioning
A Large Teradata Enterprise Data Warehouse System
One of the clever ideas behind the Resource Partition is that if no users in one of the Resource Partitions are logged on, the other Resource Partition (with users) will get all of the CPU power. Nothing goes to waste.
The Brilliant Idea behind Resource Partitioning
Financial has 50% power and Insurance has 50% power in their Resource Partitions. The 50% can further be divided with into Performance Groups. In this example, Financial gives 25% of their power to sales and 25% to their call center department.
The Concept of Resource Partitions and Weights?
TeraTom queries run twice as slow as BillyBob queries. HiteshP queries will run twice as fast as BillyBob queries and four times as fast as TeraTom queries.
The Concept of a Workload in a Resource Partition
Based on a User’s Account ID (and some have multiple Account IDs), their queries run in one of the four partitions. The $R partition will guarantee the fastest speeds.
How to Configure Priority Scheduler
1. Schmon command-line utility will bring you back to your UNIX or LINIX days.
2. Priority Scheduler Administrator (PSA) via Teradata Manager is a GUI that does the same thing as schmon, but in a prettier way.
3. Teradata Dynamic Workload Manager (TDWM) or Viewpoint Workload Designer when TASM workloads are enabled.
The schmon uses a command-line interface, but Xschmon uses graphical user interface that uses the X-Window system.
Here is an example of schmon in action. Don’t be prepared to understand it quite yet.
# schmon -b 1 Tactical 40 100
The Priority Scheduler Facility (PSF) provides a resource partition hierarchy that allows you to control CPU resource. This is like hitting the gas or the brake on a car because it gives the Teradata system the ability to speed up or slow down based on the circumstances in front of you. Now, queries designed for speed hit the gas and long-running queries, or batch queries, can travel long distances with steady speeds.
Workload Designer
Select the Add Content menu and then TASM and then Workload Designer. This is the first screen you will see. This is where you will create your Rulesets.
The Three Areas of the Workload Designer
Notice that there are three areas: Local, TDExpres and Active. Local is where you create and edit all Rulesets. The TDExpress is the current system that you will copy your Rulesets to and the Active will make the Ruleset Active.
How the Area of Workload Designer are Used
Before we begin creating our first Ruleset, let me first explain the concepts here. You will use the Local to Create the Ruleset. You can create many different Rulesets here. Once a Ruleset is created, you can drag that to the TDExpres (system name). You can have many different Rulesets here, also. You can then drag a Ruleset from the TDExpres to the Active area where it becomes the Active Ruleset. In the Active area, you can only have 1 Ruleset active.