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.
  • You can use the PGSTEAL(NONE) option with large buffer pools to preload frequently accessed objects into a buffer pool. All pages for the assigned objects remain resident in the buffer pool if they can fit in the allocated space.
• 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.