Archives of the TeradataForum

Message Posted: Wed, 25 Sep 2002 @ 23:14:08 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

"Does anybody have some information or a document that would help estimate the number of AMP Worker Tasks (AWTs) that are required by the various utilities, SQL operations or the steps from an EXPLAIN?"

1. No, there is no comprehensive, quantitative documentation of which I am aware. If you find some, please share it. There is some of the NCR@YourService customer web site.

2. I have been told the following unofficially. If this contradicts your experience, then believe your experience.

2.1 There are 80 AWT per AMP.

2.2 62 of the 80 are used for the execution of user SQL.

2.3 Each "active" session, for each AMP state of "active" uses at least one AWT on that AMP.

2.4 AMP states of "idle" use no AWTs on that AMP.

2.5 Some functions such as product joins, merge joins, and aggregations use two AWT per active AMP. Parallel steps may use two AWT each.

2.6 Therefore, for all AMP steps in all sessions, there is a limit of 62 active sessions regardless of how many nodes are present. Of course, increased nodes, decreases the amount of work done each AWT, decreasing service time, decreasing queue time, and increases system throughput and responsiveness.

2.7 Two AWTs are reserved for processing rollbacks, if necessary.

2.8 Unbalanced (skewed) SQL requests will create "hot AMPs": a shortage of AWT on one AMP.

2.9 The MP RAS UNIX utility named puma (#puma -c report) will display the number of AWT in use at the time puma is run. There is UNIX man page documentation on puma. There is documentation on the NCR@YourService web site.

3. I have observed the following:

3.1 When the number of active sessions increases above 32-40 (Teradata Manager 5.0.1 Sessions display), end user response time increases drastically. This depends on the number and type of one or two AWT steps being executed in the active execution plans.

3.2 When the number of active sessions (one with at least one AMP in the active state) rises to 72 or 80, end user response time becomes abysmal to the point of near system lockup.

3.3 Tuning SQL, applying NCR's recommended DRs, balancing workload (moving unattended tasks to evening or night) has a great impact on reducing AWT queue time, service time, and system throughput and responsiveness. Adding nodes also helps.

4. NCR has no quantitative queueing theory or ops research model of the Teradata (of which I am aware). And there is no data (of which I am aware) made available to customers which would support such a model.

5. Rough and qualitative "by the seat of your pants" tuning by yourself and NCR support will get one through the rough periods.

If you discover quantitative methods and data, it would be helpful. In the meantime, Mr. Pluebell's advice is sound and the best.

	<Prev	Next>		<<First	<Prev	Next>	Last>>