DB2 and Dynamic Logical Partitioning

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Introductory

Punit Shah (punit@us.ibm.com), Software Engineer, IBM　

10 Feb 2005

This article discusses the dynamic behavior of DB2 v8.1 and when AIX 5L memory and CPU resources change, and how changes in a few DBM and DB configuration parameters are handled using a Dynamic Reconfiguration script (DR script).

Introduction

The AIX 5L operating system version 5.2 brings an exciting new set of features to improve server manageability. The features are a part of the continuing development of the strategic vision of IBM to implement Capacity Upgrade on Demand (CUoD). A key part of this vision is the ability to divide a system into logical partitions (sometimes called as virtual servers) and resize a logical partition on the fly. You can add or remove physical memory (RAM), processors (CPU) and PCI I/O slots from the partitions. A logical extension of this feature is the ability to move these resources among the partitions without having to reboot a partition or a system. This feature is called Dynamic Logical PARtitioning (DLPAR).

To leverage these new features available in AIX 5L v5.2, the IBM database management server product DB2 Universal Database™ Enterprise Server Edition v8.1 (DB2) allows dynamic resizing of important database manager (DBM) and database (DB) configuration parameters. The memory related elements, for example, buffer pools, sort heap, database heap, can be changed dynamically, without the need to restart the DB2 server instance or reactivate any databases in that instance. Similarly, the CPU-related parameters parallel degree, number of parallel agents, are also tunable online.

An AIX 5L subsystem called Dynamic Reconfiguration manager (DR manager) executes resource change requests and notifies applications about resource changes in the partition. By default, an application does not receive any information about changes in the resources; the application has to explicitly register itself to receive such resource change notifications.

Back to top

Dynamic logical partitioning

DLPAR is now available with most eServer pSeries models (such as the p690 and p670). A partition's resources are controlled with the Hardware Management Console (HMC) with a graphical menu-based interface. An HMC is the console for an entire pSeries system or even a group of systems. The HMC administers and manages partitions and hardware resources. You can define as many partitions as required, as long as you meet the minimum criteria. This includes a minimum amount of memory, PCI I/O slots and at least one CPU per partition. In addition, you can leave some of the resources in a free pool to assign to resource hungry partitions at a later time.

The act of adding or removing resources is referred as a Dynamic Reconfiguration event (DR event). A DR event can be related to a memory, CPU, or PCI I/O slot. An AIX 5L subsystem called DR manager actually carries out the DR event request. The DR manager notifies registered applications at various stages of the DR event so that the applications can take the necessary actions to maintain their integrity and scalability with either an increase or a decrease in available resources. An application registers to receive DR event notifications either via installing a DR script (using the drmgr command) or by trapping a newly introduced signal called SIGRECONFIG. This paper discusses implementation based on a DR script.

The drmgr command is a utility that manages DR scripts on a partition. drmgr lets you install, uninstall and list installed DR scripts. The system administrator initiates a DR event from the HMC. Note that memory can only be altered in chunks of 256MB and the CPUs can only be altered in increments of one.

Back to top

DB2 Enterprise Server Edition

To complement the DLPAR feature of the AIX 5L v5.2 operating system, DB2 v8 offers online tuning capability for the majority of the DBM and DB configuration parameters. A benefit of the online tunable parameters is that a change in DBM or DB parameters does not require that client connections be terminated and a DB2 server instance restarted.

The following table lists a few of the dynamically configurable parameters. For a complete list of the online tunable DBM and DB parameters, refer to the DB2 v8 manual Administration Guide: Performance.

Dynamically configurable database manager and database parameters

To support online tuning of the configuration parameters, the commands UPDATE DB/M CFG ... and ALTER BUFFERPOOL ... have new options called DEFERRED and IMMEDIATE. These options make changes in configuration parameters take effect on the next restart of the server (DEFERRED) or immediately (IMMEDIATE). Similarly, the SHOW DETAIL option of the has been added to the GET DB/M CONFIGURATION ... command, to display current and future values. Current and future values are the same if the IMMEDIATE keyword is specified as a part of the UPDATE statement and that parameter is tunable online.

Back to top

Putting the pieces together: Dynamic reconfiguration script

When a DR event occurs, the applications are made aware of resource changes if they have registered a DR script (or explicitly trapped the SIGRECONFIG signal). The DR script can be any type of shell script, Perl script or even an AIX 5L executable. However, there are some guidelines that must be followed to qualify the script (or an executable) as a DR script. For detailed information about writing a DR script, refer to the Resources section.

The DR manager notifies the registered DR scripts three times.

1. During the check phase (before doing anything, an application can deny or grant the DR event from proceeding at this time)
2. During the pre phase (just before altering a resource)
3. During the post phase (after altering a resource)

The last two phases run if the outcome of the check phase was a grant .

An application's DR script has the ability to block a DR event from happening if, for example, it determines that doing so will jeopardize the application's integrity or performance. A DR script can check for those conditions in the check phase of the DR event and possibly grant a go-ahead or terminate the DR event at that point. This allows an application to block a DR event before any change in resources has taken place.

Once it receives the go-ahead from check phase of the DR script, the DR manager invokes the DR script again, but this time for the pre phase. This is an opportunity for an application to prepare for the resource changes that the DR event is going to bring. For example, if memory is being reduced, an application can either make sure there is enough free memory available on the partition to absorb that amount, or release some memory to avoid paging. The pre phase is also helpful for applications that cannot dynamically adjust their memory and CPU resources. In those cases, an application might shut down during pre phase and start back up again in post phase. Restarting an application in post phase allows an application to read in the most recent resources available, if the application is designed to do so.

There is also an undo phase, which is invoked in the case of an error encountered by DR manager while it is altering a resource (for example, the resource is busy or unavailable). A DR script can back out any changes made during the check or pre phase. Going back to example above, let's assume that an application reduced its memory consumption level in the pre phase. The memory consumption level can be brought back to its original level in an undo phase. The undo phase is invoked if and only if an error condition prevented successful resource alteration.

Back to top

DR Manager, DR script communication

The DR manager and DR script require a way to send information back and forth between each other. The DR manager passes information to a DR script using environment variables and command-line arguments when it invokes the DR script. On the other side, a DR script uses the exit code to indicate a success or failure. A non-zero exit code from a DR script indicates failure; an exit code of zero means success.

The DR manager calls each DR script with the name of the phase as the first argument. For example, checkacquire indicates the check phase for the resource add, preacquire indicates the pre phase for the resource add, and postacquire indicates the post phase of the resource add. Similarly, checkrelease, prerelease, and postrelease are the first arguments for the resource remove. The resource type is identified (CPU or memory) using the second argument.

The DR manager exports the following environment variables to each DR script.

Environment variables exported to dynamic reconfiguration script

Back to top

DB2 DR script

FixPak 2 (FP2) of the DB2 v8.1 product supplies a sample DR script. This script is named ibm_db2_sln, located in the <HOME_INST_OWNER>;/sqllib/samples/DLPAR directory. The script is a korn shell script. The sample DR script resizes bufferpools in response to memory add/remove operations and adjusts parallel degree to match the number of currently available CPUs in a partition. There is a compressive README file in the same directory.

A database administrator can customize the sample script as needed. The script logs messages with an AIX 5L syslog facility, with the following priorities: DEBUG, INFO, WARNING, ERR, EMERG. To get information about a DR script's progress and configuration changes made to DB2 server instances and databases, you must enable syslog.

Once installed, this script affects only DB2 v8.1 online instances and active databases. Pre-DB2 v8.1 instances, DB2 v8.1 offline instances, and databases, which are not activated, are not affected by this script's operation when the resources in a partition are altered when a DR event occurs. The sample script deals with multiple instances in a single partition and multiple databases within an instance.

DB2 DR script administration

AIX 5L provides the drmgr command to administer DR scripts. Common usages of the drmgr command are presented here; refer to drmgr man pages for more information. You must have root privileges to run this command.

To install a DR script, type:

drmgr –i ibm_db2_sln

To list installed DR scripts, type:

drmgr -l

to uninstall a DR script, type:

drmgr –u ibm_db2_sln

Memory DR event

This DR script provides protection against memory becoming very low because of a memory remove DR event. This operation prevents the DB2 instance from terminating because of a SIGDANGER signal. The AIX 5L operating system sends out this signal when paging space reaches some minimum level. Verification for this condition is made in the check phase of a memory remove operation by rejecting this memory remove operation.

During the check phase, the DR script also confirms that there is enough pinnable memory to satisfy the current requirement. The DR script allows a memory remove operation even if there is not enough free physical memory available to absorb outgoing memory, provided there is enough paging space available in lieu of physical memory. Generally, no specific conditions are checked for a memory add DR event, as more memory is always welcome.

When memory is removed, DB2 v8.1 reduces the active database's bufferpools in proportion to their respective percentage consumption level before the DR event occurred. For example, consider two bufferpools, one 1GB in size and another 10GB. If 2GB of memory is removed, then the size of these bufferpools is reduced by 186MB (9.1% of total 11GB) and 1.86GB (90.9% of total 11GB), respectively. Similarly, a DR event that adds memory allocates that incoming memory among the active databases' bufferpools in proportion to their respective percentage consumption level before the DR event. The actual changes in the size of the bufferpools, using the db2 command line interface (CLI), are made in the post phase of a DR script.

CPU DR event

The set of actions taken when a CPU count is being altered are less complex than the actions taken for memory alteration. This is because DB2 does not utilize processor affinity (binding a process to particular physical CPU). In the case of a CPU being removed, the DR script attempts to find bound processes to an outgoing CPU.If bound processes are found, those processes are unbound from that CPU and bound back to the remaining CPUs in round-robin fashion. A round-robin scheme ensures that the load among the remaining CPUs is distributed evenly. The script may reject a CPU remove operation if it fails to unbind a process from an outgoing CPU.

After performing the above housekeeping operations, the sample DR script adjusts MAX_QUERYDEGREE (a DBM configuration parameter) for all online instances and DFT_DEGREE (a DB configuration parameter) for all active databases, in the post phase.

Back to top

Conclusion

The DLPAR feature of AIX 5L v5.2 is an outstanding feature to grow and shrink partition resources as workload demand dictates. However, applications must scale their resource requirements according to resource availability in a partition for maximum performance benefits. Applications can adjust their resources transparently using either the DR script or by trapping a signal called SIGRECONFIG.

To take advantage of this feature, DB2 v8.1 introduces online tuning capability for many important DBM and DB configuration parameters, as well as bufferpool sizes. The DB2 v8.1 FixPak 2 distribution provides a sample DR script. The script adjusts DB2 online instances and its active databases' CPU and memory-related parameter in response to a DR event that altered an amount of available memory or number of CPUs. A DR script must be installed to enable this functionality.

Resources

• DB2 Universal Database Version 8.1
  
  
• IBM DLPAR Tool Set for pSeries
  

About the author

		Punit is a Advisory Software Engineer in eServer Solution Enablement/Database Engineering group. His primary responsibilities are enabling database servers to use the latest AIX technologies. He has been working in the Database server development, performance area for last 8 years. Punit has designed and developed sample DR script for DB2 UDB v8 for AIX 5L v5.2, that is shipped in FP2 of DB2 UDB v8.1. Punit holds a Bachelors and Masters degree in Computer Science. You can contact him at punit@us.ibm.com.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Back to top