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 24×7 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.
