Buffer Pool Sizing: Page Residency Time

In a DB2utor blog entry last year a reader asked that I write about buffer pool tuning. This post prompted a reply from DB2 expert Randy Ebersole, who said I should look into how page residency can help you determine proper buffer pool size.

I love it when readers share their ideas by posting to this blog. Randy's contribution was especially valuable because, to be honest, Id never heard of page residency. It took me awhile, but I've been researching this topic, and here's what I've learned.

Page residency time is a simple calculation to measure the read efficiency of a buffer pool. The idea is to calculate the average time that a page is resident in the buffer pool. There are three classifications of page residency time measurements: system, random page and sequential page.

 

• System residency time (seconds) = buffer pool size / total pages read per second
• Random page residency time (seconds) = maximum [system residency time, buffer pool size * (1-VPSEQT/100) / synchronous pages read per second]
• Sequential page residency time (seconds) = minimum [system residency time, buffer pool size * (VPSEQT/100) / asynchronous pages read per second]

where:
synchronous pages read = synchronous read I/O for random getpages + synchronous read I/O for sequential getpages (Field Name: QBSTRIO)

total pages read = synchronous read I/O for random getpages + synchronous read I/O for sequential getpages + sequential prefetch pages read asynchronously + list prefetch pages read asynchronously + dynamic prefetch pages read asynchronously
(Field name: QBSTRIO + QBSTSPP + QBSTLPP + QBSTDPP)

The page residency time is an indicator of stress on the buffer pool. Experience suggests that a residency time of less than five minutes likely means that performance can be improved by increasing the pool size. On the other hand, if the residency time is 15 minutes, then you have an opportunity to reduce pool size. If the residency time is less than 10 seconds, you may see pages stolen before they're even used, resulting in synchronous I/O which causes greater stress on the system.

Have you used this formula? If so, has it improved overall system performance?