Yeah, I know. All you need is another “thing to remember to do” following your upcoming hardware upgrade. As if your “must do” list wasn’t long enough already.
Well, consider what I’m giving you as a “soft” list…Its a few things worth checking after an upgrade, but the sky won’t fall in if you don’t get to it right way. So relax, take a deep breath, and ponder my post-expansion soft to-do list at your leisure.
The things I’m about to prod you about will make a lot more sense if you start off embracing my basic premise that a more powerful platform will require less time to accomplish the same work. Make sure you really get that: More CPU power--less time to accomplish the same work.
From there, it’s not such a big leap to these conclusions:
I'm not saying that all of these settings will need to undergo change. Maybe none of them will. But since there's a possibility they will behave differently after a hardware expansion, its a good idea to check them out.
Step Time Thresholds on System Throttles:
A step time threshold is an escape mechanism for short queries. It determines which queries will be under the control of a system throttle, and which queries are allowed to execute immediately. If a step time threshold is present (it’s an option), a query whose step estimates are less than that number of seconds will skip the throttle rule.
You may want to lower your step time thresholds based on seeing lower estimated processing times on the new hardware. This is easy to check. Just run a few explains before and after the upgrade, focusing on queries where estimates are slightly above or below the step time threshold. Then see how those explain estimates have changed, and lower the step time threshold to correlate with that change. I’ve seen DBAs simply use the factor of increased processing power for the platform and divide the threshold number of seconds by that factor. For example, if the new platform has twice the power of the old platform, cut the step time threshold number of seconds in half.
TASM Classification Criteria:
With TASM, many sites use secondary classification criteria of estimated processing time. This practice allows queries with short estimates to be mapped to a workload associated with a higher priority, and longer queries to map to a workload associated with a lower priority. With a more powerful processing platform, classification criteria may need to be lowered, so queries of the same approximate magnitude continue to classify as they did before the upgrade.
TASM Exception Criteria:
If you have exception criteria defined that includes CPU seconds consumed, you may want to think about lowering this parameter. With faster CPUs, more work can be accomplished within the same number of CPU seconds. To have the same exception experiences as before the upgrade, use DBQL to compare CPU seconds consumed before and after, then make your adjustments accordingly.
Query Milestone Thresholds:
Query milestones are for the most part a pre-TASM option that allows demotion of a running query from one allocation group to another. Whatever number of seconds you have defined as your milestone threshold will be enforced on each node separately. In most cases, you will want to consider lowering this threshold number of seconds after an upgrade, if you want to the demotion to take place at about the same point in query execution as before the upgrade.
On the other hand, if you have more AMPs per node, and fewer nodes, after an upgrade, you may want to leave the query milestone threshold where it is. Remember, a query milestone threshold is enforced at the node level. With more AMPs per node, the new node will have more work to do. Check out the following graphic.
If you alerting on any resource usage level, you might want to think about reviewing the point in which the alert is issued. Additionally, while throttle limits that control concurrency usually don’t need to change after hardware expansion, it could be that on a more powerful platform that you can tolerate a higher level of concurrency for some workloads. So you might (key word is might) want to relax (raise) some of your query limits.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.