Amp Worker Tasks behavior using STREAM or TPUMP operator.

Tpump now called stream (TD12 - Teradata implementation for real time trickle feed) under the covers of TPT; it’s a row level operation which based on the hashing algorithm does the DML operation on the hash row. There could be an application level contention based on the NUPI defined on the table having operations on the same rowhash ID. This can be controlled by proper configuration of the parameters and settings (SLEEP, TENACITY, SERIALIZATION, SESSIONS etc). The session blocking is only momentary but sometimes this can lead to overhead contention and nuisance alerts to the DBAs. Having more sessions is as harmful for performance as having as little as one session, so careful analysis in deciding the sessions will not only improve TPUMP throughput but also improve overall system performance.

System level design for TPUMP operation w/ table designed as fallback

When a DML operation is performed on a fallback table P2P (point-to-point) protocol message is spawned from the main primary AMP to the fallback AMP. The fallback AMP now has the copy as a fallback. This entire operation needs two AWTs (Amp Worker Task) doubling the number as opposed to regular non fallback DML operation.

For e.g.: How it works when a user submits an insert operation on table with fallback definition
Steps:
1. An Amp Worker Task is assigned on the AMP that has the primary copy. Once the insert is successful a message is transmitted to the fallback AMP.
2. Once the fallback AMP receives the message it will try to insert into its AMP so needed another AWT, the AWT on the primary is not relinquished till the operation is successful on the fallback. So two AWTs are on hold until the fallback operation is successful. If the fallback AMP doesn’t get the AWT right away it will be in the queue and the primary AMP will not relinquish its AWT. You can call it as a design flaw or data integrity but that is how it works.

So if you are seeing any delays on the top of deadlocks that TPUMP has on because of multiple sessions, it also could be AWTs which are so minimal but on a busy day with a flow control situation, it worsens to the whole performance package.

Recommended actions/solutions:

1. I don’t recommend this but some sites do this; for Tpump Jobs you can specify some reserved AWTs. Because reserving an AWT will have a 2n+1 rule in place, where n is the number of AWTs to be reserved. The spawning of AWTs make it worse when we reserve.
2. Have both object and workload throttles to have so many AWTs available in the spool so that TPUMP jobs have good throughput. I recommend this approach and it worked much better.