Introduction
Teradata efficiently manages complex workflows by distributing and expanding processes across numerous AMPs. However, an AMP’s maximum capacity can initiate flow control mode. This blog post delves into Teradata’s Flow Control Mode, its impact on performance, and effective monitoring and management strategies.
How it works
Teradata’s decentralized architecture distributes workload to individual AMPs, which operate autonomously and monitor their workload by tracking active and queued tasks and the overall amount of work accepted.
When an AMP’s workload exceeds a set limit, it activates flow control mode, which results in the AMP refusing additional tasks assigned by the parsing engine. The AMP rejects new messages in this mode, such as work assignments, row distributions from other AMPs, and internal system tasks.
Upon receiving a message, AMP endeavors to assign an AMP worker task for processing. Nevertheless, in the case of an All-AMP operation such as a comprehensive table scan, not every AMP may possess available worker tasks. Messages are subject to being queued, enabling certain AMPs to postpone processing while others focus on the All-AMP task.
The message queue of every AMP has limitations for various types of work, such as new tasks, second-level tasks, and row distribution. These types of work aid in prioritizing tasks, giving precedence to ongoing tasks over new assignments.
When a message queue is full, additional messages of that category are declined. The dispatcher is alerted and will persistently retry sending the messages until the AMP ultimately approves them. If any AMP reaches capacity, no other AMPs will receive additional work for that particular message type.
When AMPs enter flow control mode, it may suggest system overload, requiring efficient workload management to restore regular operation. Although infrequent and momentary flow control incidents are tolerable, repeated and prolonged ones can result in significant performance complications. In such situations, a comprehensive analysis is necessary to recognize and resolve the fundamental issue.
This query yields information on flow control in a Teradata system, including the maximum number of tasks, queued jobs, flow control events, and parallel flow control events for each sample period.
SELECT
TheDate
,TheTime
,MAX(WorkTypeMax00) AS max_wrk0
,MAX(WorkTypeMax01) AS max_wrk1
,MAX(MailBoxDepth / CollectIntervals)
,SUM(FlowControlled)
,MAX(FlowCtlCnt)
FROM DBC.ResusageSAWT
WHERE TheDATE BETWEEN DATE'2023-01-01' AND DATE
GROUP BY 1,2
Conclusion
Understanding the function and impact of Teradata Flow Control Mode is crucial in managing workflows and achieving optimal performance in a Teradata data warehouse. Administrators can effectively monitor and manage workloads by comprehending their significance, minimizing disruptions, and sustaining peak efficiency. Promptly addressing system overloads by monitoring flow control events ensures a robust, reliable, high-performing data warehouse solution.
Hi DWHPro staffs,
I was trying to run the below sql, but it complained about CollectIntervals column does not exist. So what is the correct column name for this sql instead of CollectIntervals column?
Thanks and Regards
Vianh Cao
SELECT
TheDate
,TheTime
,MAX(WorkTypeMax00) AS max_wrk0
,MAX(WorkTypeMax01) AS max_wrk1
,MAX(MailBoxDepth / CollectIntervals)
,SUm(FlowControlled)
,MAX(FlowCtlCnt)
FROM DBC.ResusageSAWT
WHERE TheDATE between ‘2022-07-11’ and ‘2022-07-15’
GROUP BY 1,2;
Hi Ronald – Thanks so much for explaining this topic in a very simple manner.