A New Option to Block Page-Stealing

Before an application can access a page of data, it must be read into a buffer pool. If the buffer pool is full, then DB2 must delete an existing page before it can read the new one in. This process of page removal is called page-stealing. DB2 usually invokes a least-recently-used algorithm--PGSTEAL(LRU)--to manage pages in storage. This algorithm deletes the oldest pages and leaves more recently read pages in the buffer pool.

Some applications can benefit by having code tables--also known as look up tables--pinned in memory to avoid I/O. The DB2 systems programmer would normally create a separate buffer pool and make it large enough to hold the entire table or index space. For these buffer pools, it's recommended that the PGSTEAL(FIFO) (first in, first out) algorithm be used instead of LRU. FIFO always removes the oldest pages first, regardless of how often they're referenced. This provides a slight cost savings while also reducing internal DB2 latch contention.

One downside to both LRU and FIFO is that neither has the capability to block DB2 from page-stealing. With page-stealing, the page to be deleted could be reread, a process which obviously adds to I/O overhead. However, with DB2 10, it's now possible to block page-stealing. With the new option PGSTEAL(NONE), page usage isn't monitored.

Here are some examples of how the page steal parameter is used:

• With PGSTEAL(LRU), DB2 monitors the pages, keeping frequently used pages in the buffer pool while removing lesser-used pages from the pool. This is the default option and should be used in most cases, because the application will most likely reread the most recent page brought into memory. By doing so, DB2 doesn't need to perform an I/O to process the getpage.
• With PGSTEAL(FIFO), DB2 monitors how long each page is in the buffer pool. The oldest pages are always freed first, regardless of how often they're referenced. FIFO is typically chosen when the index or table space are read into a buffer pool with the intent of keeping the pages in memory to avoid I/O. In addition to decreasing the cost of doing getpage operations, this option can reduce internal latch contention, which supports environments that require very high concurrency.
• With PGSTEAL(NONE), DB2 doesn't monitor pages in the buffer pool. Like FIFO, option NONE is a good choice when table and index space are loaded into memory. NONE provides greater reduction in the cost of performing getpage operations and avoids the cost of monitoring the system for page-stealing. This option can also reduce internal DB2 latch contention that supports very high concurrency.

For a summary of these options, go here.

Are you responsible for tuning DB2 buffer pools? Do you use the FIFO option? Have you had a chance to use NONE? Please share your experiences in Comments.