The role of buffer pools in caching data
Buffer pools are a key element of Db2 performance, and help you to avoid delays when retrieving data.
Db2 can retrieve a page from a buffer pool faster than it can from disk. When data is already in a buffer, an application program avoids the delay of waiting for Db2 to retrieve the data from disk.
You can specify that Db2 uses up to 50 buffer pools that contain 4 KB pages. You can also specify up to 10 buffer pools each that contain 8 KB, 16 KB, and 32 KB pages.
The following figure shows buffer pools with 4 KB and 8 KB pages. The number of pages that a buffer pool contains depends on the size of the buffer pool.
At any time, pages in a virtual buffer pool can be in-use, updated, or available:
- In-use pages are currently being read or updated. The data that they contain is available for use by other applications.
- Updated pagescontain changed data that is not yet written to disk.
- Available pages are ready for use. An incoming page of new data can overwrite available pages.
To avoid disk I/O, you can use updated and available pages that contain data.
When data in the buffer changes, that data must eventually be written back to disk. Because Db2 does not need to write the data to disk right away, the data can remain in the buffer pool for other uses. The data remains in the buffer until Db2 decides to use the space for another page. Until that time, applications can read or change the data without a disk I/O operation.
The key factor that affects the performance of buffer pools is their size. The method that Db2 uses to access buffer pools also affects performance.
Buffer pool size
The size of buffer pools is critical to the performance characteristics of an application or a group of applications that access data in those buffer pools.
By tuning your buffer pools, you can improve the response time and throughput for your applications, and provide optimum use of resources. For example, applications that process online transactions are more likely to need large buffer pools because they often must reaccess the same data. In those cases, storing large amounts of data in a buffer pool enables applications to access data more efficiently.
By making buffer pools as large as possible, you can achieve the following benefits:
- Fewer I/O operations result, which means faster access to your data.
- I/O contention is reduced for the most frequently used tables and indexes.
- Sort speed is increased because of the reduction in I/O contention for work files.
You can use the ALTER BUFFERPOOL command to change the size and other characteristics of a buffer pool at any time while Db2 is running. Use the DISPLAY BUFFERPOOL and ALTER BUFFERPOOL commands to gather buffer pool information and change buffer pool sizes.
Db2 Buffer Pool Analyzer for z/OS® helps database administrators manage buffer pools more efficiently by providing information about current buffer pool behavior and by using simulation to anticipate future behavior. Using this tool, you can take advantage of these features:
- Collection of data about virtual buffer pool activity
- Comprehensive reporting of the buffer pool activity
- Simulated buffer pool usage
- Reports and simulation results
- Expert analysis that is available through an easy-to-use wizard
Db2 Buffer Pool Analyzer capabilities are included in IBM OMEGAMON® for Db2 Performance Expert on z/OS Db2 Performance Expert.
Efficient page access
Db2 determines when to use a method called sequential prefetch to read data pages faster. With sequential prefetch, Db2 determines in advance that a set of data pages is about to be used. Db2 then reads the set of pages into a buffer with a single I/O operation. The prefetch method is always used for table space scans and is sometimes used for index scans. Prefetching is processed concurrently with other application I/O operations.
In addition to a predetermined sequential prefetch, Db2 also supports dynamic prefetch. A dynamic prefetch is a more robust and flexible method that is based on sequential detection.