DB2 has added a lot of workload management features in recent releases. So when we talked Tuesday afternoon, Tim Vincent and I didn’t bother going through every one. Even so, we covered some interesting subjects in the area of DB2 workload management, including:
- If your goal is to keep a certain class of queries from taking too many resources, Tim thinks a great way of doing that is to control how many of them are allowed to run concurrently.
- By way of contrast, Tim is cautious about the common approach of just lowering a query’s priority. His concern is that a long-running query could linger even longer, creating a long-lasting bottleneck in, for example, temp space.
- When running over (I believe) Linux and AIX, DB2 workload management is integrated with operating system workload management. I.e., the same “service class” or “workload class” (at a guess, the former is the official term and the latter is the term that makes sense) of queries and associated processes gets the same treatment in both DB2 and the OS.
- DB2’s workload management extends to buffer pools, to inhibit low-priority queries from evicting a higher-priority query’s data from cache.
- Sometimes, workload management doesn’t throttle a query, but just decides to collect stats for future analysis. (This is on the eminently reasonably theory that the best stats to collect are the ones that are live when performance problems are actually occurring.)
Finally, Tim spoke of what I regard as the weirdest workload management requirement, one I also heard about from Netezza (but didn’t explicitly mention) in June. Sometimes, it seems, you simply don’t want queries to finish too fast. Why? Because if you give great performance when the machine is lightly loaded, then business users might expect that performance too when the machine is heavily loaded and you can’t deliver it. Apparently, in some environments it’s better to never deliver great query performance than it is to do so only inconsistently.