Deciding how many buffer pools to use

You can assign all objects of each page size to the corresponding default buffer pool. Alternatively, you can specify more buffer pools of each size, according to your specific situation and requirements.

Use one of the following approaches when you create or modify buffer pools:

• Segregate different activities and data into separate buffer pools to achieve better performance and to obtain relatively inexpensive performance diagnosis data from statistics and accounting traces.
  For example, the following buffer pool assignment strategy is a good starting place:

  • Isolate the catalog and directory from user data or indexes. Objects in the catalog and directory always use the following buffer pools, and the assignments cannot be changed: BP0, BP8K0, BP16K0, and BP32K.
  • Specify separate buffer pools for work files, for 4-KB and 32-KB objects. You can change the assignments by issuing ALTER TABLESPACE statements for the work file table spaces and specifying the assignments in the BUFFERPOOL option.
  • Specify default buffer pools for user data for 4-KB (TBSBPOOL), 8-KB (TBSBP8K) , (TBSB16K), and 32-KB (TBSB32K) objects as needed. Accepting the default values for these parameters means that the same buffer pools are used for user data for and catalog and directory objects.
  • Set the buffer pool names in the IDXBPOOL subsystem parameter for user indexes as needed. The separate buffer pool for indexes might improve random access through an index to data by ensuring all or most of index non-leaf pages are cached in the buffer pool.
  • Set the value of the TBSBPLOB subsystem parameter to non-default value to specify a default buffer pool other than BP0 for LOB table spaces.
  • Set the value of the TBSBPXML subsystem parameter to a non-default value to specify a 16-KB buffer pool other than BP16K0 for XML table spaces.
• For more performance optimization, you might use any of the following approaches to configure more buffer pools. More granular buffer pool assignments can provide better performance and monitoring. However, a balanced approach is best. Too much granularity in your buffer pools can fragment your real storage and increase the cost of managing the system.
  • You can create one or a few in-memory buffer pools to store the frequently accessed data and indexes. If the buffer pools can be large enough to completely cache the objects that are assigned to the pool, consider specifying the PGSTEAL(NONE) option for better performance.
  • You can customize buffer pool parameters to match the characteristics of the data. For example, you might put tables and indexes that are updated frequently into a buffer pool that has different characteristics from the buffer pools for objects that are infrequently updated. You might keep the objects that are sequentially accessed separate from the objects that are randomly accessed and specify different VPSEQT values for each set of objects.
  • If you encounter large amounts of latch contention that is related to buffer pool pages, you might reduce it by splitting to more buffer pools. Examples of this type of contention include latch class 14 on LRU and hash chains, latch class 23 on adding or removing an entry from deferred write queue, and so forth.
• Choose the default buffer pools for each page size only under the following conditions:
  • Systems that are already constrained by storage
  • When application knowledge that is necessary for more specialized tuning is unavailable.
  • For test systems