Overview of Pool Advisor

One of the critical administration tasks of the Db2 systems programming and database is to distribute storage resources among the various pools.

Many factors must be considered for each type of pool, but the product must examine and tune each pool without causing a negative impact on any other. This can be a very time-consuming and error-prone job.

BMC AMI Pool Advisor for Db2 provides you with expert assistance in performance tuning and problem resolution, as well as some automated resource management capabilities.

Pool Advisor performs the following tasks:

• Collects and analyzes Db2 data about buffer pool, EDM pool, dynamic statement caching, sort pool, RID pool, and group buffer pool use
• Makes recommendations concerning storage resource allocation, object configuration, and various Db2 parameter settings
• Automatically manages some of these resources on a real-time basis for best performance across workload fluctuations

Pool Advisor looks at storage requirements across the system and makes recommendations based on total storage resource requirements, so that an increase in one resource does not cause a shortage in another. When storage is constrained, Pool Advisor attempts to balance resource use and, under severe conditions, prevents increases to any storage resources.

Buffer pools

Db2 includes a built-in caching mechanism for data pages that are read from DASD to satisfy SQL statements from applications.

This cache is referred to as buffer pools. Database administrators typically control the size of the pools and decide which data page objects reside in which pool. Early releases of Db2 supported three buffer pools for 4-KB data pages and one buffer pool for 32-KB data pages.

Problem

As their customers moved more heavily into relational processing, IBM addressed mounting performance demands with a variety of I/O avoidance techniques, such as the various types of prefetch. It became obvious that radically different types of data that were accessed in different manners did not coexist well in the same buffer pool, so IBM increased the number of 4-KB pools to 50 and the number of 32-KB pools to 10 and added 10 8-KB and 10 16-KB pools. Beginning with Db2 v8.1, 64 bit memory architecture has allowed the buffer pool sizes to increase dramatically. Although this vast increase in caching resources has been available for years, most customers still use relatively few buffer pools.

Db2 system programmers and DBAs have found the task of buffer pool tuning complicated and difficult to understand and, with the advent of MVS sysplex configurations and Db2 data-sharing, the task has become even more complex. Shared data has to be accessed via 'group' pools so that changes can be coordinated across MVS images. How data is assigned to these buffer pools can make a big difference in the performance of the data-sharing complex.

Another important factor is the trend among large companies towards large third-party vendor application suites such as PeopleSoft and SAP. A defining characteristic of these packages is that they use large numbers of data tables (from thousands to tens of thousands). The job of configuring buffer pools for optimum performance and use of storage resources when confronted with such massive numbers of data objects is daunting.

Faced with these circumstances, every Db2 DBA asks the same questions:

• How many buffer pools should I have?
• How big should they be, and how should I set their thresholds and parameters?
• How big should they be, and how should I set their thresholds and parameters?
• How big should they be, and how should I set their thresholds and parameters?
• Which objects should be grouped together, and into which pools?
• How should the buffer pool thresholds be changed through the day?
• How should the page set buffer pool assignments change over time?

How these questions are answered can have a dramatic impact on the performance of corporate database applications.

The problem with placing all Db2 data objects into one big buffer pool (which is not uncommon) is that these different data objects have varied and distinct access characteristics. These different characteristics tend to interfere with each other, often resulting in poor performance and inefficient resource use. Different data objects (with widely varying access characteristics) in the same pool can suffer complex interactions, resulting in some types being forced out of the pool excessively and driving down overall buffer pool effectiveness. The generally accepted way to deal with this problem is to classify the data objects and combine those with the same characteristics into their own pools.

The following figure illustrates a typical buffer pool configuration:

• Few buffer pools
• Approximate object separation
• Unlike objects sharing the same pool

The following figure illustrates a better configuration, with more buffer pools and more segregation of data into similar performance classes.

By grouping objects with similar access and priority characteristics into multiple buffer pools, data pages are shared more equitably in each pool and overall hit ratios are improved. In addition, the size of each pool can now be varied individually to achieve caching targets by object group according to priority and workload demand.

While this process is straightforward in principle, two primary factors have limited its use:

• The data that is required to classify and group similar objects is not readily available. Expensive, high-volume buffer traces are required to collect the data.
• Data objects at a typical Db2 site now number in the thousands or tens of thousands.

The effort required to implement this straightforward strategy is too great in most IS shops today. Consequently, little real buffer pool tuning is being performed.

Solution

Pool Advisor can perform object classification far better than any comparable product available today.

Pool Advisor can process large lists of buffer pools and data objects. It more accurately groups data types by considering more performance characteristics than most DBAs can afford to consider:

• Type of object (table or index)
• Degree of sequential access versus random access
• Activity level
• Object size
• Update rate
• Optimum working set size for best hit rates
• Priority

The Configuration Advisor calculates a "score" that represents the overall fitness of the current configuration for the measured attributes of the page sets. You can then submit an analysis request that evaluates the mix of pools and objects and recommend changes to the assignment of objects to pools and the attributes of the pools. You have the option to accept and implement those changes or modify and resubmit the analysis request.

Using this iterative and interactive approach, you can reach a configuration that meets the needs of your objects, while fitting within acceptable limits of complexity and resource use.

Dynamic statement cache

More and more business applications are based on dynamic SQL.

Problem

PREPARE processing for complicated SQL statements can be very expensive and time consuming. The same work is repeated. Caching can help because statements that are recalled from cache use far fewer resources than statements that need to be prepared again.

If caching is enabled Db2 caches all dynamic PREPAREs, not just those that are used repeatedly. There may not be enough storage to ensure consistently good cache performance.

Solution

Pool Advisor determines which SQL statement PREPAREs are using the most CPU, which SQL statements are duplicates that must be prepared at least twice, and what portion of the statement cache is in use. Pool Advisor monitors the dynamic statement cache and tracks CPU consumption and PREPAREs to determine the optimum size of the cache and makes recommendations for changes.

RID pool and sort pool

The RID pool is an area of storage used for sorting record IDs (RIDs) during execution of SQL that uses list prefetch (including hybrid joins and multiple index access).

The size of the RID pool is specified in an installation parameter and can be between 128 KB and 10,000 MB (if set to 0, RID list processing is disabled).

The sort pool is not part of the buffer pool. Sort pool storage is allocated separately for each concurrently active thread that requests sort processing.

RID pool problem

RID pool processing can fail under any of the following circumstances:

• The number of RID entries is greater than the RDS limit (25% of the table being accessed).
• The number of RID entries is greater than the Data Manager limit of 16 million rows (an absolute limit that cannot be changed).
• There is insufficient virtual storage.
• A process attempts to use more than half of the RID pool.

RID pool failures are usually a sign of poor resource allocation, high concurrent activity, or application SQL with access path problems

Sort pool problem

The size of the sort pool is limited to a range between 240 KB and 128 MB per thread (the default is 2 MB) and is specified in an installation parameter. So the amount of storage needed depends on the number of active threads at any time. Failures can result from the lack of available virtual storage.

Solution

Although little can be done to tune the RID and sort pools, Pool Advisor monitors them constantly and can spot problems before they become critical.

Pool Advisor can warn you of these situations or (if OPERTUNE for DB2 is installed) dynamically alter the pool allocations to prevent failures.

Group buffer pools

Data sharing in a Db2 parallel sysplex environment allows application programs running on multiple Db2 subsystems to read and write common data concurrently, while maintaining data integrity and currency.

Problem

The numerous benefits of data sharing include improved availability, better load balancing, virtually unlimited capacity, and better performance—if implemented correctly. However, the resources required to assure the integrity and currency of shared data (group buffer pools and locking mechanisms) is finite and can easily be depleted during peak workload periods.

When an application running on a Db2 member of a data sharing group updates data that is shared by all of the Db2 members of that group, the data is written to the group buffer pool and is said to have inter-Db2 read/write interest. All local buffer pool copies of that data page are invalidated and future access to the data is satisfied from the group buffer pool. As the shared workload levels increase, more and more data pages with inter-Db2 read/write interest are cached in the group buffer pools, placing an increasing demand on the available hardware resources of the cross-system coupling facility (XCF).

It is a challenge to configure the group buffer pools correctly to ensure the availability of both free data and directory entries when needed to cache data pages shared by multiple concurrently executing application programs. Because workload levels can vary wildly, it becomes even more challenging for the database administrator to monitor and control the group buffer pool configuration and identify potential problem areas before they become catastrophic failures.

Solution

Pool Advisor constantly monitors and evaluates critical DB2 group buffer pool performance metrics and configuration values, alerting you to potential problems and providing advice on prevention and resolution.
Efficiency values provide an overall status for each buffer pool and can also be used to compare the relative performance between the different group buffer pools in the data sharing group, making it possible to identify over and under allocated resources.
Read/write ratios help you to determine the predominant access method for each pool, helping you to group similarly accessed page sets to specific group buffer pools and increase their operational efficiency.
Pool Advisor recognizes potential storage resource shortages before they result in critical event failures. The number of available directory and data entries are monitored to help prevent castouts and cross-system invalidation.

Data collection

Pool Advisor data is collected every minute and accumulates over a 15-minute interval. At the end of 15 minutes, the data for the first five minutes is dropped and the new interval contains data from the previous 10 minutes. Data is added each minute for the next five minutes until the interval again represents data for a 15-minute period. Then the data for the oldest five minutes is dropped again.

The first interval begins when the Data Collector is started or when a Db2 subsystem is subsequently started. The cycle continues to repeat until the Db2 subsystem is cycled or the Data Collector is stopped.

Pool Advisor intervals

EDM pool

The EDM pool holds the following objects that by running SQL statements:

• Skeleton cursor tables (SKCTs)
• Cursor tables (CTs)
• Skeleton package tables (SKPTs)
• Package tables (PTs)
• Plan authorization cache blocks (optional)

Problem

The efficiency of the EDM pool is measured by how successfully requests for these objects are satisfied without the need for loads from direct access storage device (DASD). As the percentage of EDM pool storage use increases, the risk increases that DASD loads will fail because of a lack of storage. On the other hand, over-allocating the EDM pool leads to wasteful and inefficient use of system resources. The challenge facing DBAs is to monitor the size of the EDM pool to ensure it is meeting these diverse requirements without wasting system resources that might better be used elsewhere.

Solution

Pool Advisor attempts to make the EDM pool big enough to contain all frequently used objects in addition to the largest infrequently-referenced objects without the need for I/O.

If the EDM pool is sized correctly, there should be:

• Fewer SQL statement failures
• More concurrently active threads
• Better overall system performance
• Fewer wasted resources due to over-allocation
• Fewer unnecessary delays resulting from physical I/Os to load objects from disk

Pool Advisor accomplishes this by constantly monitoring use of the EDM pool and making recommendations for size changes when increases in activity necessitate, keeping the pool operating at optimum levels. If the OPERTUNE for DB2 product is also installed, the EDM pool size can be increased dynamically.