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.
- by the agent. This is synchronous I/O.
- by the I/O servers (prefetchers). This is asynchronous I/O.
- 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.
This calculation takes into account all of the pages (index and data) that are cached by the buffer pool.
((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
You can also use the MON_BP_UTILIZATION administrative view as a convenient method of monitoring the hit ratio for your buffer pools.
- 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
((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.
- 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.