Professional Web Applications Themes

buffer pool hit ratio - IBM DB2

Hi there, BP hit ratio = 1 - (BP physical reads / BP logical reads). If all the BP physical reads are asynchronous, it should mean that the pages are brought up to the bufferpool before the database manager needs them. But in that case, the BP hit ratio will be 0% (BP physical reads = BP async. physical reads = BP logical reads). Could this formula BP hit ratio = 1 - ((BP physical reads - BP async. physical reads) / BP logical reads) be more accurate or am I missing a point ? Thanks for your help, Eric...

  1. #1

    Default buffer pool hit ratio

    Hi there,

    BP hit ratio = 1 - (BP physical reads / BP logical reads).

    If all the BP physical reads are asynchronous, it should mean that the pages
    are brought up to the bufferpool before the database manager needs them.
    But in that case, the BP hit ratio will be 0% (BP physical reads = BP async.
    physical reads = BP logical reads).

    Could this formula BP hit ratio = 1 - ((BP physical reads - BP async.
    physical reads) / BP logical reads) be more accurate or am I missing a point
    ?

    Thanks for your help,

    Eric



    eric Guest

  2. #2

    Default Re: buffer pool hit ratio

    eric wrote:
     

    Your formula is not correct, and I think that's what's causing
    your confusion. The total number of reads in the database is
    the sum of the logical and physical reads. Therefore,

    BP hit ratio = 100 * logical / (physical + logical).


    As you can see, if BP physical reads = 0, then the hit ratio is
    100%.


    Good luck,





    -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
    Ian Guest

  3. #3

    Default Re: buffer pool hit ratio

    Thanks Ian,

    Your formula sounds more comprehensible to me.
    BP hit ratio = 100 * logical / (physical + logical).

    However, the formula BP hit ratio = (1 - (BP physical reads / BP logical
    reads))*100 was taken from the DB2 UDB HTML doentation.
    Can someone explain why this formula and not Ian's one ?

    Thanks,

    Eric

    "Ian" <com> a écrit dans le message de
    news:3f93b73b$newsgroups.com... [/ref]
    pages [/ref]
    async. 
    >
    > Your formula is not correct, and I think that's what's causing
    > your confusion. The total number of reads in the database is
    > the sum of the logical and physical reads. Therefore,
    >
    > BP hit ratio = 100 * logical / (physical + logical).
    >
    >
    > As you can see, if BP physical reads = 0, then the hit ratio is
    > 100%.
    >
    >
    > Good luck,
    >
    >
    >
    >
    >
    > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/ref]


    eric Guest

  4. #4

    Default Re: buffer pool hit ratio

    PM,

    If you take the (1 - (BP physical reads / BP logical reads)) * 100 formula
    in a DSS context, where let say you have many physical ios and 95% of them
    are asynchronous, the formula always leads you to a very weak hit ratio, for
    example: 1 - (500 / 600) = 16.7% [ 500*.95=475 async ios]

    Now, if you substract the Async IOs:
    (1 - (500 - 475)/ 600) * 100, your hit ratio seems better, = 95.8%

    Am I right to change the formula ? It seems to me it's adequate in OLTP
    environment, not DSS.

    Any additional thoughts ?

    Thanks,

    Eric


    "PM-pm3iinc-nospam" <ca> a écrit dans le message de
    news:anWkb.3202$bellglobal.com... 


    eric Guest

  5. #5

    Default Re: buffer pool hit ratio

    (BP physical reads / BP logical reads) is there to measure the relation
    between
    the physical io's (disk access) vs the logical ones (buffered pages/in
    bufferpool).

    There are logical and physical io's.
    Physical io's can be split into sync (direct) and async io's (prefetch,
    etc.).

    logical
    physical sync
    physical async

    PM


    PM-pm3iinc-nospam Guest

  6. #6

    Default Re: buffer pool hit ratio

    Eric-

    A couple of us were going around about this formula today until we
    took a closer look at the 7.1 Performance Tuning Guide (p. 242):

    "Buffer Pool Data Logical Reads: Denotes the total number of read
    data requests that went through the buffer pool.
    Buffer Pool Data Physical Reads: Denotes the number of read requests
    performed that required I/O to place data pages in the buffer pool."

    So it seems that Logical Reads are the TOTAL requests for data from
    the buffer pool, regardless of whether the pages were in the buffer
    pool or not, in which case, the IBM formula is correct. I just double
    checked the algebra :)

    It's a bummer for me, because I have been using the formula Ian posted
    for a while now, and my buffer pool hit ratios are now much lower than
    I originally thought.

    Hope this helps,
    Todd

    "eric" <fr> wrote in message news:<bn176c$ort$wanadoo.fr>... [/ref]
    > pages [/ref]
    > async. 
    > >
    > > Your formula is not correct, and I think that's what's causing
    > > your confusion. The total number of reads in the database is
    > > the sum of the logical and physical reads. Therefore,
    > >
    > > BP hit ratio = 100 * logical / (physical + logical).
    > >
    > >
    > > As you can see, if BP physical reads = 0, then the hit ratio is
    > > 100%.
    > >
    > >
    > > Good luck,
    > >
    > >
    > >
    > >
    > >
    > > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    > > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    > > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/ref][/ref]
    Todd Guest

  7. #7

    Default Re: buffer pool hit ratio

    "Todd McNeill" <com> wrote in message
    news:google.com... 
    It only makes sense to measure the hit ratio once the buffer pool has been
    primed. The ulative buffer pool hit ratio from database start to stop can
    never be 100%.


    Mark Guest

  8. #8

    Default Re: buffer pool hit ratio

    eric wrote:
     

    Oops, looks like _I've_ been using the wrong formula for a long
    time! So I've been reading the doc... My mistake was thinking
    that a read is either logical OR physical, when in reality the
    logical read may require a physical read to be satisfied. Remember
    that all data access goes through the bufferpool.

    Back to your original question, IF you did have a situation
    where physical reads = 0 (but the async bp reads was high),
    then you would get a hit ratio of 100%. It would just be
    indicative of the fact that your prefetching was VERY effective.


    Thanks for helping me get a correct understanding of this...


    Ian





    -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
    Ian Guest

Similar Threads

  1. Replies: 5
    Last Post: April 12th, 02:24 AM
  2. High Buffer Pool read time?
    By Bruce in forum IBM DB2
    Replies: 2
    Last Post: October 6th, 10:17 PM
  3. Replies: 0
    Last Post: August 23rd, 09:14 AM
  4. Setting shared pool size and Java pool size on 9i
    By Howard J. Rogers in forum Oracle Server
    Replies: 1
    Last Post: December 11th, 09:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139