buffer pool hit ratio

[eric]

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

[Ian]

eric wrote:

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).

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! =-----

[eric]

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 documentation.
Can someone explain why this formula and not Ian's one ?

Thanks,

Eric

"Ian" <ia*****@mobileaudio.com> a écrit dans le message de
news:3f**********@corp.newsgroups.com...

eric wrote:

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).

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! =-----

[PM-pm3iinc-nospam]

(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

[eric]

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" <pm*****@sympatico.ca> a écrit dans le message de
news:an******************@news20.bellglobal.com...

(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

[Todd McNeill]

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" <no***********@wanadoo.fr> wrote in message news:<bn**********@news-reader1.wanadoo.fr>...

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 documentation.
Can someone explain why this formula and not Ian's one ?

Thanks,

Eric

"Ian" <ia*****@mobileaudio.com> a écrit dans le message de
news:3f**********@corp.newsgroups.com...

eric wrote:

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).

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! =-----

[Mark A]

"Todd McNeill" <to***@bluefiresystems.com> wrote in message
news:d3**************************@posting.google.c om...

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

It only makes sense to measure the hit ratio once the buffer pool has been
primed. The cumulative buffer pool hit ratio from database start to stop can
never be 100%.

[Ian]

eric wrote:

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 documentation.
Can someone explain why this formula and not Ian's one ?

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! =-----