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?

Comments

[Mike Wingfield]

How are you determining pages read per second?

[Wilfried Laser]

Hi Troy,
very interesting article. But I have the same question as Mike.
How are you determining pages read per second? I can't find any information. DIS BPool don't show it. Do I need Omegamon or Mainview or .. ?
Best regards
Wilfried

[Troy Coleman]

Hi Wilfried and Mike,
The calculation I shared came from the DB2 9 for z/OS: Buffer Pool Monitoring
and Tuning Guide. I'm not aware of any monitor showing these numbers in seconds. I use Insight which shows the metric every 30 seconds. So if I have a buffer pool size of 10504 with read I/O of 21 in 30 seconds which is about .7 per second. This would give me a page residency of 15,006 seconds.

[Leela Krishna Kandrakota]

New reply to an old post -
There is a REXX written by Mike Bracey (IBM) to execute a DISPLAY BUFFERPOOL(ACTIVE) DETAIL at regular intervals (5,10,15,30,60 minutes) and calculate the I/O rate for the interval by the differences.

One version of the Rexx is at:
http://www.ruban.de/DB2_for_z_OS/z_OS_Code/DB2_z_OS_Code_-_Detail_View/db2_z_os_code_-_detail_view_5.html

[NaiLi]

Troy;
Thanks for the posting. I've got some very interesting numbers. For the workhorse bufferpools, the system page residence is less than 5 seconds. Some others are less than 30 seconds.. Serious tuning is due.
BTW, I used CA Detector which is set at hour intervals, that the get page numbers are tallied at hour level. That may or may not skewed my calculation.

NaiLi

[Joel Goldstein]

Page residency time, like hit ratios are interesting, but not a measureable metric for pool tuning. There will be a vast difference in useful residency times based on the overall pool getpage rates.
You may also have some very large and very random obects that almost always require an IO - they will skew your residency times. Likewise you may have some pages from other objects that live in the pool. You need to look specifically at the objects generating the highest IO rate/second in your workhorse pool(s). Object separation may do more for you than just adding memory.
Overall, the critical metric for pool performance is the IO rate/second because you can convert that to application delay/performance, and to CPU costs.

[NaiLi]

Joel;
Good to hear from you! Appreciate the comments! I had the objects separated by use, so code tables are more or less live in one pool, large sequential objects in one pool, and large dynmic objects in another, volatiles in their own. Index/data separated. The thresholds are set favoring the access patterns in each. But through time, the pools got messy and murky again, and the io/sec is out of shape. Page residence angle is new to me, fun to look at:) Yes, the large dynamics will definitely toss the residence out of the pool! I am with you, IO reduction is what we should thrive for!
NaiLi