Choosing buffer pool sizes

The sizes of the buffer pools that your subsystem uses can significantly affect the performance of that subsystem.

You can use the following approaches to determine the appropriate size for your buffer pools:

• In most cases, specify the largest sizes possible for buffer pools.
  Db2 handles large buffer pools efficiently. Searching in large buffer pools does not use any more processor resources than searching in smaller pools.

  In general, larger buffer pool sizes provide the following advantages:

  • Result in a higher buffer pool hit ratio, which can reduce the number of I/O operations. Fewer I/O operations can reduce I/O contention, which can provide better response time and reduce the processor resources that are needed for I/O operations.
  • Increase transaction rates with the same response time. For any particular response time, the transaction rate depends greatly on buffer pool size.
  • Prevent I/O contention for the most frequently used disks, particularly the catalog tables, and frequently referenced user tables and indexes. Large buffer pools are beneficial for sort operations. I/O contention on the disks that contain the work file table spaces is reduced.
• For buffer pools that use PGSTEAL(LRU), perform a buffer pool simulation to determine the appropriate size for the buffer pools.
• For PGSTEAL(NONE) buffer pools, specify a VPSIZE value that is large enough to contain all assigned objects that might be open at the same time.
  The buffer pool must also be large enough to contain an overflow area within the VPSIZE. The size of overflow area is generally 10 percent of the VPSIZE in the range 50 - 6400 buffers. Db2 uses the overflow area for pages that do not fit in the main part of the buffer pool. Pages that are allocated to the buffer pool area are not returned to the main part of the buffer pool until the data set is closed.
  
• If you see significant paging activity, increase the amount of real storage or decrease the size of the buffer pools.
  If insufficient real storage exists to back the buffer pool storage, the resulting paging activity might cause performance degradation.

  Important: Insufficient storage causes paging, and in extreme situations, might cause the system to enter wait state and require an IPL of the system.