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.
“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.
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.