Tackling Slowdowns: Utility Throttling in IBM DB2 UDB V8.1.2 and Beyond

Tuesday Apr 13th 2004 by DatabaseJournal.com Staff

Learn how utility throttling empowers DBAs to automatically assign more computing resources during off-peak hours to some utilities and scale resources back during periods of high-resource demand.

by Paul C. Zikopoulos and Roman B. Melnyk

The maintenance of any database involves the routine invoking of utilities that perform backup operations, data rebalancing, updating the database statistics, reorganizing a table, and so on. In IBM DB2 Universal Database for Linux, UNIX and Windows (DB2 UDB), utilities are parallelized and extremely fast.

Usually, speed makes people smile. However, you can only get a utility to run so fast by using a good algorithm that makes its operations efficient. Ultimately, speed costs one or more resources that constitute the performance "triangle." Balancing utility consumption with business operations was traditionally handled by scheduling the maintenance operations to run during off-peak hours in "batch" windows.

Database administrators (DBAs) could not offload the database maintenance as batch processes because these utilities were not available online. In fact, DB2 UDB V8.1 enhanced a whole set of utilities so they can be performed online, thereby removing any need to take a database down for maintenance. For example: online loads, index and table reorganizations, materialized query table rebuilding, and so on, are all online tasks in DB2 UDB V8.1. However, we found DBAs still batching their maintenance tasks because they could not afford to break their service-level agreements (SLAs) over consuming too many of these fast online utilities. (Talk about coming full circle!)

The batch approach works well for many users, depending on the industry, and is still appropriate for some applications. However, in today's 24x7 business climate, the batch process approach is becoming a less and less acceptable management methodology.

What was really needed for the online utilities was a granular resource control mechanism by which utilities could run more aggressively at certain times and perhaps stop and pause at others. Ladies and gentlemen, allow us to introduce DB2 UDB throttling.

DB2 UDB Throttling in the V8.1.2 Update

DB2 UDB V8.1.2 adds utility throttling, which empowers DBAs to regulate the performance impact of some utilities. (You get to this level by applying the V8.1.2 update.) This allows utilities to be automatically assigned more computing resource during off-peak hours, and scaled back during periods of high-resource demand.

Because workloads can be unpredictable, their resource demand characteristics can vary during the lifetime of a utility invocation. With this in mind, utilities need to be able to dynamically adapt their resource consumption. Ultimately, DBAs will run utilities more aggressively when the workload is light and conservatively when workload demands increase.

The benefits of utility throttling to a DB2 UDB DBA include the ability to:

  • Execute maintenance tasks with total control at all times over the performance impact to the production workload. This eliminates the need to identify batch windows or schedule down time for utility execution, and eliminates the need to lock in agreed-upon SLAs.
  • Ensure that valuable system resources are fully utilized by these utilities in periods of reduced demand.
  • Eliminate the performance impact as a consideration when monitoring a utility and adjusting its parameters (for example, the number of BACKUP buffer manipulators).

Once a DBA has established a throttling policy, it is the system's responsibility to ensure that the policy is obeyed.

In DB2 UDB V8.1.2, the BACKUP and REBALANCE utilities can be throttled. The maximum impact percentage for all throttling-enabled utilities running within an instance can be controlled through the new UTIL_IMPACT_LIM database manager configuration (dbm cfg) parameter.

This parameter is dynamic and can therefore be changed without stopping and restarting the instance; it can even be set while the utilities are running. This gives many powerful options to a DBA. For example, a DBA might choose to implement a "health" policy such that when a certain threshold is breached, utility throttling automatically kicks in and scales back the utility operations, allowing business workflow to continue uninterrupted.

For example, let's assume a new container was added to a DMS table space. This action would trigger an online rebalance of the data to evenly stripe it across all containers to achieve more parallelized prefetching. (You can actually avoid rebalancing if you want in DB2 UDB V8.1.) This operation is online and transparent to applications. However, let's assume that shortly after adding the new container, the company's supply chain management (SCM) database experiences a heavy load when a foreign country's central bank cuts interest rates, spawning a flow of import dollars to your company (which is a good thing). This heavy load causes a spike in I/O as the logger is busy keeping up with the new entries and hardening them to disk. Although the rebalance operation is online, the movement of data between the various database containers is not optimal for throughput. The DBA could have implemented health monitors to make a policy-based decision and scale back the utility automatically through a script, an API, or manually.

The UTIL_IMPACT_LIM parameter is set as a percentage of the allowable impact to the current workload on the system. The default setting for this parameter is 100, which essentially means that no throttling will take place, and that the utilities that can be throttled will run full speed (in other words, you have to have this parameter set to <100 for throttling to be enabled).

The following chart shows the results of an OLTP-type application that we ran in our labs concurrently with the BACKUP utility. We ran multiple test cases, each time varying the amount of throttling via the UTIL_IMPACT_LIM parameter. As you can see, throttling this utility such that it impacted the workload by only 10% resulted in only a minor degradation in workload while allowing the BACKUP utility to run. (This was done by setting the UTIL_IMPACT_LIM parameter to 10, which is shown as a non-adaptive throttle rate of 90 in the chart.) Of course, your setting will depend on your SLA and, of course, performance will vary, depending on how you configured backup, your hardware, and so on; the point of this chart is to illustrate the impact of utilities and potential gains that could result from throttling.

We recommend that you start with a value that does not impact the workload by more than 10%; however, your environment, business conditions and workload should ultimately dictate this setting. A throttled utility should, in most cases, take longer to complete than an unthrottled utility. If you find that a utility is running for an excessively long time, adjust the value of the UTIL_IMPACT_LIM parameter or disable throttling altogether by setting this parameter to 100.

How Does Throttling Work?

To control throttling, there is an algorithm that is busy ensuring that DB2 UDB is aware of the utility's load on the system and adjusts its consumption accordingly. A simplified representation of this algorithm is shown below:

Click for larger image

The DBA ellipse represents the setting of the UTIL_IMPACT_LIM parameter that is determined by the DBA and the business policy. The DB2 ellipse encapsulates the utilities that are running. During utility operations, a sensor interacts with the Adaptive Controller box (which is actually inside DB2 UDB), and it runs algorithms that determine a metric for computing degradation. It compares that returned value with a baseline estimation for the workload and the "impact allowances," as defined by the DBA. If there is a problem, a separate process will cause the utility to "sleep" until it is in compliance with the set policy.

The graph on the right plots a sample of the output for the aforementioned OLTP workload with the BACKUP utility running. In this test, the UTIL_IMPACT_LIM parameter was set to 50 (represented by the red line). The actual performance numbers are plotted in blue. You can see that DB2 UDB was able to keep the workload constant by throttling the utility after it exceeded the target threshold and the throttling algorithm took effect.

More representative of the real world would be a scenario involving multiple concurrent workloads on the system.

The following test had a dual workload with the BACKUP utility running concurrently. On the left side of the diagram, you can see the workload distribution between the two applications, with the total system resource shown in blue. On the right side of the diagram, the BACKUP utility has been invoked, again with the UTIL_IMPACT_LIM = 50. If you look at the right side of the diagram, you can see that the "meat" of the workloads' impact on the system is now at 900 seconds, compared to 600 seconds on the left - this implies that the workloads were impacted by roughly 50%. Note that the distribution of the workloads in both diagrams has a similar shape.

Using DB2 UDB Throttling

In DB2 UDB V8.1.2, throttling is mainly confined to the BACKUP utility; throttling the REBALANCE utility requires special handling.

Utilities can be run in either throttled or unthrottled mode. In unthrottled mode, the utility would not be under the throttling system and does not obey the UTIL_IMPACT_LIM parameter. In throttled mode, you can actually assign different priorities.

In most cases, DBAs will just run a utility in throttled mode without any special priority. Although the ability to specify a priority is not very important when there are not a lot of utilities that can be throttled, this method created an infrastructure that is scalable and extensible to support other utilities in future releases (see the DB2 UDB Throttling in "Stinger" section later in this article).

For example, the following syntax was added to the BACKUP DATABASE command:

On the BACKUP DATABASE command, priority is an optional flag that represents a number in the range of 1 to 100, with 100 representing the highest priority, and 1 representing the lowest priority. The priority specifies the amount of throttling to which the utility is subjected. All utilities at the same priority undergo the same amount of throttling, and utilities at lower priorities are throttled more than those at higher priorities. If you do not specify this option, the default priority is 50. If you invoke the BACKUP utility without specifying the UTIL_IMPACT_PRIORITY keyword, the utility will execute in unthrottled mode (of course, if the UTIL_IMPACT_PRIORITY keyword is specified, but UTIL_IMPACT_LIM is set to 100, the utility will run unthrottled).

The REBALANCE utility is invoked indirectly from an ALTER TABLESPACE operation. By default, the REBALANCE utility is started in unthrottled mode, and a mechanism is provided whereby a DBA can throttle this utility after it has been started.

The ability to use the command line processor (CLP) to enable or disable throttling, as well as reprioritize a running utility, is also provided through the set command, as follows:

set UTIL_IMPACT_PRIORITY for <utility_id> to <priority>

For the set command, the priority flag performs the same function as detailed above; however, if you set it to 0, it will force a throttled utility to continue unthrottled.

In the DB2 UDB V8.1.2 implementation, running multiple throttled utilities at the same time is not supported.

DB2 UDB Throttling in "Stinger"

DB2 UDB "Stinger" has a number of enhancements to the throttling capabilities of DB2 UDB (check out www.ibm.com/db2/stinger for more information):

  • Some of the utilities and policy settings have been encapsulated in the Control Center and Wizards.
  • There is an active utilities monitor to track the status of utilities.
  • Snapshots and SQL support for monitoring the progress of executing utilities have been added.
  • Multiple concurrently running utilities can be throttled.
  • The UTIL_IMPACT_PRIORITY clause has been added to the RUNSTATS utility.

About the Authors

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.

IBM reserves the right to include or exclude any features advertised as part of the DB2 UDB "Stinger" release in the final version of this product.

Mobile Site | Full Site