Optimize buffer pool usage and sizes

A major task of database administrators is the tuning of complex systems.

Figure 1. Optimizing buffer pools and reviewing the success
This figure is a graphical representation of optimizing buffer pools and reviewing the success.

This task includes the optimization of buffer pools for the most effective use by DB2®. It is the process of finding the most effective distribution of Db2 objects in the available buffer pools, based on the characteristics of the objects. And it includes determining efficient buffer pool parameters for these placements. An optimized buffer pool provides the best possible hit ratios, in terms of pages found in the buffer pools, related to the given sizes of the buffer pools.

The need for optimizing the usage of buffer pools can have several reasons:
  • New Db2 applications might require new objects, and they might need to share existing buffer pool resources.
  • Table spaces are resized, table columns are added, or page sizes are changed.
  • The usage of Db2 objects changed over time and demands a new balancing of individual buffer pools.
  • Regular review and analysis of buffer pool performance indicates inefficiencies or shows unused buffer pool resources.

Database administrators often perceive buffer pool performance in context with the design and usage of tables and indexes, and also in context with the available buffer pool memory. Database systems are often so complex that it is more or less impossible to tune them manually. Expert knowledge is often required to find a good balance among the many parameters and rules.

The task of optimizing the performance will usually take several iterations of analyzing the current state, finding better object placements and buffer pool sizes, tuning the system, and reviewing the effects.