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:
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 "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.
» 
Comment and Contribute
