Teradata manages the workflow in a scalable and decentralized way on each AMP. Each AMP handles all the workload assigned to it on its own. Each AMP keeps track of the amount of work accepted, how much work is active, and how much work is waiting in a queue to be executed.
Flow control mode happens when the workload on an AMP reaches a specific limit. It will decline any further work assigned to it by the parsing engine. The state of an AMP not allowing any additional work is called flow control.
The AMP will decline any new message in flow control mode, such as new work, rows sent by other AMPs, or internal system work.
Any message arriving on an AMP tries to get an AMP worker task to fulfill the required task. In the case of an All-AMP operation, such as a full table scan, it might happen that not all-AMPs have free AMP worker tasks available.
Messages received by an AMP can be queued, and not all-AMPs have to begin immediately to work on the All-AMP task.
Still, each AMP’s message queue can only hold a limited number of messages per work type (new work, second-level work, row distribution, etc.). Grouping queue messages by work type is used to prioritize them. For example, new work is not as important as ongoing work needed to finish an already started task.
When message queues reach the limit, further messages of the work type are no longer accepted. The dispatcher is informed by the AMP and will retry to send the messages until the AMP finally retakes them. If only one AMP closes the gates, none of the AMPs will accept further work for the same message.
One or more AMPs in the flow control state are usually a sign of system overload. Proper workload management must be applied to bring the AMPs back to normal.
Flow control mode can cause severe performance problems if it happens repeatedly and over an extended period. Single occasions of flow control every once in a while and for a couple of seconds are acceptable.
Frequent flow control situations over an extended period should be analyzed in detail.
Below is a simple test query, which gives detailed information about flow control on a Teradata system.
It delivers, per sample period (i.e., the end of the period), the maximum tasks of type “new task,” “continued task,” the size of jobs queued, the number of flow control events, and the maximum parallel flow control events which occurred during the sample period.
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.