AWTs are the heart of the AMP, responsible for executing tasks and ensuring the smooth functioning of the system. AWTs are threads that process incoming tasks in the AMP. Each AMP has a finite pool of AWTs, which is shared among all the tasks that the AMP needs to execute.
When a request is received, the Parsing Engine (PE) breaks it down into steps, which are then assigned to the appropriate AMPs. Each AMP allocates an AWT from its pool to execute the assigned task. Once the task is completed, the AWT is released back into the pool, making it available for other tasks.
The Need for Efficient AWT Management
Efficient management of AWTs is critical to the overall performance of a Teradata system. Since the number of AWTs in an AMP is limited, poor AWT management can lead to bottlenecks and degraded system performance. The following factors contribute to efficient AWT management:
AWT Allocation: The Teradata system allocates AWTs based on priority levels, ensuring that higher-priority tasks are processed first. This helps maintain optimal system performance and prevents low-priority tasks from monopolizing the available AWTs.
Workload Management: Teradata offers advanced workload management tools, such as the Teradata Active System Management (TASM) and the Teradata Workload Management (TWM), which enable administrators to monitor and manage AWT usage. These tools help balance workloads, assign appropriate priorities, and prevent AWT exhaustion.
AWT Tuning: Teradata provides various configuration settings that allow administrators to fine-tune AWT usage. Adjusting these settings can help optimize system performance and prevent AWT-related issues.
Monitoring: It is essential to monitor AWT usage regularly to identify potential bottlenecks and take corrective action before they impact system performance. Administrators can use tools like Teradata's ResUsage to monitor AWT usage closely.
Message Queuing in Teradata AMPs
When an AMP receives a message, it places the message in a queue to be processed by an AWT. Each AMP has separate message queues based on the priority levels, ensuring that higher-priority tasks are handled first. The main types of message queue in a Teradata AMP are
Expedited Queue: This queue holds high-priority messages that require immediate attention, such as transaction control messages.
Work Queue: This queue stores standard priority messages, including data retrieval, storage, and manipulation tasks.
Delayed Work Queue: This queue contains lower-priority messages, such as maintenance tasks or background processes, that can be deferred.
The AWTs in an AMP pick messages from these queues based on their priority level and execute the tasks associated with them. Once an AWT becomes available, it selects the next message from the highest-priority queue with messages waiting for processing.
Handling Full Message Queues
When an AMP's message queue is full, it can no longer accept new messages, leading to a bottleneck in the system. A full message queue can occur for various reasons, such as insufficient AWTs, inefficient workload management, too many load utilities running in parallel, or a sudden spike in query volume.
When a message queue is full, the following consequence may occur
Flow Control: Teradata employs a flow control mechanism to handle situations when message queues are full. When an AMP's message queue reaches a predefined threshold, it sends a flow control message to the PE, indicating that it cannot accept more messages. The PE then stops sending messages to that AMP and retries after a short delay to send the message again. This mechanism helps prevent message loss and ensures that AMPs can handle requests.
Impact on System Performance: A full message queue can significantly impact overall system performance, as it can cause delays in processing tasks and increase query response times. In extreme cases, it can even lead to query timeouts and failures.
Teradata administrators must regularly monitor AWT usage and message queue levels to prevent message queues from becoming full and maintain optimal system performance. They can employ workload management tools like Teradata Active System Management (TASM) or Teradata Workload Management (TWM) to balance workloads, assign appropriate priorities, and manage system resources effectively. Additionally, tuning AWT configurations and optimizing query performance can help alleviate the risk of full message queues and ensure smooth system functioning.In Teradata, tactical queries are short, time-critical queries that require swift processing to support real-time decision-making and operational tasks. Teradata provides reserved AMP Worker Tasks (AWTs) specifically for these queries to ensure that tactical queries receive the necessary processing resources and complete quickly.
Reserved AMP Worker Tasks for Tactical Queries
Teradata allows administrators to configure a portion of the AWT pool in each AMP to be reserved exclusively for tactical queries. This reserved pool is separate from the general AWT pool used for other types of queries. By reserving AWTs for tactical queries, Teradata ensures that these critical, time-sensitive queries are not delayed due to resource contention with other, less urgent queries.
The number of reserved AWTs can be adjusted according to specific needs, considering the anticipated volume of tactical queries and the desired response times. It is essential to balance allocating enough reserved AWTs for tactical queries and maintaining sufficient AWTs in the general pool for regular queries to prevent performance issues.
Tactical Query Execution
When a tactical query is submitted, the Parsing Engine (PE) identifies it based on its workload classification, typically determined by query complexity, resource usage, and priority. Once identified, the PE generates messages for processing to the appropriate AMPs.
Upon receiving a message related to a tactical query, the AMP checks for available reserved AWTs. If a reserved AWT is available, the AMP allocates it to the tactical query, ensuring it receives priority processing. If no reserved AWTs are available, the tactical query may need to wait for an AWT to become available or be processed using a regular AWT, depending on the system configuration.
Workload Management for Tactical Queries
Teradata provides advanced workload management tools, such as Teradata Active System Management (TASM) and Teradata Workload Management (TWM), which enable administrators to manage and prioritize tactical queries effectively. These tools allow administrators to classify queries based on predefined criteria, assign appropriate priorities, and allocate resources accordingly. Administrators can use these workload management tools to ensure tactical queries receive the required resources and maintain optimal system performance.
Teradata enables tactical queries to access reserved AMP Worker Tasks to ensure the fast and efficient processing of these time-sensitive requests. Organizations can prioritize and process tactical queries effectively by properly configuring reserved AWTs and using workload management tools, supporting real-time decision-making and operational tasks.
AWT requirements for Utility Workload
Different Teradata utilities require different amounts of AMP Worker Tasks (AWTs) to manage their workload effectively. The AWTs are responsible for performing the work assigned to AMPs, and their allocation varies depending on the utility and workload requirements.
FastLoad:
FastLoad is a utility that loads large volumes of data into empty tables at high speeds. This utility consumes more AWTs because it divides the data into multiple blocks, which are then processed concurrently by AMPs. The parallel execution of tasks requires a substantial allocation of AWTs to ensure optimal performance.
Teradata Fastload needs 3 AMP worker tasks in the first phase (acquisition phase) and 1 AMP worker task in the apply phase (all per AMP). This is why the number of parallel running fastloads and multiloads is limited to a small two-digit range (the default setting can be adjusted as needed). An SQL statement typically only needs 1-2 AMP worker tasks per AMP.
MultiLoad:
MultiLoad is a Teradata utility designed for high-volume maintenance of large databases. It supports the concurrent loading, updating, and deleting of data. As it performs multiple operations simultaneously, MultiLoad needs a moderate to high amount of AWTs for efficient execution. The exact number of AWTs required depends on the complexity and concurrency of the operations involved.
AMP Worker Task Requirements in MultiLoad
During the Acquisition phase of a MultiLoad job, irrespective of the number of sessions utilized, each AMP requires both a sender and receiver AWT. The sender AWT, a work00 work type, accepts rows from the client and redistributes them to the appropriate AMP based on the Primary Index (PI) value. Meanwhile, the receiver AWT (work01 work type) receives rows from other AMPs, consolidates them, and writes them to disk.
Generally, each MultiLoad job will require two AWTs per AMP during the Acquisition phase and one AWT per AMP during the Apply phase.
Active and Inactive Sessions
When the number of sessions is equal to the number of AMPs in the system, each AMP actively participates in a session and processes data from the client. If the number of sessions is less than the number of AMPs, some AMPs will not actively engage with a client session. However, each AMP will still have a sender AWT set up.
Some MultiLoad sessions may appear "inactive" in Viewpoint Query Session at certain times, meaning that the session/AMP is waiting for data from the client. In contrast, "active" status indicates that the AMP is actively processing data sent from the client.
A session may alternate between active and inactive multiple times during a load job, particularly when many sessions are specified. A client machine or network may not be able to keep all sessions/AMPs busy simultaneously. Since the client sends data round-robin across all sessions, different sessions/AMPs may be active at varying times.
When a MultiLoad job employs only a few sessions, it is more likely that all sessions will be active for the majority of the time. Whether sessions are active, inactive, or a combination, all AMPs will retain their sender and receiver AWTs throughout the Acquisition phase.
Activities During the Acquisition Phase
During the Acquisition phase of a MultiLoad job, the following actions take place:
All sender AWTs are initially set to the inactive state.
The client sends multiple rows in a message to a sender AWT.
The sender AWT that receives rows from the client:
- Sets its state to active.
- Unpacks the rows in the message and converts them to the internal format.
- Sends converted rows to the correct AMP's receiver AWT based on the row's hash code.
- Sets its state to inactive.
- Sends a response to the client and waits for the next message.
- Meanwhile, the receiver AWTs process received rows independently.
MultiLoad and MAPS
When updating a table using MultiLoad that resides in a map covering only a subset of the AMPs in the system, only the AMPs in that map will be used for the load activity. For instance, if you load data into a table located in TD_Map1, TD_Map1 covers only half of the AMPs in the current configuration. Only the AMPs in TD_Map1 will be used to determine the default number of sessions and support the load job. In such cases, only the AMPs in TD_Map1 will require the active sender and receiver AMP Worker Tasks during the Acquisition phase.
Summary
This chapter provided an overview of the Teradata MultiLoad utility, its relationship with AMP Worker Tasks, and the impact of using different numbers of sessions during a load job. We examined the role of AWTs during the Acquisition and Apply phases and discussed the significance of active and inactive sessions. Finally, we explored how MultiLoad interacts with MAPS in cases where only a subset of the AMPs in the system is used for the load activity.
Understanding the relationship between MultiLoad and AMP Worker Tasks is crucial for optimizing performance in a Teradata environment. By carefully considering the number of sessions employed, the system's configuration, and the distribution of data across AMPs, administrators can ensure that data is loaded efficiently and effectively, resulting in optimal performance for data processing and management tasks.
TPump:
TPump is a real-time data-loading utility that uses row-by-row processing to insert, update, and delete data. It consumes a relatively lower number of AWTs than FastLoad and MultiLoad, as it operates on a smaller scale and focuses on continuous data ingestion. Since it processes one row at a time, the allocation of AWTs per AMP is lower, allowing for a smoother, more controlled workload distribution.
FastExport:
FastExport is a utility that extracts large volumes of data from Teradata tables and exports it to external systems. The AWTs required by FastExport are determined by the size of the data set, the number of AMPs involved, and the desired level of parallelism. The more parallelism required, the higher the number of AWTs needed to manage the workload efficiently.
In summary, different Teradata utilities have varying AWT requirements based on their functionality, scale, and level of parallelism. To achieve optimal performance, it is essential to allocate an appropriate number of AWTs according to each utility's specific workload and requirements.
Recommended number of AWTs per AMP
Are you facing slow performance issues on your Teradata platform and considering increasing the number of AMP worker tasks (AWTs) per AMP? Hold on! Let's take a look at the considerations and limitations.
By default, 80 AWTs per AMP and up to 500 AWTs/AMP can be defined, but the practical maximum is usually in the mid-200 to high 300 range. Increasing AWTs/AMP above the default of 80 is useful for sites that have taken some AWTs out of the unassigned pool for tactical reserves.
However, when thinking about increasing AWTs, it's essential to consider the number of AMPs per node. Too many active tasks on a node can cause tasks to be swapped in and out more frequently, heating competition for resources. Increasing AWTs needs to be done more conservatively when the platform supports many AMPs per node.
Guidelines for increasing AWTs have not changed much over the years. Consider more AWTs/AMP when spare resources cannot be consumed, AWTs are the bottleneck, there is adequate memory, reserved AWTs are being used to support tactical workloads, or Work01 cannot get AWTs when needed. But don't use an increase in AWTs as a substitute for sensible tuning. Throwing more AWTs at the AMPs won't address skewed processing or poorly-designed applications.
Finally, increasing AWTs is a software change that should be done carefully. Small, gradual changes are better than big, bold ones.