Monitoring buffer pool activity

The database server reads and updates all data from a buffer pool. Data is copied from a disk to a buffer pool as it is required by applications.

Pages are placed in a buffer pool:

by the agent. This is synchronous I/O.
by the I/O servers (prefetchers). This is asynchronous I/O.

Pages are written to disk from a buffer pool:

by the agent, synchronously
by page cleaners, asynchronously

If the server needs to read a page of data, and that page is already in the buffer pool, then the ability to access that page is much faster than if the page had to be read from disk. It is desirable to hit as many pages as possible in the buffer pool. Avoiding disk I/O is an important factor in database performance, therefore proper configuration of the buffer pools is one of the most important considerations for performance tuning.

The buffer pool hit ratio indicates the percentage of time that the database manager did not need to load a page from disk in order to service a page request because the page was already in the buffer pool. The greater the buffer pool hit ratio, the lower the frequency of disk I/O.

Note: The information that follows discusses buffer pools in environments other than Db2® pureScale® environments. Buffer pools work differently in Db2 pureScale environments. For more information, see Buffer pool monitoring in a Db2 pureScale environment.

For example, the overall buffer pool hit ratio can be calculated as follows:

     ((pool_data_lbp_pages_found
+ pool_index_lbp_pages_found
 + pool_xda_lbp_pages_found
+ pool_col_lbp_pages_found
- pool_async_data_lbp_pages_found
- pool_async_index_lbp_pages_found - pool_async_xda_lbp_pages_found
- pool_async_col_lbp_pages_found) 
/ (pool_data_l_reads
+ pool_index_l_reads + pool_xda_l_reads + pool_col_l_reads + pool_temp_data_l_reads
+ pool_temp_xda_l_reads + pool_temp_index_l_reads + pool_temp_col_l_reads))
× 100

This calculation takes into account all of the pages (index and data) that are cached by the buffer pool.

You can also use the MON_BP_UTILIZATION administrative view as a convenient method of monitoring the hit ratio for your buffer pools.

For a large database, increasing the buffer pool size may have minimal effect on the buffer pool hit ratio. Its number of data pages may be so large, that the statistical chances of a hit are not improved by increasing its size. Instead, you might find that tuning the index buffer pool hit ratio achieves the required result. This can be achieved using two methods:

Split the data and indexes into two different buffer pools and tune them separately.
Use one buffer pool, but increase its size until the index hit ratio stops increasing. The index buffer pool hit ratio can be calculated as follows:
```
((pool_index_lbp_pages_found
- pool_async_index_lbp_pages_found )  / (pool_index_l_reads
+ pool_temp_index_l_reads)) × 100
```

The first method is often more effective, but because it requires indexes and data to reside in different table spaces, it may not be an option for existing databases. It also requires tuning two buffer pools instead of one, which can be a more difficult task, particularly when memory is constrained.

You should also consider the impact that prefetchers may be having on the hit ratio. Prefetchers read data pages into the buffer pool anticipating their need by an application (asynchronously). In most situations, these pages are read just before they are needed (the required case). However, prefetchers can cause unnecessary I/O by reading pages into the buffer pool that will not be used. For example, an application starts reading through a table. This is detected and prefetching starts, but the application fills an application buffer and stops reading. Meanwhile, prefetching has been done for a number of additional pages. I/O has occurred for pages that will not be used and the buffer pool is partially taken up with those pages.

Page cleaners monitor the buffer pool and asynchronously write pages to disk. Their goals are:

Ensure that agents will always find free pages in the buffer pool. If an agent does not find free pages in the buffer pool, it must clean them itself, and the associated application will have a poorer response.
Speed database recovery, if a system crash occurs. The more pages that have been written to disk, the smaller the number of log file records that must be processed to recover the database.

Although dirty pages are written out to disk, the pages are not removed from the buffer pool right away, unless the space is needed to read in new pages.

Note: Buffer pool information is typically gathered at a table space level, but the facilities of the database system monitor can roll this information up to the buffer pool and database levels. Depending on your type of analysis, you may need to examine this data at any or all of these levels.