Prioritized Delay Queue

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

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:

  1. How many requests entering the system classify to the throttle rule
  2. How restrictive (low) the concurrency limit is
  3. 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.

 

Throttle_Rule.jpg

 

 

 

 

 

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.

 

http://www.info.teradata.com

 

 

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.

 

Prio_Delay_Queue.jpg

 

 

 

 

 

 

 

 

 

 

 

 

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.

3 Comments
Apprentice

Hi Carrie,

 

As usual a very useful post - thank you.

 

Can I please just confirm my understanding of one point that you raise. You said:

"Requests that are only delayed due to throttles defined on a workload (“workload throttles”) will not be impacted by prioritizing the delay queue."

and:

" 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."

 

I understand that all queries under the control of the same "workload definition" run at the same priority and therefore if the only throttle in place was a single "workload throttle" then this feature has no effect.

 

However, if there are multiple "workload throttles" in place and the associated "workload definitions" have differing priorities then (I think) this feature is still effective.

 

Is my understanding correct?

 

Cheers,

Dave

Teradata Employee

Hi Dave,

 

Before reaching that conclusion, I had to to go through the exercise using a paper and pencil (and doing a lot of erasing and re-drawing) to simulate the situation of having multiple workload throttles (each with delayed queries).  I tried the simulation with time-based delay queue ordering and then prioritized ordering. The key reason why there would be no impact of prioritizing the delay queue if you are only using workload throttles has to do with isolation of one workload throttle from another workload throttle. Each workload throttle controls its subset of delayed queries, even though they are all in a single delay queue.  

 

All the queries that classify to a particular workload (and could be potentially delayed by the workload’s throttle) will be exhibiting the same priority in the delay queue. Let’s assume there are 10 queries in the delay queue for a workload throttle on a low priority workload, WD-Low. All those 10 queries will have the same priority, so their ordering in the delay queue relative to each other will be on arrival time. We only care about the position of WD-Low queries in the delay queue relative to each other, because that is how it is decided which one to release next. The pool of candiates to release by that workload throtlte only include queries that classified to WD-Low.

 

When the counter for the WD-Low workload throttle falls below the throttle limit, the query that has waited the longest WITHIN THAT WORKLOAD will be eligible to be released, the same as if the delay queue had been ordered by time. Since the delay queue is sorted by priority, there could be several queries placed closer to the front of the delay queue than the WD-Low queries, queries that classified to a higher priority workload. But when it comes to determining which query to release when the WD-Low throttle counter is below its limit, only queries that classify to WD-Low can be considered, none of those queries higher in the delay queue would be eligible for release because they didn’t classify to WD-Low and that’s the workload throttle with the open slot.

 

Those higher priority queries will have to wait until THEIR workload throttle has an open slot. The higher prority queries may be placed higher in the delay queue, but it won't impact when they are released.  They, like queries delayed by WD-Low throttle, can only be released when THEIR workload throttle counter falls below its limit.  Being positioned higher in the delay queue will not help higher priority requests get released sooner if they have been delayed by a workload throttle.  

 

Only throttles that manage queries of differenting priorities will exhibit a difference when you prioritize the delay queue. That is because, contrary to workload throttles, the individual delayed requests of such throttles may carry different priorities and therefore those queries able to be sorted first by priority, then by arrival time. Delayed requests coming from workload throttles cannot be sorted first by priority because they have no priority differentiation among them.

 

Regards, -Carrie

Apprentice

Hi Carrie,

Thanks for that. The key bit that I missed/forgotten/hadn't realised is:

"...isolation of one workload throttle from another workload throttle. Each workload throttle controls its subset of delayed queries, even though they are all in a single delay queue."

Now it all makes sense.

Many thanks,

Dave