Buffer pool efficiency

The buffer pool related report blocks of the Statistics trace report begin roughly at page 9 of the report. They are repeated for every active buffer pool.

The efficiency of your buffer pools directly affects the performance of your Db2 system. Well tuned buffer pools reduce the number of read and write operations from and to a hard disk drive, which in turn reduces application wait times.

Tip: OMEGAMON® XE for DB2 PE includes the Buffer Pool Analyzer. This is a powerful tool that reports on the efficiency of your buffer pools and simulates your DB2 workload to arrive at an optimal buffer pool configuration for the system. For more information, see the Buffer Pool Analyzer User's Guide.
Hit ratios
The Buffer Pool Read block provides hit ratios that serve as indicators of the overall buffer pool efficiency.

The buffer pool hit ratio (BPOOL HIT RATIO (%)) shows the number of Getpage requests issued by applications and satisfied by the buffer pool, expressed as a percentage of all Getpage requests. This is a relative value that depends on the type of application. For example, applications that browse large amounts of noncontiguous data could cause the buffer pool hit ratio to drop near 0.

Avoid page-Ins
The Buffer Pool Read block and the Buffer Pool Write block provides indications of potential performance problems that are caused by paging activities to a hard disk drive.

A buffer pool that is too large can also cause performance problems because it causes paging to a hard disk drive.

Look at the PAGE-INS REQUIRED FOR READ and PAGE-INS REQUIRED FOR WRITE fields in the Buffer Pool Read block and the Buffer Pool Write block of the report. These values should be zero, or close to zero. Higher values mean that the buffer pool size is over allocated. Consider reducing the buffer pool size.

Optimize prefetch
The Buffer Pool Read block provides indications of the efficiency of DB2's page prefetch activities.

DB2 uses prefetch to optimize queries. For SQL, a prefetch can read up to 32 pages from a hard disk drive. Prefetch can stop or be canceled when more than 90% of the pages in the buffer pool is unavailable. This can have a considerable effect on performance because scanned pages must be read synchronously from a hard disk drive.

Check the PREF.DISABLED-NO BUFFER and DM CRITICAL THRESHOLD fields in the Buffer Pool Read block of the report. If one or both field values are not zero or not close to zero:

  • Review the Sequential Steal thresholds (VPSEQT)

    These thresholds are percentages of the virtual buffer that might be occupied by sequentially accessed pages. If these thresholds are too low, prefetch can be disabled. If these thresholds are too high, other thresholds can be reached too soon.

  • Reduce the Deferred Write thresholds (VDWQT and DWQT)

    If the Db2 system is used mainly for high-use query systems reliant on prefetch, reducing these thresholds decreases the portion of the virtual buffer pool that is occupied by updated pages.

  • Increase the size of the buffer pool

    Use the ALTER BUFFERPOOL command to increase the size of the buffer pool, which will give the system more space for prefetched pages.