Performance-related tips

About this task

The following tips might be useful when you perform a simulation:
  • The result from a simulation shows a comparison of buffer pool efficiency for separate buffer pools versus a single combined buffer pool (see step 2). Generally, a single buffer pool improves the total buffer pool hit ratio, requires less monitoring and tuning, treats applications equally, and offsets increasing workload of one application by decreasing workload in another. However, with a single buffer pool no preference to applications with different levels of importance can be given, and different access and usage pattern cannot be isolated.

    Multiple buffer pools allow for performance preferences, grouping according to access pattern, different thresholds, separation of table spaces from index spaces to optimize the hit rate for indexes, and more. In most cases, multiple buffer pools are necessary to optimize among total buffer pool size and its cost, and overall transaction performance. However, too many buffer pools also increase the effort to monitor and administer the buffer pools.

  • The total buffer pool size has a great effect on performance. However, if it is too large, and there is not enough memory to allocate them, then a minimum buffer pool for each page size will be allocated, and the performance will be sharply reduced. To calculate the maximum buffer pool size, all other storage utilization must be considered by DB2® as well as the operating system and any other applications. When the total available size is determined, this area can be divided into different buffer pools to improve utilization.
  • Smaller buffer pools are more likely affected by fluctuating workload. Smaller buffer pools tend to show more performance peaks than larger buffer pools.
  • The size of buffer pools that predominantly process write requests can be minimized. These buffer pools usually show a low hit ratio. Consecutive write operations fill up the buffer pool and require the data to be frequently written to disk. This applies typically to objects that log or journalize transaction data.