AMP Worker Task Resource Limits & Increased Load Limits for MLOADX

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

MLOADX is a new load utility enhancement introduced in Teradata Database 14.10 which is intended to overcome some of the limitations that are associated with standard MultiLoad.  This posting focuses on how workload management options will open up MLOADX for more expanded usage in Teradata Database 15.10.

Note:  When the term “MultiLoad” is used in this posting, it also refers to the Teradata Parallel Transporter (TPT) Update Operator utility, which uses the same protocols and exhibits the same behaviors.

Pre-15.10 Utility Workload Management Controls

Let’s first look at the traditional workload management controls that exist for load utility jobs in pre-15.10 releases.  Whether or not you have defined your own utility throttles, default internal utility throttles are in place on all Teradata platforms to limit the number of utility jobs that are allowed to run in combination.  The number of load slots that are available at any point in time for FastLoad (TPT Load) and/or MultiLoad (TPT Update ) are limited to 30.  And if you add in Fast Export (TPT Export) the limit for all three combined is 60.

In addition to default utility job limits, there are internal limits on how many AMP worker tasks (AWTs) can be in use by load utilities at any point in time.   The current AWT limit for utility jobs is 60% of the total number of AWTs per AMP.   If the default of 80 AWT per AMP are in place, all active load utilities would be able to use up to 48 AWTs at a time.  With 120 AWTs per AMP defined, up to 72 could be used to support load utilities.

This limit on the number of AWTs used by load utilities is referred to as an “AWT Resource Limit”.   It is not enforced at the workload level, but rather system-wide.  The existence of this rule prevents utility jobs as a group from using an excessive number of AMP Worker Tasks (AWTs), and possibly impacting other non-utility work.  The internal AWT resource limit rule is defined with a delay action.

The counter associated with this resource limit is incremented and decremented based on an estimate of the number of AWTs that will be used by the particular phase of the load job.  The estimate is based on table attributes, and is generally accurate.  This default AWT resource limit rule is not displayed in Viewpoint Workload Designer, at least not until 15.10.  

Why MLOADX?

There have always been a few cases where the MultiLoad utility was just not flexible enough to do the job that was needed.  Which tables MultiLoad is able to operate on depends on the characteristics of the target tables themselves.  For example, MultiLoad is not able to load into tables that have unique secondary indexes, referential integrity, or join indexes. Neither can it operate on tables that include trigger definitions or involve large objects.  Tables with those types of characteristics have always had to look for other non-utility solutions to loading rows into a populated table.

An enhanced version of MultiLoad, called “MLOADX”, overcomes these long-term limitations of the standard utility.  MLOADX uses SQL sessions to load tables, and so is able to handle join indexes, triggers and other physical design attributes of a table that standard MultiLoad cannot.  It loads the data into a NoPI temporary table during the acquisition phase, then performs a MERGE-INTO step in the application phase to merge the updates into the base table, along with any steps required to update attributes such as join indexes.  Because MLOADX is SQL-based a larger number of MLOADX jobs are able to run at the same time.  

MLOADX is available for use as of 14.10, under the covers of the TPT update operator.  Although it is considered an enhancement to MultiLoad, MLOADX can only be invoked through TPT Update, not through the legacy MultiLoad utility.  

For more information about how MLOADX works, see the Teradata Parallel Transporter Reference Guide 15.10, the chapter on the Update Operator.

The decision whether or not to use MLOADX will be made for you automatically when the TPT Update Operator is invoked, without you having to specify that it be used.  If the target table has restrictions, such as a unique secondary index, TPT Update will automatically switch to the MLOADX protocols. A user may not even be aware that MLOADX is being used when they submit a TPT update job.

In the 14.10/15.0 timeframe, MLOADX responds to workload management rules similarly to legacy MultiLoad. 

MLOADX is restricted by the 30 concurrent load job limit that has traditionally been in place for utilities, and has to honor to same AWT resource limit as do other active load utility jobs.   

However, because MLOADX is capable of handling target tables with USI, JI, RI, it is expected that more applications over time will turn to MLOADX for better performance, including some of those that are currently using TPump.  For that reason, MLOADX users may begin to bump up against the limit of concurrent load jobs and AWTs that can be used that is present in the 14.10 and 15.0 environments.

MLOADX Workload Management Opportunities in 15.10

Opportunities open up for MLOADX in 15.10.  MLOADX will be able to do more in that release, such as load JSON data as well as large objects (LOBs).  But the changes we are focused on in this write-up are the enhancements to workload management that will give you the opportunity to use more AWTs for MLOADX and to run more concurrent MLOADX jobs than were supported in earlier releases. 

There is a new capability in TASM and TIWM to count and throttle MLOADX jobs separately, so that higher limits can be specified for them, independent of other load utility jobs. You will be able to set an individual utility throttle limit just for MLOADX jobs, apart from the other load utilities.  

But the opportunity for more flexible workload management rules with MLOADX in 15.10 is not automatic.  Once you get to 15.10 you will be offered the option to activate expanded MLOADX support by turning on a flag within Viewpoint.  To do this you will go to Workload Designer General category and click on a new “Other” tab.  At the bottom of the resulting screen you will see a box that is unchecked, which says “Support increased MLOADX job limits and increased AWT resource limits”.   Once you check that box you will automatically get new internal system defaults for just MLOADX jobs, apart from other utility jobs on the platform.

Here is what the screen under the General "Other" tab looks like after you have checked the MLOADX option at the bottom:

Increasing Job Concurrency with MLOADX

As mentioned above, the master switch to turn on expanded MLOADX usage is at the bottom of the “Other” tab under the General category.  By default, the flag that gives you higher concurrency for MLOADX jobs is off in order to be backward compatible with earlier releases.  If the box is not checked, then all load utilities combined, including MLOADX, will be limited to no more than 30 concurrent jobs at a time.

Below is a screen shot showing you what you will see under the Sessions category when you go to the “Utility Limits by State” tab prior to checking the Support increased MLOADX job limits box.  This is the screen you would go to see the system default utility limits, as well as any new utility throttles you may have defined. As you can see, MLOADX is limited by default to the same number of concurrent load jobs as the other load utilities, 30.

Once you check the Support Increased MLOADX job limits box in the General category that was shown earlier, and go back to the Utility Limits by State tab you will see a change has been made to the MLOADX listing:

The internal default limit for MLOADX has automatically been increased to 120 concurrent jobs.   This upper limit of 120 was selected looking into the future, as more and more applications are expected over time to embrace MLOADX, including many running TPump today.

If 120 jobs is a higher level of concurrency for MLOADX than you wish to support, you can setup a new system-level utility throttle rule specifically for MLOADX jobs that limits MLOADX to a somewhat lower level of concurrency system-wide.  

In order to set up a system-wide utility throttle for MLOADX that is higher than 30 but less than 120, create a new utility throttle with classification criteria that only specifies MLOADX.  Then go to the State Specific Settings tab and specify whatever job limit you wish, up to 120.  Until such time as you define one or more customized MLOADX utility throttles, the new default of 120 concurrent MLOADX jobs will be in effect.

Once you have created your new system-wide MLOADX throttle to control the total number of MLOADX jobs that will be allowed to run concurrently, you can also define different workloads to support MLOADX jobs.   If you set up multiple different workloads with utility classification specific to MLOADX, you can differentiate those workloads by adding other classification, such as user or account, thereby separating MLOADX jobs.   Once separate workloads are defined for MLOADX jobs coming from different sources, you can add workload throttles that allow different numbers of MLOADX jobs to run for different applications or users.

How MLOADX Jobs use AMP Worker Tasks

AWT usage by a MLOADX job is different from that of a standard MultiLoad job, although MLOADX is similarly composed of an acquisition and an application phase.

During the acquisition phase array INSERT requests are used to populate data into a NOPI staging table. An available AWT is assigned to each Array INSERT request and then freed up as soon as the request completes. Therefore, AWTs are not held up for the duration of the acquisition phase. The number of AWTs in use during this phase at any point in time is similar to TPump, and it depends on the number of SQL sessions you have defined for the job.

In the application phase each MultiLoad DML will be replaced with a MERGE-INTO statement that will be processed in the utility’s control SQL session.  Contrary to the acquisition phase, AWTs used during the application phase will typically be held on all AMPs for the duration of the phase.  How many AWTs will be needed depends on the attributes of the table undergoing updates.  Characteristics such as fallback, USI indexes or RI constraints may require additional AWTs. 

Join index maintenance is normally done by separate steps executed after MERGE-INTO has completed its main DML processing.  These join index maintenance steps will require various combinations of short-term and long-term AWTs depending on the MERGE-INTO DML as well as on the number of join indexes and their detailed definitions.

Increasing AWT Usage for MLOADX

Once you check the MLOADX flag in the General category, a new AWT resource limit is spun off as a separate resource control mechanism just for MLOADX jobs,  similar to the automatic creation of a system-wide MLOADX utility throttle.  As mentioned earlier, a single AWT resource limit rule is enabled by default for all load utilities combined.  When you first upgrade to 15.10, the same AWT resource limit of 60% will be in place, and will include AWTs used by all MLOADX jobs.    

Once you have checked the MLOADX flag, a separate AWT resource limit rule will be set up exclusively for MLOADX jobs.  That rule will allow 70% of the total AWTs to be used by MLOADX jobs in combination.  It will have an action to delay, as do all the internal resource limits rules.

An AWT resource limit rule of 70% means that 70% of the total number of AWTs defined per AMP will be able to be used by multiple MLOADX jobs.  If the default of 80 AWTs/AMP is defined, as specified in DBS Control Internal Field 4 (named “MaxAmpWorkerTasks”), then MLOADX jobs combined could use up to 56 of those AWTs.

Setting the MLOADX flag will not only create a separate AWT resource limit for MLOADX, it will also cause the AWT resource limit for all load utilities (including MLOADX) to be increased from 60% to 70%.  These two default AWT resource limits, one for MLOADX and one for all load utilities (including MLOADX), are completely separate and will be enforced independently. However, both must be satisfied before any given MLOADX utility will be allowed to run.

The MLOADX AWT resource limit does not set aside or reserve AWTs just for MLOADX jobs.   But if there are available AWTs, MLOADX jobs will be allowed to use them up to a limit of 70% of the total AWTs defined per AMP.  The resource limit on the standard utilities works the same way; it’s just an upper limit, not an entitlement.

Under the Throttles category is a new tab called “Resource Limits”.  That is the location where you can define one or more additional AWT resource limit rules (the only type of resource limit currently available).  When a user-defined AWT resource limit rule is created the user has the option of the action (delay or reject) which will be taken if the rule is exceeded.  

To set up a new resource limit rule for MLOADX jobs, you give the rule a name and associate it with MLOADX jobs only. 

You may want to create new AWT resource limits for these two reasons:

  1. You want a system-wide resource limit that will enforce a lower percentage of AWTs for MLOADX jobs than the system default of 70%.
  2. You want to create different AWT resource limits for MLOADX jobs for different groups of users or applications.

Below is an example of creating a new system-wide AWT resource limit with a lower percent than the default 70%.  If you do not add any classification beyond the MLOADX specification on the General tab, the resource limit will apply to all MLOADX jobs.

 

If you want to create additional AWT resource limits for MLOADX by application or by user, you can create new AWT resource limit rules and add appropriate request-level classification.  When you do that, both the system-level rule and the application/user-specific resource limit rule will be enforced separately.  However, all such rules must be satisfied before any given MLOADX utility will be allowed to run.

You can see all the AWT Resource Limit rules if you go to the new “Resource Limits by State” tab under the Throttles category.

If you have not checked the increased MLOADX job limits box in the General category, The Resource Limits by State tab will look like this.  The default is for all load utilities to be limited to using AWTs only up to 60% of the total.  That is the limit in place for releases earlier than 15.10.

Once the increased MLOADX job limits box in the General category has been checked, that same screen will look like the one below  MLOADX has been extracted from the general utility category and given the ability to use up to 70% of the total AWTs defined on the platform.  The “All load utilities” grouping has also been increased to 70%.  The screen below also shows two newly-created AWT resource limit rules that differentiate the number of AWTs available for MLOADX jobs for different groups of users (ETL users vs. App users).

You may have noticed above that DSA jobs have also been provided with their own system AWT resource limit.  That resource limit will be set by default at 70% in all 15.10 releases, without have to check a box or turn on a switch. Additional AWT resource limits may be defined for DSA jobs in a similar manner as was described for MLOADX jobs.

Conclusion

MLOADX is expected to offer both ease of use and performance advantages to users submitting updates to populated tables.  When you get to 15.10, use the workload management options described in this posting to make sure you are getting the full benefit from this new member of the Teradata load utility family. 

Some other new features accompany the new AWT resource limit rule for MLOADX.  Starting in 15.0, DBQL UtilityInfo logging will display the actual number of AWTs used by each load job phase.  Just as with other utilities, MLOADX has two phases, an acquisition phase and an application phase.  You can use DBQL logged data to help you understand how the AWT resource limit is being applied to your MLOADX jobs, and how many AWTs each phase used.  This information may help you understand how to use these new capabilities optimally.

11 Comments
Enthusiast

Hi Carrie,

(Not directly related to Workload management of MLOADX, but...)  if TPT UPDATE switches to using MLOADX and is loading a table (eitehr inserts or updates, because it is now using SQL sessions/commands and not the MLOAD protocol, doe sthis mean that free space in a cylinder is used up?

Normally, when loading a table with Multiload / TPT Update, the free space in a cylinder is kept free. The process honours the 'free space percent' option in force 9either from dbscontrol or from the table ddl).

When loading a table using SQL operations (well most of them) the 'free space percent' value is ignored and free space within a cylinder is used.

So, is the 'free space percent' value honoured when a TPT Update operator switches to MLOADX?

Cheers,

Dave

Teradata Employee

Hi Dave,

It depends (sorry).

With MLOADX, whether freespace percent will be honored depends on the operations being utilized within the utility. 

if you are doing updates to existing rows in the table, or if you are inserting into a  standard non-partitioned table where the inserts will be spread around what is already there, then free space percent will not be honored. 

However, if you use MLOADX to load into a table where the new rows will be appended to the end (adding an entirely new date partition, or loading a NoPI or columnar table for example) then free space percent will be honored.  

Different operations within MLOADX behave differently.  Some operations will use FSP, others will not.

Good question.

Thanks, -Carrie

Enthusiast

Hi Carrie,

Many thanks.

Dave

Enthusiast

Hi Carrie,

is there anway to enforce the usage of MLOADX even though the target table does not have any USI/RI/JI? We're in the process of converting few of our MLOAD jobs from legacy multiload to TPT update and it appears to be a good idea to use MLOADX for all of our mload jobs.

By loading to a NOPI table and then using SQL to merge, the limited load utility slot will be released sooner.

-Suhail

Teradata Employee

Suhail,

In order to force TPT Update jobs to use MLOADX you would have to change the DBSControl Internal field 296 to 2.   

296. MLoadProtocol - This field enables or disables MLOADX for qualified MultiLoad applications.

                       0 - MLOADX is Enabled (default)

                       1 - MLOADX is Disabled

                       2 - MLOADX is always chosen for MultiLoad

However, if you change this setting to 2, then, all TPT Update jobs will use MLOADX.   (Note that Standalone MultiLoad Client does not support MLOADX.)

There is no mechanism for you to individually control which jobs you want to use MLOADX.   But with the default setting of 0, the database controls which job will use legacy MultiLoad and which job will use MLOADX. Those jobs that legacy MultiLoad can handle will use legacy MultiLoad. Jobs with  the stated attributes on the target table (USI, RI, etc.) will use MLOADX.  So, it’s not really user controlled but more like system controlled.

Thanks, -Carrie

Enthusiast

Thank you Carrie.

Enthusiast

Hi Carrie,

As mentioned, in the 14.10/15.0 timeframe, MLOADX responds to workload management rules similarly to legacy MultiLoad, does it mean that its better to use this when we move to 15.10(we are on 14.10 right now) ? What if we want to use this in 14.10 ?

Thanks , Samir

Teradata Employee

Hi Samir,

First, only the TPT Update supports MLOADX. Standalone MultiLoad does not support MLOADX.

Second, in the default setting, the user just submits a TPT Update job and the system (DBS specifically) decides which protocol to use. If the legacy MultiLoad protocol can handle the job, that protocol will be selected. Otherwise, MLOADX will be selected.  The user cannot make the decision based on which they prefer.  This decision is made inside the database. 

Third, there is a DBSControl Internal field that can be used to force a TPT Update job to always use MLOADX (as long as both the Client and the DBS support MLOADX).  However, being this is an "internal" field, you would need to to discuss whether making this change is a good idea for your system or not with the support center and ask for their assistance.

Thanks, -Carrie

Teradata Employee
Teradata Employee

Mload Protocol is a DBSControl Internal field (#296) that can be used to enable/disable MLOADX. If that is what you are looking for here is the detail:

 

#296  MLoadProtocol - This field enables or disables MLOADX for qualified MultiLoad applications. 

                       0 - MLOADX is Enabled (default)  

                       1 - MLOADX is Disabled  

                       2 - MLOADX is always chosen for MultiLoad  

 

Thanks, -Carrie

Teradata Employee

 

Thanks Carrie

 

I did the test and efectively MLOADX works:

 

Mloadx.GIF

Thanks - Diego C.