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

All information submitted is secure.

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.

All information submitted is secure.

My developerWorks:

My notifications:

Sign out

Select a language:

Optimizing IBM DB2 pureScale transaction throughput in virtualized IBM Power Systems

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

Danny 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.

Mala 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.

Summary: 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.

Date: 12 Jun 2012
Level: Intermediate
PDF: A4 and Letter (701 KB | 11 pages)Get Adobe® Reader®

Activity: 10688 views
Comments:

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.

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.

Hostname host03/04 host05

Command lssrad –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.