This chapter describes the optimization and tuning of the POWER7 processor-based server running IBM DB2. It covers the following topics:

IBM DB2 10.1 is uniquely positioned to take advantage of the POWER7 architecture.
DB2 10.1 offers many capabilities that are tailored to use POWER7 features. The DB2 self-tuning memory manager (STMM) feature is one of many features that can help DB2 workloads efficiently consolidate on POWER7 Systems. Additionally, DB2 is one of the most optimized software applications on POWER7. During the DB2 development cycles of new features, there is a specific focus on the POWER7 guidelines and the various technologies of the POWER7 architecture are characterized. So far, in the earlier chapters in this book, you read detailed descriptions about most of these POWER7 guidelines and technologies. The focus of this chapter is to showcase how IBM DB2 10.1 uses various POWER7 features and preferred practices from this guide during its own software development cycle, which is done to maximize performance on the Power Architecture. General DB2 tuning and preferred practices of DB2 10.1 are covered extensively in many other places, some of which are listed at in 8.8, “Related publications” on page 144.

Methods for taking advantage of the inherent power of the POWER7 processor include affinitization, page size, decimal arithmetics, and the usage of SMT priorities for internal
lock implementation.

New to DB2 V10.1 is a much easier way to achieve affinitization on POWER7 Systems through the DB2 registry variable DB2_RESOURCE_POLICY. In general, this variable defines which operating system resources are available for DB2 databases or assigns specific resources to a particular database object. A typical example is to define a resource policy that restricts a DB2 database to run only on a specific set of processor cores.

On POWER7 Systems running AIX V6.1 Technology Level (TL) 5 or higher, this variable can be set to AUTOMATIC. This feature is a new one introduced in DB2 10.1 and can result in enhanced query performance on some workloads. With this setting, the DB2 database system automatically detects the Power hardware topology and computes the best way to assign engine dispatchable units (EDUs) to various hardware modules. The goal is to determine the most efficient way to share memory between multiple EDUs that need access to the same regions of memory. This setting is intended for larger POWER7 Systems with 16 or more cores. It is best to run a performance analysis of the workload before and after you set this variable to AUTOMATIC to validate the performance improvement.

The following example demonstrates how to set the registry variable to AUTOMATIC using the db2set command and then starting the DB2 database manager:

In DB2 10.1, DB2_RESOURCE_POLICY uses Scheduler Resource Allocation Domain Identifier (SRADID) instead of resource set (RSET) attachments to identify resources for the database.

For more information about other usages of DB2_RESOURCE_POLICY other memory-related DB2 registry variables, see Chapter 2, “AIX configuration”, in Best Practices for DB2 on AIX 6.1 for POWER Systems, SG24-7821.

Physical objects on disks, such as tables and indexes, are stored in pages. DB2 supports
4 KB, 8 KB, 16 KB, and 32 KB page sizes. During the processing of such objects, they are brought into DB2 buffer pools in main memory. The default AIX page size is 4 KB, but other page sizes are available. To achieve increased performance on Power Systems, DB2 10.1 by default uses 64 KB, which is a medium page size.

For some workloads, particularly ones that require intensive memory access, there are performance benefits in using large page size support on AIX. However, certain drawbacks must be considered. When large pages support is enabled through DB2, all the memory that is set for large pages is pinned. It is possible that too much memory is allocated for large pages and not enough for 4 KB pages, which can result in heavy paging activities. Furthermore, enabling large pages prevents the STMM from automatically tuning overall database memory consumption. Consider using this variable only for well-defined workloads that have a relatively static database memory requirement.

The POWER7 large page size support can be enabled by setting the DB2 registry variable DB2_LARGE_PAGE_MEM.

Here are the steps to enable large page support in DB2 database system on AIX
operating systems:¹

LargePageSize is the size in bytes of the hardware-supported large pages, and LargePages specifies the number of large pages to reserve.

DB2 uses the hardware DFP unit in IBM POWER6 and POWER7 processors in its implementation of decimal-encoded formats and arithmetics. One example of a data type that uses this hardware support is the DECFLOAT data type that is introduced in DB2 9.5. This decimal-floating point data type supports business applications that require exact decimal values, with precision of 16 or 34 digits. When the DECFLOAT data type is used for a DB2 database that is on a POWER6 or POWER7 processor, the native hardware support for decimal arithmetic is used. In comparison to other platforms, where such business operations can be achieved only through software emulation, applications that run on POWER6 or POWER7 can use the hardware support to gain performance improvements.

DECFLOAT: The Data Type of the Future describes this topic in more details. The paper is available at:

DB2 uses SMT and hardware priorities in its internal lock implementation. Internal locks are short duration locks that are required to ensure consistency of various values in highly concurrent applications such as DB2. In certain cases, it is beneficial to prioritize different DB2 agent threads to maximize system resource utilization.

For more information about this topic, see 8.8, “Related publications” on page 144.

DB2 10.1 is built by using an IBM XL C/C++ Version 11 compiler using various compiler optimization flags along with optimization techniques based on the common three steps of software profiling:

On the AIX platform, whole-program analysis (IPA) and profile-based optimizations (PDF) compiler options are used to optimize DB2 using a set of customer representative workloads. This technique produces a highly optimized DB2 executable file that is targeted at making the best usage of the Power Architecture.

In addition to IPA/PDF optimizations using IBM XL C/C++ compiler, a post-link optimization step provides further performance improvement. The particular tool that is used is IBM Feedback Directed Program Restructuring (FDPR). Similar to IPA/PDF, a set of DB2 customer representative workloads is employed in this step, and IBM FDPR-Pro profiles and ultimately creates an optimized version of the DB2 product. For more information, see 6.3, “IBM Feedback Directed Program Restructuring” on page 114.

For more information about this topic, see 8.8, “Related publications” on page 144.

DB2 10.1 supports and fully draws upon the virtualization technologies that are provided by the POWER7 architecture. These technologies include PowerVM for AIX and Linux and System Workload Partitioning (WPAR) for AIX. Many of the DB2 performance preferred practices for non-virtualized environment also extend to a virtualized environment.

Furthermore, DB2 offers IBM SubCapacity Licensing, which enables customers to more effectively consolidate their infrastructure and reduce their overall total cost of ownership (TCO). DB2 also provides a flexible software licensing model that supports advanced virtualization capabilities, such as shared processor pools, Micro-Partitioning, virtual machines, and dynamic reallocation of resources. To support this type of licensing model, a tool is provided that allows customers to track and manage their own software license usage.

DB2 10.1 is engineered to take advantage of the many benefits of virtualization on POWER7 and therefore allows various types of workload to be deployed in a virtualized environment. One key DB2 feature that enables workloads to run efficiently in virtualized environments is STMM. STMM is designed to automatically adjust the values of several memory configuration parameters in DB2. When enabled, it dynamically evaluates and redistributes available memory resources among the buffer pools, lock memory, package cache, and sort memory to maximize performance. The changes are applied dynamically and can simplify the task of manual configuration of memory parameters. This feature is useful in a virtualized environment because STMM can respond to dynamic changes in partition memory allocation.

By default, most DB2 parameters are set to automatic to enable STMM. As a preferred practice, leave the instance_memory parameter and other memory parameters as automatic, especially when you are running in a virtualized environment because DB2 is designed to allow STMM to look for available memory in the system when instance_memory is set
to automatic.

DB2 also supports the PowerVM Live Partition Mobility (LPM) feature when virtual I/O is configured. LPM allows an active database to be moved from a system with limited memory to one with more memory without disrupting the operating system or applications. When coupling dynamic LPAR (DLPAR) with STMM, the newly migrated database can automatically adjust to the additional memory resource for better performance.

DB2 Virtualization, SG24-7805, describes in considerable detail the concept of DB2 virtualization, in addition to setup, configuration, and management of DB2 on IBM Power Systems with PowerVM technology. That book follows many of the preferred practices for Power virtualization and has a list of preferred practices for DB2 on PowerVM.

DB2 supports product installation on system WPARs. Since Version 9.7, the product can be installed either within a local file system on a system WPAR or in a global environment under either the /usr or /opt directory with each instance created on the local WPARs. In both cases, each DB2 instance is only visible and managed by the system WPAR that it is created in. If DB2 is installed in a global environment, different instances on different WPARs share the globally installed DB2 copy to improve i-cache efficiency and memory usage. WPAR mobility is also supported where a DB2 instance that is running on a system WPAR can migrate to a remote WPAR on a different physical machine.

There are certain restrictions and considerations to keep in mind when you install DB2 in a global environment:

For information about installing a DB2 copy on a WPAR, see Chapter 8, “Workload Partitioning”, in Best Practices for DB2 on AIX 6.1 for POWER Systems, SG24-7821.

For more information about this topic, see 8.8, “Related publications” on page 144.

This section describes methods for capitalizing on the AIX system libraries.

DB2 uses thread_wait and thread_post_many to improve the efficiency of DB2 threads running on multi-processor Power Systems. DB2 takes advantage of the thread_post_many function. The availability of such an API on AIX directly impacts the efficiency of DB2 processing, as it allows for waking many EDUs with a single function call, which in other operating systems requires many individual function calls (typically as many as the number of EDUs being woken up).

DB2 uses most of the advanced features within the AIX file systems. These features include Direct I/O (DIO), Concurrent I/O (CIO), Asynchronous I/O, and I/O Completion Ports (IOCP).

By default, DB2 uses CIO or DIO for newly created table space containers because non-buffered I/O provides more efficient underlying storage access over buffered I/O on most workloads, with most of the benefit that is realized by bypassing the file system cache. Non-buffered I/O is configured through the NO FILE SYSTEM CACHING clause of the table space definition. To maximize the benefits of non-buffered I/O, a correct buffer pool size is essential. This size can be achieved by using STMM to tune the buffer pool sizes. (The default buffer pool is always tuned by STMM, but user created buffer pools must specify the automatic keyword for the size to allow STMM to tune them.) When STMM is enabled, it automatically adjusts the buffer pool size for optimal performance.

For file systems that support CIO, such as AIX JFS2, DB2 automatically uses this I/O method because of its performance benefits over DIO.

The DB2 log file by default uses DIO, which brings similar performance benefits as avoiding file system cache for table spaces.

In general, DB2 users cannot explicitly choose synchronous or asynchronous I/O. However, to improve the overall response time of the database system, minimizing synchronous I/O is preferred and can be achieved through correct database tuning. Consider the following items:

For more information about how to monitor and tune AIO for DB2, see Best Practices for DB2 on AIX 6.1 for POWER Systems, SG24-7821.

You should configure the AIX I/O completion port for performance purposes, even though it is not mandatory, as part of the DB2 10.1 installation process. For more information, see Configuring IOCP (AIX), available at:

After IOCP is configured on AIX, then DB2, by default, capitalizes on this feature for all asynchronous I/O requests. With IOCP configured, AIO server processes from the AIX operating system manage the I/O requests by processing many requests in the most optimal way for the system.

For more information about this topic, see 8.8, “Related publications” on page 144.

Correct performance tooling is crucial for maximizing DB2 performance. Zoning in on potential performance bottlenecks is impossible without a strong performance tool set, such as the ones on Power Systems.

During the general analysis of any performance investigation, the identification of the system resource bottlenecks is key to determining the root cause of the bottleneck. System resource bottlenecks can be classified into several categories, such as CPU bound, IO bound, network bound, or excessive idling, all of which can be identified with AIX system commands.

Various system level tools are essential in drilling down to find a potential root cause for the type of the bottlenecks that are listed in 8.6, “Capitalizing on performance tooling” on page 143. Profiling tools are especially invaluable for identifying CPU bound issues and are available on AIX and Linux platforms for POWER7.

tprof is a powerful profiling tool on AIX and Linux platforms that does program counter-sampling in clock interrupts. It can work on any binary without recompilation and is a great tool for codepath analysis.

For instructions about using tprof, go to the AIX V7.1 Information Center and search for tprof command at:

Beyond the high-level tprof profiling, DB2 also uses the microprofiling option of tprof during development. Microprofiling allows DB2 to perform instruction level profiling to attributes the CPU time spent on source program lines.

OProfile is a system profiling tool, similar in nature to tprof, that is popular on the Linux platform. OProfile uses hardware counters to provide functional level profiling in both the kernel and user space. Like tprof, this tool is useful during DB2 development for
codepath analysis.

For more information about this topic, see 8.8, “Related publications” on page 144.

DB2 is positioned to capitalize on many Power features to maximize the ROI of the full IBM stack. During the entire DB2 development cycle, there is a targeted effort to take advantage of Power features and ensure that the highest level of optimization is employed on this platform. With every new Power generation, DB2 ensures that the key features are supported and brought into play at Power launch time, by working on such features well in advance of general availability. This type of targeted effort ensures that DB2 is at the forefront of optimization for Power applications.

The publications that are listed in this section are considered suitable for a more detailed discussion of the topics that are covered in this chapter: