TechBytes: Vantage - Flex Throttles in Teradata Active System Management (TASM)

Let’s go back a little bit of history and let’s actually talk about what is a throttle – It’s a really important component to talked about. So first of all, there’s an important feature in the Workload Management called a throttle. There are different levels of throttle. There are system throttles, workload throttles, group throttles, virtual partition throttles – There are different types of throttles. But for Flex Throttle, today we are just going to basically talk about the workload throttle. So basically, the workload throttle controls the number of active, or what we call concurrent requests or queries. And you can see from the screen that we currently have a limit of 3 active. What that means is when the limit is reached, new queries are put on the delay queue. So in the animation you can see I have three active. But the other two are now on the delay queue, and that’s a software delay queue. So a query on a delay queue is released when an active query finishes. So some of the advantages of the Flex Throttles features is that it helps alleviate congestion and blocking; also helps provide more controlled response times. So think about the throttle as this – Think of this as a valve for a pipe. There’s water going through the pipe, and you can open and close. The tighter that gets when you close it less work goes through; the more you open it more water goes through. It’s very similar in concept in that we work with throttles. It’s software only, and it works in the Parsing Engine (PE). Let’s talk about a high-level overview. Basically Flex Throttle allows additional requests, or what we call queries, to be automatically submitted to the system when there are unused platform resources available. When I talk about unused platform resources, I am talking about system CPU and I am talking about AMP Worker Task. Very similar to looking at the ResUsage SPMAtable and ResSAWT table. Flex Throttle allows the DBA to override the existing workload limit that I talked about in the previous screen, and releases the queries from the delay queue. Only workload throttles have the Flex Throttles capability. Let’s talk about some of the benefits. It does more fully utilize previously unused resources, and I’ll talk a little bit more on that in the next slide. It helps eliminate the need for manual concurrency adjustments. Think about this – a throttle when you define or enable it, it’s static. But with this feature it becomes more dynamic. I’ll talk a little bit more on that in the next slides. It also helps minimize the need for additional states in the state matrix. Considerations to think about – It does require full Teradata Active System Management, or TASM. Flex Throttles are not available on Teradata Integrated Workload Management. Let’s talk a little bit about what problems they actually solve. So I briefly mentioned this before but when you enable or create a throttle, that throttle remains static until you disable, or you actually change states. So let’s take a look at the CPU busy. So I’m reading the ResSPMA table, and I’m looking system CPU. And it’s not a steady state, so I have peaks and valleys. So on those valleys, that’s really a good opportunity for me to be able to release work from the delay queue. What happens is because throttles are static in the environment – Well, your delay queues can get really big and really large. So what DBAs have asked us to do is to be able to release work without the DBA manually going in and releasing work from the TASM delay queue. Let’s also look at AMP Worker Task (AWT) usage. This I pulled from the ResSAWT table. It’s very similar – has peaks and valleys. It can have a steady state where it’s stuck, say for example, at 72 AWTs being used. That would be considered busy. But there are peaks then valleys when there are only 20 utilized. So that might be an opportune time and maybe also at the same time would CPU may also be low at the same dip. So then that point with the delay queue being backed up you may want to actually release queries from the delay queue. So that’s really the problem we are trying to solve – It’s to prevent the DBA from actually manually having to go and release from the delay queue, or actually having to change states which when you change states in TASM, we can actually change throttle values. Let’s discuss a little bit how it works. So Flex Throttle currently addresses the resource under-utilization case I just talked about - the valleys, not the peaks. What does that really mean? It means that TASM will continually evaluate the system resources – the CPU and AWTs that I was talking about – and release additional requests from the delay queue. And this takes advantage of the unused system resources. This continues until the event criteria is no longer true. So think of it this way – When a Flex Throttle expression is written inside the TASM database, or what we call the TASM traffic cop, TASM will constantly evaluate the expression to see if it meets the expression criteria specified by the time criteria or what we call the qualified period of time. So TASM and Viewpoint will allow the selection. That means we are going to allow the DBA to go in and select which workloads that already have a throttle on it. We are going to be able to select those workloads by state to allow the queries to be rereleased from the delay queue. So let’s take a little example on this animation here. Here I am looking at the expression called CPU. And notice that we are evaluating the CPU, and I had a rule that said we want a release 2, and notice that now I just released two queries. One for WD-Short and one for WD-Medium. They just went through the throttle. That means I went over the limit temporally and I’ve submitted those from the delay queue onto the Teradata system. Let’s talk about how to set it up. It’s actually pretty easy. You are going to go on to Viewpoint and there will be a Flex Throttles screen. Basically what you do is to set the number of queries released with each flex action – an action meaning I want to release 1 through 10. That’s what I mean by flex action. The second step is simpler. You just define the Flex Throttle triggering events. What’s my expression? The two system events I talked about were system CPU and available AMP Worker Tasks or AWTs. You can do either one individually or you can combine those two. Third step is really to set the Flex Throttle action interval. We’ve created a new TASM interval and the default is now set for 60 seconds. But you can lower or raise that. And the fourth step is finally just to select the workloads eligible for the flex event. Again there is a rule that you have to have a throttle on the workload to actually be able to have this event participate in the Flex Throttles. Let’s take a look at this particular example here. Let’s go through the columns and notice I have 3 workloads - WD-Short, WD-Medium, and WD-Long. And notice that I have gone in and actually manually selected in Viewpoint – I said WD-Short is flex eligible; that’s on. WD-Medium’s also flex eligible is on. But WD-Long is not. So I do not want that participate in the Flex Throttle event. My CFS SLES 11 scheduler priority is assigned. I have high priority for WD-Short. I have medium priority set my WD-Medium. And I have low priority set for my WD-Long. My throttle limit is currently set for WD-Short at 8. I have a throttle limit of 5 for my WD-Medium, and I have a throttle limit of 3 for my WD-Long. You may notice that my throttle counters are currently set for 8, 5, and 3. This is currently what the counters are at. So let’s go through this animation process. OK – I am looking at system CPU. My rule says to release 2. Notice that my counters for WD-Short and WD-Medium have been released and the counters have actually increased. So if you look in the left side of the diagram you’ll see now the counters are 9 and 6. Also in the upper chart, you’ll see throttle counters of 9 and 6. What that means is that I have temporally gone over my throttle limits. However TASM recognizes that there’s plenty of system resources available - CPU in this case – so I am going to release work and I am going to keep track of that on the system TASM delay queue. So I mentioned Teradata Viewpoint Workload Designer. Let’s talk about setting that up. It’s really easy. So you will go to the Flex Throttles events screen. Before this feature can even be used or enabled you have to check the “Enable Flex Throttles” – That is a global flag. Without that, we are not releasing queries from the delay queue. Another cool feature that we’ve added is what we call the “Enable Evaluation Mode.” What we do here is we actually don’t release the queries from the delay queue. We actually evaluate whether there were CPU and the AWT resources available, depending which one you checked. And they will decide whether we would have released. And if we did, we would put in the TDWM event log. So that you can go back and study the TDWMevent log and actually analyze that and look for patterns to see whether you might during the day, in the morning, afternoon, and evening whether the Flex Throttles would be beneficial or not. So the next step is really – once you’ve enabled it – you do pick the triggering events, or what we call the expression. So the first expression that you have as an option is the available AWTs that I briefly talked about. And in this example I am using – I want to select 95 number of AMPs where this available AWTs will occur that means I am analyzing 95 AMPs on the system where that event must be true. The number of AWTs available means that out of the pool of AWTs available on those AMPs, there must be at least 5 or more. And finally the time that I qualify for this event to trigger true, or for this expression to be true, means that it must be qualified for 5 minutes, for example, if the valley of the CPU available for 5 minutes is below a certain threshold – then yes, this event will become true. Then finally notice that I can “AND” this condition with CPU, so I can look at both AWTs and system CPU. And the same thing here – I have the threshold value of 80% system CPU available, and also I’ve qualified that for 5 minutes. Notice that each individual expression can have a different qualification time. The actions that I talked about is the number of queries to release – In this particular example, I am releasing 2. And again I mentioned they have a range from 1to 10. So that means queries are released on the flex throttle action interval based on every whatever my qualification time is. All of the other events are the same as you define in the system traffic cop, or what we call the state matrix – you still to get to send an alert, run program, or post to QTable. Those events still exist. So I did briefly mention that the flex throttle action interval was a new timer. Again, it is strictly for Flex Throttles and it is on the General screens. So this is just showing you that now. By default you have a 60-second evaluation timer that we go out to evaluate the Flex Throttles feature. Let’s move on to the subject called “how to monitor,” and most you know Viewpoint we have the ability to actually monitor queries. And right now I’m looking at a static view. I’m looking the Cumulative System Data report and I set that timer to 1 minute. So what we did is we added a few new columns. We have a new column called the Cumulative Active Flex Throttles column. And we have the Cumulative Completion Flex Throttles. And all that simply means in the static report – It means over a 1-minute period for my WD in the lower left, you see I have a WD_Flex here. It just means that I have accumulative value of 1. And then how many have actually completed in that 1-minute period. Another new column that we added is whether the query when you drill down in Viewpoint, you can look of the delayed queries – the queries that are actually on the delay queue, by each request. Each request has a session ID, virtual partition, workload, name, etc. The new column we add is this Flex Throttle Eligible. And if it is, in this in this case, I have a query on the delay queue under the workload WD_Flex. And it’s now letting me know that query is actually eligible to be released. But again and it won’t be released until it meets the criteria. Finally my last slide on Flex Throttles for the day is: You have the option in Viewpoint to drill down on the states. And it actually tells you what planned environment you are in - what actually the health condition you are in. In this particular example I only have one state called the Base State. What’s been enhanced for this is all the events that triggered true in the expressions will actually be displayed on this screen so for example notice that I have the FlexThrottle-AwtEvent. It’s marked on 4/1/16 and the state is “true”. Also I have the AvailableAWTs Flex Throttle event and also marked true. The first one, the FlexThrottle-AwtEvent, is the actual expression that I wrote. The AvailableAWTs is the actual event that’s triggered to be true. Because in this example, the machine is idle and we have plenty of AWTs available for the Flex Throttle event to be set to true. Thank you very much and have a nice day.