Optimizing IBM DB2 pureScale transaction throughput in virtualized IBM Power Systems

Today, hardware virtualization is prevalent throughout the IT industry. Having a single server perform the same amount of work previously done by several, is a great a value proposition for companies of any size. Although there are volumes of documentation available — both online and printed — the vastness of the resources make it difficult to navigate them. This article begins to address that challenge by providing a list of high-level, planning-and-deployment best practices, followed by a series of lower-level optimization steps using a sample database environment running an IBM DB2® pureScale® installation for IBM POWER7® servers.

Danny Leung (danleung@ca.ibm.com), Systems Optimization & Performance Analyst, IBM Canada Ltd.

Danny LeungDanny has been with IBM for five years and is a member of the Systems Optimization Competency Center (SOCC) team based in Toronto. His main area of focus is on the POWER7 architecture running OLTP DB2 pureScale database workloads.



Mala Anand (manand@us.ibm.com), PowerVM Performance Lead, IBM Corp.

Photo ofMala AnandMala is a Senior Technical Staff Member at IBM Systems and Technology Group System Performance. She leads the Power Virtualization performance testing, focusing on processor and memory virtualization and IBM PowerVM competitive performance. She previously led network and enterprise workload performance efforts on x86 Linux at the IBM Linux Technology Center.



12 June 2012

Also available in Chinese

Introduction

Today, a database server is likely to be implemented on a virtual machine (VM), where processor, memory, disk and network components are allocated from a shared pool of available resources on a physical machine. This provides the ability to consolidate multiple workloads onto a single server. Despite potentially higher upfront costs for larger physical machines, the ability to consolidate many workloads onto a single server can mean more efficient use of system resources, which can help lead to less power consumed, less data center floor space occupied and ultimately, a lower total cost of ownership (TOC).

The nature of this paradigm, however, introduces a coordination mechanism within the physical machine that assigns, maps and manages the physical resources to the virtual hosts, or logical partitions (LPARs) as they are known on IBM Power Systems™ severs. The implementation of this mechanism — usually known as the hypervisor — can provide a robust, scalable and manageable virtualization solution that can help maximize clients' return on investment (ROI).

That hypervisor, part of the IBM PowerVM™ virtualization solution, is able to take LPAR profiles and deploy them onto IBM Power Systems in an efficient and seamless manner. It can virtualize multiple operating systems (OS), has the scalability to deploy up to a thousand VMs on a single server and integrates with a full suite of tooling to plan, deploy and manage the LPARs quickly and easily.

For example, with the IBM PowerVM solution, not only can you set the quantity of resources LPARs receive, but you can also control — from the same interface — how those resources, such as processors and memory, are shared among the other system LPARs. If you have unimportant LPARs that are frequently idling, they would be best implemented with a shared-processing LPAR profile. This means, unused processing resources are deposited back into a shared processing pool for other LPARs to consume. If you have LPARs that are mission-critical, despite having some idle time, you might want to set up a dedicated-processing profile, where processing resources are strictly coupled with that LPAR and no other.

There are volumes of literature available (see the References section below) on virtualization, from short articles to comprehensive books; targeting a variety of scenarios, from specific database and application middleware workloads to generalized host deployment strategies. This article will provide a short list of LPAR planning and deployment best practices, followed by a series of optimization steps using a sample database environment running an IBM DB2® pureScale® installation on IBM POWER7® servers, to demonstrate the advantages of such best practices.


Best practices

As physical servers provision additional resources for additional VMs, the need to optimize hardware usage becomes greater in order to maximize server performance. The following set of best-practice suggestions should help you with ensuring that the optimal configuration is achieved and maintained.

1. Understand and document the intentions, purposes and goals of the server

Documenting the server's goals not only helps system architects and administrators focus their planning on a finite list, but provides a document that can be referenced for new teams or members using this server in the future. Even if the usage patterns are not constant, maintaining a living document provides value by maintaining the team's agreement of how the server is being used. Using the IBM System Planning Tool (SPT) should make this step fairly trivial. Further SPT details can be found in the Miscellaneous section under References at the end of this document.

2. Ensure that the system firmware is up to date

To get the best-performing hypervisor, have the system installed with the latest release of the firmware for the machine being used. If a Hardware Management Console (HMC) is set up, you can use its maintenance functionality to perform the updates for you.

Figure 1. After you have selected the server, expand the “Updates” task to see the “Upgrade Licensed Internal Code to a new release” option.
After you have selected the server, expand the “Updates” task to see the “Upgrade Licensed Internal Code to a new release” option.

Further details for firmware updates can be found in the Firmware Update Procedure section under References at the end of this document.

If the HMC is not being used to update the server, an alternate method would be to manually download the patches through the IBM support site, which also provides patches and updates for other components like the HMC or SPT.

3. Determine the proper hardware resource topologies (processor, memory, disk etc.) for the LPARs

After understanding what the server will be used for, the planned hosts need to be resourced appropriately to carry out that purpose in the most effective way. This does not just refer to the quantity of the resources (for example, 32 compared to 16 processors, 256 GB compared to 128 GB RAM), but also to the manner in which they are to be virtualized: shared processing, dedicated processing, dedicated-donate processing, active memory sharing, active memory deduplication, Virtual I/O Server (VIOS) deployments etc.

Further documentation for virtualization best practices can be found online. Refer to the Virtualization section under References.

4. Power on the most important LPARs first

The processor-memory and processor-processor affinities are important factors for system performance. The fewer the Multi-Chip Modules (MCMs) and Central Electronic Complex (CEC) enclosures the LPAR resources are spread across, the better it will perform. The hypervisor computes the best, most-aligned, resource assignments to a starting LPAR based on the current resource pool that exists. It also remembers the resource allocations it computes for an LPAR from its first startup. Therefore, it is vital that the processor and memory resource pool is made as large as possible to produce the best affinities. After, it is equally vital that the most processor- and memory-critical LPARs are activated first, to ensure they get the best chance for being assigned the most optimal resource alignments.

In the strictest sense, this is only needed when the hypervisor has no allocation for the server LPARs. That is, the startup order of a server is important only if there are LPARs that have never been activated or started, or LPARs whose resource allocations were cleared (for example, using chhwres). For example, if a system administrator is shutting down the running LPARs, power cycling the server, and starting those same LPARs, the startup order does not matter: the hypervisor has the information that it needs to optimize the LPAR resource allocations during server startup time. However, it might be easier to start the LPARs in order of importance anyway, so that one does not need to track chhwres or LPAR creation events.

The minor tradeoff to this practice is a somewhat unintuitive startup order, where the VIOS instances are powered on last. This implies that while the most important LPARs are started first, they will not actually be operational — spinning with the platform firmware code, AA060011 — until the VIOS is up and running.


The case scenario

In the following hypothetical situation, we have a database administrator who wants to add a new DB2 pureScale member, host5, to a DB2 pureScale instance running on a single IBM Power System™ 770 test system, barcelona00-SN103EA7P, such that the instance configuration looks like this:

Listing 1. The DB administrator wants to add host05 to the instance to have its db2nodes.cfg file look like this
                    0   host3 0 192.168.0.4 - MEMBER
                    1   host4 0 192.168.0.5 - MEMBER
                    2   host5 0 192.168.0.6 - MEMBER
                    128 host1 0 192.168.0.2 - CF
                    129 host2 0 192.168.0.3 - CF

After the successful DB2 member addition, the database administrator finds that the transaction throughput performance looks like the following example in Figure 2:

Figure 2. Observe how the DB2 member on LPAR host05 is relatively low compared to DB2 members on host03/04, despite identical LPAR profiles.
Observe how the DB2 member on LPAR host05 is relatively low compared to DB2 members on host03/04, despite identical LPAR profiles.

Because DB2 members are generally identical, (for example, same database, OS and LPAR configurations), optimization techniques within the OS and DB layers of host5 have little effect. Rather, the area with the greatest optimization potential would be the virtualization layer, where physical resources are mapped to virtual devices. For an LPAR's processor and memory, the AIX command, lssrad –av, lists the Scheduler Resource Allocation Domains (SRADs). Understanding the SRAD layout helps us visualize the physical-virtual mapping for the host in question. A difference in the lssrad output, as shown in Table 1, would suggest a possible source of the transaction throughput discrepancy.

Table 1. Notice the increased spread of the resources across several SRADs for host05, with resources distributed over four MCMs from two processor “books” compared with host03/04, where resources are taken from two MCMs on a single book.
Hostnamehost03/04host05
Commandlssrad –a –v
Output
REF1     SRAD       MEM     CPU
0
0       31806.31    0-31
1       31553.75    32-63
REF1     SRAD          MEM      CPU
0
0        27822.31      0-27
1        3984.00       28-31
1
2        3984.00       32-35
3        27569.75      36-63

From this sample output, the administrator can deduce that the performance characteristics on LPAR host05 can be improved if its distribution of processor and memory resources is more compact, similar to host03/04. The most effective way to implement the redistribution is to clear the hardware resource assignments.


The optimization steps

There are actually several sets of steps that can be employed to realize performance gains on POWER7 servers. Each set is associated with its own series of pros and cons, allowing a client to select the one most appropriate for their situation. There is a solution that is quick and simple but provides limited performance increases; another solution that is more involved but allows for greater performance benefit; and a third solution with the heaviest of requirements but providing the greatest performance gains.

The simplest, and least-intrusive solution, is merely an update to the LPAR profile, (for example, increase maximum memory by 1 GB) while the LPAR is powered off. Upon a subsequent startup, you run an "in-place" re-allocation of resources.

The next option is to power down a finite number of non-critical LPARs, running chhwres on the deactivated LPARs (see step 3 below for chhwres details), and starting them up in the proper order (of importance; see step 4 below for startup order details). The resource domain with this option is the size of multiple LPARs, compared to the single LPAR domain size allowed with the previous solution, which is why this option contains greater optimization potential.

Similarly, the last option requires the most inconvenience, while creating the largest resource domain for the hypervisor to work with, thereby providing the best chance for the best allocation. The steps for this last solution set are as follows:

1.Power down all LPARs on the server, including the VIOS instances

The rest of these steps have the greatest efficacy if all hardware resources participate in the re-assignment. With a larger resource pool to work with, the optimization during LPAR startup (Step 4) tends to be greater. Thus, even though the problem was observed on the DB2 member, we can enlarge our resource pool by shutting down the other LPARs running on the system, such as the DB2 pureScale Cluster Cache Facility (CF) or the VIOS, or both.

2.Power cycle the server

Now that the hosts are safely shut down, power down the server, and then start it back up. This clears the hypervisor of any previous LPAR resource assignments, lending itself to a more optimized allocation of LPAR resources upon subsequent host startups (Step 4).

3.(Alternative to Step 2) Log in to the HMC and run chhwres for all LPARs on the server

There are situations where power cycling a server might not be feasible, despite all the LPARs being shut down. For example, perhaps the downtime of the LPARs is very short, such that the extra time needed for a power cycle is not available. As an alternative, you can clear the resource assignment information within the server hypervisor and the HMC by running the chhwres command from the HMC command line.

Listing 2. Listing 2. -r is to specify the resource type (‘mem’ is for memory), -m is to specify the managed system, -o is the operation (‘r’ is to remove the resource), -q is the quantity of the resource we want to operate on (in megabytes), and --id is the LPAR host ID that we are targeting.
                    chhwres –r mem -m "barcelona00-SN103EA7P" -o r -q 2048 --id 1
                    chhwres -r mem -m "barcelona00-SN103EA7P" -o r -q 2048 --id 2
                    chhwres -r mem -m "barcelona00-SN103EA7P" -o r -q 65536 --id 3
                    chhwres -r mem -m "barcelona00-SN103EA7P" -o r -q 65536 --id 4
                    chhwres -r mem -m "barcelona00-SN103EA7P" -o r -q 65536 --id 5
                    chhwres -r mem -m "barcelona00-SN103EA7P" -o r -q 65536 --id 6
                    chhwres -r mem -m "barcelona00-SN103EA7P" -o r -q 65536 --id 7
                    chhwres -r mem -m "barcelona00-SN103EA7P" -o r -q 65536 --id 8
                    chhwres -r mem -m "barcelona00-SN103EA7P" -o r -q 65536 --id 9

Figure 3 outlines the source of where some of the option values can be found.

Figure 3. Values needed for the chhwres command are found in several places of the HMC (v7) server view. Server name can be found near the top (see orange box on the top), LPAR IDs are under the "ID" column (green box on the left) and the amount of memory that needs to be cleared is found under the Memory column (red box on the right).
Values needed for the chhwres command are found in several places of the HMC (v7) server view. Server name can be found near the top (see orange box on the top), LPAR IDs are under the

4.Activate LPARs in descending order of importance, that is, most-important LPARs first

Now that the hardware resources are completely free, it is time to re-optimize the resource associations. The hypervisor does a great job at calculating the optimal allocations, especially when there is an adequate resource pool to work with.

After these four steps, we see balanced performance across all three DB2 members. If the lssrad –av command was invoked again, all three LPARs would have similar, or even identical, resource allocations.

Figure 4. After re-aligning the resources, the imbalance is addressed and our newly added DB2 member is now performing to original expectations
After re-aligning the resources, the imbalance is addressed and our newly added DB2 member is now performing to original expectations

References and links

Virtualization

Operating system and database layer optimizations

Optimization step procedures

Firmware update procedure

Miscellaneous

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into AIX and Unix on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=AIX and UNIX
ArticleID=820695
ArticleTitle=Optimizing IBM DB2 pureScale transaction throughput in virtualized IBM Power Systems
publish-date=06122012