A generalized approach to performing analyses

This topic outlines a problem-oriented approach of how to analyze buffer pool performance and related problems.

The proposed approach is a combination of using the suite of Buffer Pool Analyzer tools in reasonable sequence and applying reasoning and experience to a well-defined task. As each system setup and behavior is different, the following approach is one possible example. It is not meant as a step-by-step instruction.

• On the host, use a trace data file that contains data from a representative time and create a summary activity report, a few detail activity reports (for example, a TOP(25) report, sorted by Getpage, Readreq, or Readpage request), and a bpd file. See Creating activity reports and bpd files for details.
• Inspect the summary activity report to determine how the system is set up (the number and the sizes of buffer pools, the threshold values for certain buffer pool operations, and others). See The Buffer Pool Characteristics section for details.

  Also pay special attention to the Buffer Pool Statistics Highlights section of the summary report. It highlights certain counter values with an asterisk (*). For example, nonzero values for most threshold reached values will be flagged. It is still up to you to decide whether these values are acceptable for that system setup and workload. See The Buffer Pool Statistics Highlights section for details.

• Download the bpd file to the client and load it into the graphical report utility. See Viewing performance data on the client for details. This function is excellent when used in parallel to the host reports, to get a feeling for the distribution of work and the type of access behavior of each buffer pool.

  For example, when you navigate to Buffer Pools —► Buffer Pool Comparison —► Getpages, the buffer pools with the most Getpage operations are immediately identified. All other buffer pools can usually be ignored until a simulation is performed later. The other graphical buffer pool comparisons, by ReadRequest, ReadPage, WriteRequest, and Writepage operations, are also important indicators for directing further analysis.

• Inspect the detail activity reports to determine whether a small number of objects are dominating the overall system or single buffer pool activity. Investigate these objects in detail. See The Detail Activity section for details.
  For example:

  • Read Request - Delay (msec) - Synchronous

    If some of these high-activity objects have a relatively high value, compared to the average for this counter, you may be experiencing disk problems and should consider moving such objects to a faster disk.

  • BP Hit Ratio (%) - System / Application

    A large discrepancy among system and application hit ratios may indicate a conflict, such as a wrongly set Virtual Sequential Threshold, or the object is mainly accessed sequentially but the buffer pool also contains many objects with high synchronous access.

• Use the graphical report utility on the client to further check for unusual objects:
  • For each of the high-access buffer pools navigate to 'BPx —► Object Comparison. The graphical representation makes it immediately apparent if some high-activity objects do not fit in this buffer pool, as their access type is not typical of the rest of the buffer pool objects. A reorganization of the buffer pools, using the object placement function, might be appropriate.
  • Be especially critical of high-activity objects with an unexpectedly large amount of RID-List activities. This may indicate an overdue REORG, a missing index, or an application programming style issue.
• Use the simulation function to determine if memory distribution between buffer pools is correct. See Simulating buffer pool behavior for details. Perform the simulation with Minimum / Maximum buffer simulation sizes initially set to approximately 50 percent, respectively 200 percent of the current buffer pool sizes. Initially, only check the recommended memory distribution in the list of recommended buffer pool sizes (the second table in the simulation results) for the value nearest to the current total buffer pool size. If these values differ significantly from the current memory distribution, you should question whether the activity data in the trace data file is typical for the workload you wish to optimize, especially regarding buffer pools that the simulation recommends to make smaller. You may need to collect traces from other times and also perform the same simulation on them, determining a best middle value from the combined reports.
• In a following step, the simulation results should also make it immediately clear whether an increase in overall memory would result in a significant performance increase. If a recommended size for a buffer pool is the smallest or largest size that was simulated for that buffer pool, consider performing a further simulation with a larger range of values.
• Consider simulating what happens if you move any unusual objects found earlier into other or even new buffer pools. Compare the results with the original simulation with no object movement.
• Finally, even if it was not determined earlier that object placement is required, consider using it, followed by simulation to determine whether a different distribution of objects can also result in a performance improvement. This is a time-consuming exercise, but can produce significant performance improvements.