Db2 for z/OS and vendor products collect and report on several buffer pool characteristics. Db2 statistics (SMF 100 records) and accounting (SMF 101) records can provide buffer pool performance information at the subsystem and thread level. Db2 can externalize multiple IFCIDs (198, 199, 201, and 202) with detailed buffer pools and object information.
Focus on the following metrics when looking at buffer pool performance; they will give you a good idea of overall buffer pool health:
- GETPAGE rate – The average number of GETPAGE requests per second over time. You can instantly compare the relative amount of work supported by each buffer pool. This is a good indicator of overall workload through the buffer pool. It’s also a great metric to review at the Db2 subsystem level.
- Page arrival rate – The average number of pages read into the buffer pool per second. This quickly identifies the buffer pools and objects that are driving your I/O subsystem. This is also a great metric for the entire Db2 subsystem.
- Buffer pool hit ratio (ratio of GETPAGEs resolved without read I/Os / total GETPAGEs) – This value measures overall buffer pool efficiency. Your goal is to have as high a number as possible. Generally, a lower value indicates a need for a larger buffer pool. However, if you have very large objects in the pool that are accessed randomly, then you may always have a smaller hit ratio. In this case, consider lowering the size of the pool. If the pool is page fixed, expect to see a number approaching 100%.
- Page updates per write – The average number of synchronous page updates for each system page written over the length of the interval. This indicates how well the pool size and deferred write thresholds enable repeated updates to the same page to occur before the page is written.
- Pages written per write I/O – The average number of pages written per write I/O over the length of the interval. For the most efficient use of the Db2 asynchronous write engines, the average pages per write should be as high as possible, but must be balanced against the amount of the pool tied up with updated (dirty) pages and the possibility of an undesirable spike in activity when checkpoints occur. Consider page updates per write and the deferred write threshold when reviewing this metric. The goal is to set the deferred write threshold and the vertical deferred write threshold at a level that gets the best possible updates per page write and pages per write I/O ratio.
In addition to these watching these metrics, it’s important to monitor adverse events occurring in the Db2 subsystem, including
- GETPAGE failures due to no available buffers
- GETPAGE failures due to DBM1 virtual storage shortage
- Write I/Os delayed because all 300 write engines are busy
- Data Manager Critical Threshold reached
- Prefetch disabled due to buffer shortage
- Prefetch disabled due to prefetch limit
- Parallel prefetch requests denied due to buffer shortage
- Parallel groups not started due to buffer shortage
- Parallel prefetch requests halved due to buffer shortage
- Parallel prefetch requests quartered due to buffer shortage
- Sort work files exceeded the sequential buffer limit
- Sort-merge passes degraded due to wk file/sequential limit
Buffer Pool Performance Tuning Tools
Buffer pool performance management can be a challenge, but tools are available to help you. These tools fall into two groups: general purpose monitors and buffer pool tools. (BMC System Performance for Db2 provides both types of tools.)
Db2 performance monitors monitor buffer pools in real time and may provide near and long-term historical reporting capabilities. Monitors report Db2 accounting and statistics data about buffer pool performance. Monitors can capture many adverse events, and you can use automation capabilities to respond to these problems. Detailed analysis of buffer pool configurations is typically not provided. Depending on your environment, this may be the only type of Db2 buffer pool monitoring tool you need.
For other Db2 environments, “deep-dive” products provide extensive monitoring and analysis. These tools collect and report on real-time performance metrics and maintain detailed historical performance data that can provide long term analysis of buffer pool performance to help you identify and respond to negative trends. These tools also recommend buffer pool configuration changes including number and size of pools, thresholds, and Db2 object placement.
