Some utility load slot questions...
1. Are there any guidelines for the number of utility load slots a Teradata system can have? Is there a limit? How is it determined.
2. How do we configure Teradata to have more or less load slots?
3. Exactly what is a utility load slot? For example, is it a dedicated AWT?
Ideally there would be a spreadsheet model that would come up with the max load slots that would not impact the other work being done on the system. Any ideas?
Update: Teradata has told us there is a hard limit of 30 utility aka load slots, and that it is difficult if not impossible to configure systems in such a way that all 30 are usable.
Has to do with certain settings in dbcControl and AWT usage. Here are some notes that I saved over the years from Jim Judge and Carrie B.:
Probably more info than you need or want but thought I would put it out there if anyone was interested...
Specifies the combined number of AMP Worker Tasks (AWTs) that can be used by FastLoad and MultiLoad at any time. It allows more FastLoad, MultiLoad, and FastExport tasks (jobs) to run concurrently, and sets a limit on AWT usage to prevent excessive consumption or exhaustion of AWT resources.
This field also acts as a “switch” on the function of MaxLoadTasks field:
• When MaxLoadAWT set to zero, the number of load utilities that can run concurrently is controlled entirely by the MaxLoadTasks field. In this case, MaxLoadTasks specifies the maximum number of combined FastLoad, MutiLoad, and FastExport jobs that can run concurrently.
• When MaxLoadAWT is set to an integer greater than zero, MaxLoadTasks applies only to the combined number of FastLoad and MultiLoad jobs, which are also limited by the MaxLoadAWT setting. In this case, the number of FastExport jobs that can run is always 60 minus the number of combined FastLoad and MultiLoad jobs currently running.
If MaxLoadAWT is set to a non-zero value, it should be a value greater than or equal to five, which allows at least one FastLoad and one MultiLoad job to run concurrently.
The maximum allowable value is 60% of the total AWTs per AMP. By default, the maximum number of AWTs started for each AMP vproc is 80, so the default maximum value for MaxLoadAWT is 48.
Consider using MultiLoad, rather than FastLoad, especially in cases of many small load jobs. MultiLoad generally consumes fewer AWTs per job than FastLoad.
The MaxLoadAWT field works together with the MaxLoadTasks field to limit the number of concurrent load utilities allowed to run:
• If MaxLoadAWT is zero (the default):
• MaxLoadTasks can be an integer from zero through 15.
• The MaxLoadTasks field specifies the maximum number of combined FastLoad, MultiLoad, and FastExport jobs that can run concurrently.
• The system does not consider the number of available AWTs when limiting the number of load utilities that can run concurrently.
• If MaxLoadAWT is greater than zero:
• MaxLoadTasks can an integer from zero through 30.
• The MaxLoadTasks field sets the maximum number of combined FastLoad and MultiLoad jobs that can run concurrently. MaxLoadTasks does not directly limit the number of FastExport jobs that can run.
• The number of combined FastLoad and MultiLoad jobs that can run concurrently is limited by the values of both the MaxLoadTasks field and the MaxLoadAWT field. When either limit is met, no further FastLoad or MultiLoad jobs are allowed to start until the limiting factor is reduced. See “About AWTs”.
• The maximum number of load utility jobs of any type—FastLoad, MultiLoad, or FastExport—that can run concurrently is 60. Consequently, the number of FastExport jobs allowed to run at any time is 60 minus the number of combined FastLoad and MultiLoad jobs that are running.
For example, if the sum of currently running FastLoad and MultiLoad jobs is 29, the number of FastExport jobs that can be started is 31 (60 minus 29), regardless of the MaxLoadAWT and MaxLoadTasks settings.
• If MaxLoadAWT is set to anything greater than zero, it can only be reset to zero if MaxLoadTasks is 15 or less.
Because load utilities share system resources with other system work, such as tactical and DSS queries, limiting the number of load utility jobs can help ensure sufficient system resources are available for other work.
FastLoad and MultiLoad require different numbers of AWTs at different phases of execution. The following table shows how many AWTs are used at each phase.
1 per target tabl
Load Utility and Phase
Number of AWTs Required
FastLoad: End Loading
Assume that MaxLoadAWT = 48 and MaxLoadTasks = 30. The list below shows some permitted combinations of load utility jobs. The limiting condition(s) for each combination is shown in bold:
• 16 FastLoads in Loading phase
16 concurrent load tasks
48 AWTs in use: (16 x 3)
• 9 FastLoads in Loading phase and 21 FastLoads in End Loading phase
30 concurrent load tasks
48 AWTs in use: (9 x 3) + (21 x 1)
• 24 MultiLoads in Acquisition phase
24 concurrent load tasks
48 AWTs in use: 24 x 2
• 5 MultiLoads in Acquisition phase and 25 MultiLoads in Application phase
30 concurrent load tasks
35 AWTs in use: (5 X 2) + (25 x 1)
• 6 FastLoads in Loading phase and 15 MultiLoads in Acquisition phase
21 concurrent load tasks
48 AWTs in use: (6 x 3) + (15 x 2)
Correction above... What I pasted into the response was not the notes I collected from Jim Judge and Carrie B. over the years. This is straight out of a manual.
Note that if you activate TASM utility throttles, the MaxLoadTasks and MaxLoadAWT from dbscontrol are no longer used. But the AWT usage rules still apply.
It can be difficult to determine how many "loads" could be run at a time to not impact other production work. For example a single large mload could have more impact than several small fastloads due to duration and table locks that it can hold as well as cpu resources it can consume if it is a delete/update. You can also setup TASM rules to limit how many utility slots are consumed by a given user or workload. So if you wanted to control how many could be used in order to protect the system you can do that. For example we allow ETL to be able to run 15 fastloads but never more than 10 mloads any job past that limit goes into the queue to wait. We limit adhoc users of fastloads to one utility slot so they can't overconsume and impact production.
You can look at dbql tables and determine how many loads are running concurrently (its a little messy but doable) since for fastload/mload/fastexport you have to look at the LSN to see which sessions belong to the same job.
Out of the box Teradata supports 5 concurrent load slots (dbscontrol setting) which can be changed to 15, and if you change some other settings in dbscontrol then you can support more load slots based on the information that Joe mentions above. Also, if you don't use the MaxLOadTasks and MaxLoadAWT to go past the 15 load slot limit (or the TASM Utility Throttle) then fastexport will also count towards that 15 utility slot limit (I believe that is still correct).
We have a very peculiar problem.
We are using TD14.10. We have system level throttle for TPT Multiload and is set to 15. We are trying to run 7 MLOAD through TPT concurrently and is triggering 230 sessions.
Out of that 222 sessions, are moving to IDLE state and other 8 sessions are in delayed Queue. These 222 sessions are remaining in Queue for more than 20 mins and is not getting activated. It is remaining in the same state and it is not failing too. There is nothing active during this time in the system.
if we are running 6 MLOAD jobs concurrently then everything is running smoothly without any issue.
Is it the scenario, system is expecting all the 230 sessions to start and is kind of deadloack as remaining 8 jobs in delay Queue and waiting for it and will never get it. Is it the case here?
Please let me know, what is the issue here.
Thanks in advance.