Teradata Database workload management offers a new feature starting in Teradata Database 15.10 for managing the throttle delay queue. It’s called “Prioritized Delay Queue”. This posting looks at how the Prioritized Delay Queue feature works and some things to keep in mind if you begin to use it. The intent of this enhancement is to ensure that higher priority work is able to be released from the delay queue ahead of lower priority work.
If anyone is using this feature, I’d like to hear about your experiences, so please take a moment to add a comment at the end of this posting.
Background
Throttles are workload management concurrency control rules, a widely-used option in Teradata workload management. To create a system throttle you simply give it a name, provide classification criteria to determine which queries it will control, and pick a concurrency limit.
If you select a limit of 6, the 7th query that classifies to the throttle rule will be placed in the throttle delay queue. When the rule’s counter falls below the limit, the first query in the delay queue that is associated with this throttle will be released. How long a query will remain in the delay queue depends on several factors:
- How many requests entering the system classify to the throttle rule
- How restrictive (low) the concurrency limit is
- What other throttles are contributing to a delay of the same request
In order to be released from the delay queue a request must be below the respective limit for each throttle rule it classifies to.
A system throttle is only one type of throttle rule. Refer to the the Teradata Workload Management User Guide, Chapter 6, for an explanation of the different types of throttles available in Teradata workload management.
New Delay Queue Enhancement
All delayed requests from all throttles go into the same, single delay queue. By default this throttle delay queue is ordered by request arrival time. Starting in TD 15.10 you may choose to order the delay queue by priority of the request. If you do so, then all queries will be positioned in the delay queue by the priority of the workload that the request classifies to.
When the delay queue has been ordered priority, position of a delayed request is determined by priority scheduler tier level, and within the tier the global weight of the workload. Secondary ordering is by request arrival time.
At the point at which a throttle rule makes the decision whether to run a request or delay a request, the request’s workload has already been established by the workload classification process. Using that internal knowledge about which workload owns a request enables its correct prioritization within the delay queue.
Below is an example of the delay queue when a single system throttle is active. System throttles often use high-level classification criteria such as User, Profile, or Application. Individual queries managed by system throttles often classify to different workloads, and so they incorporate different priorities. This graphic illustrates requests associated with different priorities being held in the delay queue. The ordering is first by time (the default) and then by priority.
Considerations
If you are thinking about prioritizing the delay queue, realize that it will only make a practical difference to requests being delayed by system throttles, virtual partition throttles, or group throttles. Requests that are only delayed due to throttles defined on a workload (“workload throttles”) will not be impacted by prioritizing the delay queue.
Requests delayed by workload throttles will behave the same when the delay queue is prioritized because all requests covered by a workload throttle will be assigned the same priority. Recall that when the delay queue is prioritized the first sort order is priority, the second is arrival time. Since priority will be identical for all delayed requests managed by the same workload, the second level of ordering, arrival time, becomes the de facto primary ordering.
If a low priority workload has a workload throttle and many of its queries are being delayed, these delayed requests may get pushed to the bottom of the delay queue when ordering by priority, no matter when they arrive in the queue. However, when the low priority workload throttle’s counter falls below its limit, the next eligible request associated with that workload throttle has to come from that workload and only that workload. So even though low priority requests will be sitting in the bottom of the prioritized delay queue, the one waiting the longest among them will be released. None of the requests higher in the queue are eligible to be released because they are managed by different throttles whose counter has not yet fallen below their respective limits. No matter how far down in the delay queue the next eligible request in this low priority workload is, that’s the request that will be released to run.
If only a single throttle, a workload throttle, owns all the delayed requests, then those requests will essentially behave as though the delay queue was ordered by time. In fact, if all your defined throttles are workload throttles, then choosing to order the delay queue by priority will have no impact at all on query ordering.
But let’s assume delayed requests are coming from system throttles, virtual partition throttles, or group throttles, and not from workload throttles. In such a case all delayed requests will be impacted by a prioritized delay queue.
Under those conditions, the lowest priority requests will go to the bottom of the delay queue. As long as there is delayed requests of a higher priority that are managed by the same throttle as these low priority requests, the low priority requests will remain at the bottom of the delay queue. There is no mechanism in workload management to give requests at the bottom on the delay queue a boost or some other special attention if they remain in the queue for an unreasonably long amount of time.
Take care in using this feature, as there is only a single delay queue and it can only have one sort order: By arrival time or by priority. There is no mechanism to exempt certain workloads from this feature. Keep on eye on how long low priority requests remain in the delay queue. Monitor DBQLogTbl DelayTime metrics.
If you find low priority queries are having difficulty getting out of the prioritized delay queue, think about tuning throttle limits to accommodate the servicing of all requests, or adding new planned environments that will be more generous to low priority requests sitting at the bottom of the queue. Consider converting system throttles to workload throttles where possible.
Just a small reminder, I’d love to hear back from you if you have had any experience using the prioritized delay queue option.
4 Comments