Archives of the TeradataForum

Message Posted: Mon, 29 Jan 2007 @ 18:11:45 GMT

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

Assuming by "throttle" you are not referring to the function of TDWM(TDQM) but rather the DBScontrol setting(s). If yes, then the BEGIN MLOAD parameter setting TENACITY will effect this. the TENACITY run-time option in which hours is the number of hours that MultiLoad continues trying to log on when the maximum number of load jobs are already running on the Teradata Database.

The default tenacity value is 4 hours.

The Teradata Database either allows the MultiLoad utility to log on for all of the required data loading sessions, or rejects the logon because load capacity is currently completely consumed.

When the MultiLoad utility tries to log on for a new task, and the Teradata Database indicates that the maximum number of load sessions are already running, the MultiLoad utility:

* Waits for 6 minutes, by default, or for the amount of time specified by the SLEEP run-time option.

* Then tries to log on to the Teradata Database again.

The MultiLoad utility repeats this process until it has either logged on for the required number of sessions or exceeded the TENACITY hours time period.

Prabably TMI, but here is info on the DBSCONTROL setting (for R6.1):

11. MaxLoadTasks - Maximum combined number of FastLoad, MultiLoad, and possibly FastExport. This field works differently depending on the value of MaxLoadAWT.

a) If the internal field MaxLoadAWT is zero (by default), this field is the concurrent job limit for all three utilities: FastLoad, MultiLoad, and FastExport.

b) If the internal field MaxLoadAWT is changed to non-zero, this field is the concurrent job limit for only two utilities: FastLoad and MultiLoad.

The valid range is 0 .. 30. The default is 5.

NOTE: This field can only be set to greater than 15 if the internal field MaxLoadAWT is changed from the default of zero.

The MaxLoadAWT internal field serves two purposes:

1) Enabling a higher limit for the MaxLoadTasks field beyond the default limit of 15.

2) Specifying the AMP Worker Task (AWT) limit for concurrent FastLoad and MultiLoad jobs when a higher limit is enabled.

In effect, this field allows more FastLoad, MultiLoad, and FastExport utilities running concurrently while controlling AWT usage and preventing excessive consumption and possible AWT exhaustion.

Usage Notes

* The default value is zero.

* When MaxLoadAWT is zero, concurrency limit operates in the same manner as prior to V2R6.1:

- MaxLoadTasks specifies the concurrency limit for all three utilities: FastLoad, MultiLoad, and FastExport.

- The valid range for MaxLoadTasks is from 0 to 15.

* When MaxLoadAWT is non-zero (higher limit enabled),

- It specifies the maximum number of AWTs that can be used by FastLoads and MultiLoads. Maximum allowable value is 60% of the total AWTs.

- The valid range for MaxLoadTasks is from 0 to 30.

- A new FastLoad/MultiLoad job is allowed to start only if BOTH MaxLoadTasks AND MaxLoadAWT limits are not reached. Therefore, jobs may be rejected before MaxLoadTasks limit is exceeded.

- MaxLoadTasks specifies the concurrency limit for the combination of only two utilities: FastLoad and MultiLoad.

- FastExport is managed differently:

* FastExport is no longer controlled by the MaxLoadTasks field.

* FastExport limit is 60 minus number of active FastLoad and MultiLoad jobs.

* A FastExport job is only rejected if the total number of active utility jobs is 60.

* At least 30 FastExport jobs can run at any time. A FastExport job may be able to run even when FastLoad and MultiLoad jobs are rejected.

- When a Teradata Dynamic Workload Manager (TDWM) utility throttle rule is enabled, the MaxLoadAWT field is overridden. TDWM will use the highest allowable value which is 60% of total AWTs.

- Update to MaxLoadAWT becomes effective after the DBS control record has been written. No DBS restart is required.

- Note that when the total number of AWTs (specified by the internal field MaxAMPWorkerTasks) has been modified but a DBS restart has not occurred, then there may be a discrepancy between the actual number of AWTs and the DBS control record. The system may internally reduce the effective value of MaxLoadAWTs to prevent AWT exhaustion. Below are the possible cases:

- If the total number of AWTs is increased, then the actual number of AWTs is less than the value in DBS control record. In this case, the highest effective value of MaxLoadAWT is reduced internally to 60% of the actual number of AWTs until a DBS restart occurs. For example, if MaxAMPWorkerTasks is changed from 80 to 120 and MaxLoadAWT is changed from 40 to 72 and no DBS restart has occurred, then the highest MaxLoadAWT value being enforced is 60% of the actual number of AWTs which is 48, not 72. After a DBS restart, the effective value of MaxLoadAWT is 72.

- If the total number of AWTs is decreased, then update to MaxLoadAWT becomes effective after the DBS control record has been written even when a DBS restart has not occurred.

Possible confusion from the above scenarios could be avoided by restarting the DBS immediately after modifying the total number of AWTs so that the effective value of MaxLoadAWT is always consistent with the DBS control record.

- MaxLoadAWT can only be set to zero (disabling higher limit) when MaxLoadTasks is less than or equal to 15.

AWT Usage of Load Utilities

All load/unload utilities require and consume AWTs at different rates depending on the execution phase:

Utilities Phase AWTs

FastLoad Phase 1 (Loading) 3

Phase 2 (End Loading) 1

MultiLoad* Acquisition Phase (and before) 2

Application Phase (and after) 1

FastExport All 0**

*This description is for the single target table case which is the most common. A multiple target table MultiLoad job requires 2 AWTs at logon time. Then, its AWT requirement is changed to one AWT per target table after the execution of the "BEGIN MLOAD" statement (during the acquisition phase). So, a MultiLoad job may require up to 5 AWTs (maximum of 5 target tables).

**The SELECT phase of FastExport actually uses 2 AWTs but these are executed as normal DML so no AWT is counted toward the AWT limit. The export phase is processed by the Load Control Task (LCT) so no AWT is required.

- More load/unload jobs can run concurrently within a given AWT limit by overlapping their execution phases.

- As a FastLoad/MultiLoad job changes to the next phase, less AWTs are needed.

The system dynamically calculates the total AWTs required by active jobs and allows more jobs to start as AWTs become available. A new job is rejected if the AWT limit is reached.

Example

Suppose MaxLoadTasks = 30 and MaxLoadAWT = 48 (60% of 80 AWTs), the possible job mixes are:

- 16 FastLoads in Phase 1, or

- 9 FastLoads in Phase 1 and 21 FastLoads in Phase 2, or

- 24 MultiLoads in Acquisition Phase, or

- 5 MultiLoads in Acquisition Phase and 25 MultiLoads in Application Phase, or

- 6 FastLoads in Phase 1 and 15 MultiLoads in Acquisition Phase

Guidelines

- Higher utility concurrency limit is achieved by overlapping execution phases of active jobs. There is no performance improvement for individual utility. Therefore, increasing utility concurrency limit by setting MaxLoadAWT will not benefit workloads where system resources (CPU, I/O, etc.) are the bottlenecks.

- Higher limit may impact non-load operations due to higher resource consumption. MaxLoadAWT can be used to control FastLoad and MultiLoad AWT usage.

- Consider increasing total AWTs when increasing MaxLoadAWT. However, the impact on fsgcache should also be evaluated. Please discuss with the Teradata Support Center (TSC) when increasing total AWTs.

- Consider using MultiLoad rather than FastLoad because more can run concurrently, especially in case of many small jobs.

- When Priority Scheduler's AWT reservation for expedited allocation groups is used (please see Priority Scheduler's documentation for more details), the highest value of MaxLoadAWT should be reduced by the actual number of reserved AWTs. In other words, the highest recommended value for MaxLoadAWT should be 60% of the total AWTs minus the actual number of reserved AWTs. For example,

User-specified reserved AWTs 5

Internally reserved AWTs 4

---

Actual reserved AWTs 9

Total AWTs 80

Highest possible MaxLoadAWT (60% x 80) 48

Highest recommended MaxLoadAWT (48 - 9) 39

- If MaxLoadAWT is set to a non-zero value, the value should be set to 5 or higher to allow at least one Mulitload or one Fastload to run.

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