Expedite Your Tactical Queries, Whether You Think They Need It or Not

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

Looking for an extra level of protection around your online, web, or interactive queries? Think about expediting the priority scheduler allocation group that they run in.

In the past, expediting priority scheduler allocation groups that support tactical queries was one way to prevent out-of-AMP worker task conditions from slowing down those response-sensitive queries. But even if you never run out of AWTs, there are some less obvious reasons why expediting those allocation groups is a good, proactive idea.

What Does “Expedite” Mean?

“Expedite” is a means of giving a special status to an allocation group that will help the work running within that allocation group complete faster. This special status bestows extra privileges in several different ways, which we will discuss.

Some background: In priority scheduler, all requests that enter the Teradata database are under the control of one allocation group or another. The allocation group controls the access to CPU for the requests running under its control. If a query runs in a high-priority allocation group, that query will be offered CPU more frequently than a query running in a lower-priority allocation group. The allocation group is the component that manages and defines the priority of a request.

Expedited Queries Have Access to New AMP Worker Task Reserve Pools

If an allocation group is expedited, it’s queries have access to their own exclusive pools of reserved AWTs. Steps coming from such queries are also is assigned a higher message work type than are non-expedited work. A higher work type means if there is a shortage of AWTs, the position of those messages in the message queue will be closer to the front than non-expedited ones.

These new reserved AMP worker tasks (AWT) pools can be set up as an option, from within Priority Scheduler itself, or within TASM. Only allocation groups that the database administrator has marked as “expedited” will be eligible to use this reserve. Expedited allocation groups are intended to be those allocation groups that support well-tuned tactical query applications, single or few-AMP queries, or short all-AMP queries.

If a message representing a tactical query step queues up waiting for an AWT on an AMP, the tactical query may get delayed. If that message waits in the queue very long, this wait will cause the query response time to get longer. Establishing new reserved work types and selectively expediting allocation groups is a method to increase availability of AWTs for tactical queries.

In order to accommodate one and two levels of spawned work, as is done for new work that has not been expedited, a total of three new work types are used to support the reserve functionality, each with their own pool of reserved AWTs. These work types are:

• WorkEight, a step from the dispatcher for an expedited allocation group

• WorkNine, the first level of spawned work coming from a step running as work type WorkEight

• WorkTen, the second level of spawned work

Up to 20 reserved AWTs may be specified for Linux and Windows platforms in Teradata 12. Whatever number you select for the reserve, that same number will be reserved internally for both WorkEight and WorkNine reserve pools. WorkTen, on the other hand is fixed. WorkTen will always have a reserve of two, no matter what reserve number you selected for your reserve count. 

The graphic below illustrates the 3 reserve pools at their maximum levels:

All of the AWTs to support the new reserve pools will be taken out of the unassigned pool. Using the reserve option will diminish the number of available AWTs for the non-tactical work.   The graphic below illustrates the impact on the unassigned pool of AMP worker tasks when a reserve count of 10 has been selected.     With a reserve number of 10, a total of 22 AWTs will be taken out of the unassigned pool to be used in the new reserve pools, as shown below.

Some Teradata sites have increased their total number of AMP worker tasks per AMP to compensate, when the number removed from the unassigned pool is high. Before considering an increase in the total number of AWTs per AMP, always consult with the Support Center.

Other Benefits of Expediting a Tactical Allocation Group

Some of the additional special privileges that are available for queries executing in an expedited allocation group include:

• Higher priority for FSG locks and other internal structures

• Boost for express requests (special data dictionary access requests)

• More privileges accessing key resources in future releases

While it is difficult to quantify the impact of these types of internal software enhancements, they are intended to smooth the way for tactical query executions, and they provide a further reason for expediting your tactical queries today. 

For more information, including guidance and recommendations for reserving AMP worker tasks, see the Priority Scheduler Orange Book.

Thanks for the insightful article Carrie and also for sharing the trends at the Teradata sites.
I would like to confirm one thing: We can accurately calculate the work done by these reserved AWTs from the ResUsageSawt but there is no way we can distinguish among the reserved and the other AWTs when looking into the summarized AWT metrics of ResUsageSpma, right?
Teradata Employee
The ResUsageSAWT table shows you in-use counts for AMP worker tasks by work type. This table can be useful for understanding how many tasks are active for the expedited work types (usually work8 and work9). It does not tell you whether any of those tasks are doing real work while they are holding the AWTs, or how much resource they are consuming. It only tells you how many AWTs are being held.

You are correct that the ResUsageSPMA table only gives you total in-use counts, without breaking it out by work type.

Other tools that will give you a break-out of in-use counts by work type include AWTMON and puma -c, in you have access to them.
Thanks for the confirmation Carrie.
I working on solving a problem with a particular tactical query(a stored procedure)
and based on the feedback I'm receiving from the tech I working with, I came back to your Blog hoping you could give me some clarification.

This is an excerpt from my incident:
My Update:
I have 2 AWT’s reserved for The Tactical RP with a weight of 60.
(standard = 50 , default = 75) This procedure that normally runs in less than
1 second is run by the same user all the time and this is the only user that
has access to expedited AG 10. If this is no guarantee, how can it be achieved?

Many of our customers have been unable to cope with this type of problem
using Priority Scheduler alone.
Its primary limitation is that requests already exist by the time
it sees them and those which have started work may hold critical resources and/or locks.
So we offer workload managers, with ViewPoint being the latest offering.
In a nutshell, they all do the same thing.
They keep new work from getting as far as becoming an
AWT request per rules which you devise.
Teradata Employee
It sounds like you are considering throttles to control the concurrency of the non-tactical work. Many sites do find throttles to be valuable for this purpose. Reserved AMP worker tasks is also a good option, if you are running out of AMP worker tasks. Often both approaches are used.

While reserved AMP worker tasks can ensure that an AWT is always available to the tactical work, AWTs are only one resource that a tactical query might require. The additional benefit of using throttles is that CPU and I/O contention can also be reduced. Sometimes this can help a tactical query run more consistently.
I'm using some throttles now and along with the reserved AWTs ,the tactical work is benefiting.
However, now I want to reserve 4(totaling 10 from the unreserved pool), since the tactical queries using these are very low cpu users, I'm considering increasing available AWT from 80 to 90.
What is your opinion of this?

Also, another question:
I've notice instances in my resusagesawt table where the inusemax for a time interval is low, for instance 18 but the flowctlcnt is non zero.
these are the non zero fields from this record:
Flowctlcnt 1
WorkTypeMax00 12
WorkTypeMax01 6
WorkTypeMax02 1
WorkTypeMax03 2
WorkTypeMax08 2
WorkTypeMax09 2
WorkTypeMax12 10
WorkTypeMax13 2
WorkTypeMax14 1
WorkTypeMax15 1
InuseMax 18

What explains ths situation?

thanks, Robert Glass

Teradata Employee
Hi Robert,

For you first question, it is not uncommon for sites to add additional AWTs/AMP to add back in some of the AWTs that were taken out of the general pool for use by the new tactical reserve pool. Not seeing all the conditions at your site that might feed into such a decision, I wouldn't want to try to advise you in the particulars. Going up to 90 per AMP may be acceptable for you, particularly if you you're tactical work is a very light consumer of resources, and if you think it would benefit your overall system performance to allow more non-tactical work to run.

Adding AWTs/AMP is the direct opposite of adding a throttle rule, except that a throttle rule is more focused. By increasing AWTs/AMP you are allowing more concurrency on the box, so make sure that is really what you want to do and that that increased concurrency will not have a negative impact on the tactical query performance.

As to your second question, the thing to keep in mind is that Teradata does a lot of message passing, and there are different types of message queues. The work message queue that holds messages coming from the dispatcher that need an AWT is only one type of message queue. Row redistribution, for example, involves a diffent kind of message queue, a queue on receiver AMPs that are receiving the redistributed rows from other AMPs. These queues can go into flow control just as the work message queue can, often for very short periods of time, maybe due to some periodic skewed processing on one AMP. Any message queue that has to push work back to the sender, that will be counted as an incident of flow control in the Flowctlcnt column.

I wouldn't worry too much about seeing some low counts in that column even if your AWT inuse counts are currently low.

Thanks, -Carrie