Negative value for bufferpool hit ratio

#1

From the administrative view sysibmadm.bp_hitratio I get a high
negative value ( -89%) on one of the bufferpools. I didn't think that
could be possible since I assumed that the number of physical reads
was included in the number for logical reads, and that the formula
was: 1 - (physical reads/logical reads).

Am I missing something and how could one explain a negative value?

Regards
Odd B Andersen

#2

On Apr 4, 11:56*am, oddbande <odd.ander... (AT) ergo (DOT) no> wrote:

Quote:

From the administrative view sysibmadm.bp_hitratio I get a high
negative value ( -89%) on one of the bufferpools. I didn't think that
could be possible since I assumed that the number of physical reads
was included in the number for logical reads, and that the formula
was: 1 - (physical reads/logical reads).

Am I missing something and how could one explain a negative value?

Regards
Odd B Andersen

The 9.5.0.7 view definition uses DEC((1 -
(FLOAT(TOTAL_DATA_PHYSICAL_READS + TOTAL_INDEX_PHYSICAL_READS +
TOTAL_XDA_PHYSICAL_READS) / FLOAT(TOTAL_DATA_LOGICAL_READS +
TOTAL_INDEX_LOGICAL_READS + TOTAL_XDA_LOGICAL_READS))) * 100,5,2)
which is pretty much what you said, so it shouldn't happen. What are
the numbers in your case? You could try to recycle your instance as it
will reset these counters.

--
Frederik Engelen

#3

On 04/04/2011 11:56 AM, oddbande wrote:

Quote:

From the administrative view sysibmadm.bp_hitratio I get a high
negative value ( -89%) on one of the bufferpools. I didn't think that
could be possible since I assumed that the number of physical reads
was included in the number for logical reads, and that the formula
was: 1 - (physical reads/logical reads).

To my understanding your assumption is correct. If a page is requested,
a logical read is done. If the page can't be found a physical read
occurs. What are TOTAL_LOGICAL_READS and TOTAL_PHYSICAL_READS for that
bufferpool?

/Lennart

#4

On 4 apr, 18:07, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

On 04/04/2011 11:56 AM, oddbande wrote:

From the administrative view sysibmadm.bp_hitratio I get a high
negative value ( -89%) on one of the bufferpools. I didn't think that
could be possible since I assumed that the number of physical reads
was included in the number for logical reads, and that the formula
was: 1 - (physical reads/logical reads).

To my understanding your assumption is correct. If a page is requested,
a logical read is done. If the page can't be found a physical read
occurs. What are TOTAL_LOGICAL_READS and TOTAL_PHYSICAL_READS for that
bufferpool?

/Lennart

Today I see that the value for hit ratio on that bufferpool is a much
higher negative value (-123,41%). The value for TOTAL_LOGICAL_READS is
39143168 and for TOTAL_PHYSICAL_READS is 87450083. DATA_LOGICAL_READS
is 34807361, DATA_PHYSICAL_READS is 87434907 and
DATA_HIT_RATIO_PERCENT IS -151,19. INDEX_LOGICAL_READS is 4335807,
INDEX_PHYSICAL_READS is 15176 and INDEX_HIT_RATIO_PERCENT is 99,64.

I must tell that we get a lot of messages for this bufferpool in the
db2diag.log like this: "no available buffer pool pages" . I'm sure why
this happens, but we have turned on STMM and I would expaect DB2 to
tune itself in a way so we can avoid this. Perhaps it is that message
that causes this stramge value?

Regards
Odd B

#5

"oddbande" <odd.andersen (AT) ergo (DOT) no> wrote

Quote:

I must tell that we get a lot of messages for this bufferpool in the
db2diag.log like this: "no available buffer pool pages" . I'm sure why
this happens, but we have turned on STMM and I would expaect DB2 to
tune itself in a way so we can avoid this.

Hahahahahahahha.

#6

On 5 apr, 10:58, oddbande <odd.ander... (AT) ergo (DOT) no> wrote:

Quote:

On 4 apr, 18:07, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com
wrote:

On 04/04/2011 11:56 AM, oddbande wrote:

From the administrative view sysibmadm.bp_hitratio I get a high
negative value ( -89%) on one of the bufferpools. I didn't think that
could be possible since I assumed that the number of physical reads
was included in the number for logical reads, and that the formula
was: 1 - (physical reads/logical reads).

To my understanding your assumption is correct. If a page is requested,
a logical read is done. If the page can't be found a physical read
occurs. What are TOTAL_LOGICAL_READS and TOTAL_PHYSICAL_READS for that
bufferpool?

/Lennart

Today I see that the value for hit ratio on that bufferpool is a much
higher negative value (-123,41%). The value for TOTAL_LOGICAL_READS is
39143168 and for TOTAL_PHYSICAL_READS is 87450083. DATA_LOGICAL_READS
is 34807361, DATA_PHYSICAL_READS is 87434907 and
DATA_HIT_RATIO_PERCENT IS -151,19. INDEX_LOGICAL_READS is 4335807,
INDEX_PHYSICAL_READS is 15176 and INDEX_HIT_RATIO_PERCENT is 99,64.

I must tell that we get a lot of messages for this bufferpool in the
db2diag.log like this: "no available buffer pool pages" . I'm sure why
this happens, but we have turned on STMM and I would expaect DB2 to
tune itself in a way so we can avoid this. Perhaps it is that message
that causes this stramge value?

Regards
Odd B

Sorry about my spelling errors:-)
But what I wanted to say: I'm NOT sure why this happens, but we have
turned on STMM and I would expect DB2 to tune itself in a way
that would prevent these messages from occuring. Could it be that
there is a connection between these messages and the
negative value for bufferpool hit ratio that we are experiencing?

Regards
Odd B

#7

On Apr 5, 7:11*am, oddbande <odd.ander... (AT) ergo (DOT) no> wrote:

Quote:

On 5 apr, 10:58, oddbande <odd.ander... (AT) ergo (DOT) no> wrote:

On 4 apr, 18:07, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com
wrote:

On 04/04/2011 11:56 AM, oddbande wrote:

From the administrative view sysibmadm.bp_hitratio I get a high
negative value ( -89%) on one of the bufferpools. I didn't think that
could be possible since I assumed that the number of physical reads
was included in the number for logical reads, and that the formula
was: 1 - (physical reads/logical reads).

To my understanding your assumption is correct. If a page is requested,
a logical read is done. If the page can't be found a physical read
occurs. What are TOTAL_LOGICAL_READS and TOTAL_PHYSICAL_READS for that
bufferpool?

/Lennart

Today I see that the value for hit ratio on that bufferpool is a much
higher negative value (-123,41%). The value for TOTAL_LOGICAL_READS is
39143168 and for TOTAL_PHYSICAL_READS is 87450083. DATA_LOGICAL_READS
is 34807361, DATA_PHYSICAL_READS is 87434907 and
DATA_HIT_RATIO_PERCENT IS -151,19. INDEX_LOGICAL_READS is 4335807,
INDEX_PHYSICAL_READS is 15176 and INDEX_HIT_RATIO_PERCENT is 99,64.

I must tell that we get a lot of messages for this bufferpool in the
db2diag.log like this: "no available buffer pool pages" . I'm sure why
this happens, but we have turned on STMM and I would expaect DB2 to
tune itself in a way so we can avoid this. Perhaps it is that message
that causes this stramge value?

Regards
Odd B

Sorry about my spelling errors:-)
But what I wanted to say: I'm NOT sure why this happens, *but we have
turned on STMM and I would expect DB2 to tune itself in a way
that would prevent these messages from occuring. Could it be that
there is a connection between these messages and the
negative value for bufferpool hit ratio that we are experiencing?

Regards
Odd B- Hide quoted text -

- Show quoted text -

IE could your msgs and negative bufferpool hit rate be a coincidence?
I doubt that.... cycling DB2 is a good idea...looking at db2top for
available memory might help...how much memory is available? What are
your bp settings? Are you overriding the BP settings with a registry
variable? Good questions all.

#8

On 2011-04-05 13:11, oddbande wrote:
[...]

Quote:

Today I see that the value for hit ratio on that bufferpool is a much
higher negative value (-123,41%). The value for TOTAL_LOGICAL_READS is
39143168 and for TOTAL_PHYSICAL_READS is 87450083. DATA_LOGICAL_READS
is 34807361, DATA_PHYSICAL_READS is 87434907 and
DATA_HIT_RATIO_PERCENT IS -151,19. INDEX_LOGICAL_READS is 4335807,
INDEX_PHYSICAL_READS is 15176 and INDEX_HIT_RATIO_PERCENT is 99,64.

I must tell that we get a lot of messages for this bufferpool in the
db2diag.log like this: "no available buffer pool pages" . I'm sure why
this happens, but we have turned on STMM and I would expaect DB2 to
tune itself in a way so we can avoid this. Perhaps it is that message
that causes this stramge value?

Regards
Odd B

Sorry about my spelling errors:-)
But what I wanted to say: I'm NOT sure why this happens, but we have
turned on STMM and I would expect DB2 to tune itself in a way
that would prevent these messages from occuring. Could it be that
there is a connection between these messages and the
negative value for bufferpool hit ratio that we are experiencing?

I can see you concern, the numbers are pretty wierd and I have no idea
on how to intepret them. How big is the bufferpool, how much of it is
used, and is the bufferpool started alright? You can probably find info
on size matters in SNAPDB_MEMORY_POOL. Also, what are the number for
sync vs async writes for the BP?

/Lennart

#9

On 5 apr, 15:26, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

On 2011-04-05 13:11, oddbande wrote:
[...]

Today I see that the value for hit ratio on that bufferpool is a much
higher negative value (-123,41%). The value for TOTAL_LOGICAL_READS is
39143168 and for TOTAL_PHYSICAL_READS is 87450083. DATA_LOGICAL_READS
is 34807361, DATA_PHYSICAL_READS is 87434907 and
DATA_HIT_RATIO_PERCENT IS -151,19. INDEX_LOGICAL_READS is 4335807,
INDEX_PHYSICAL_READS is 15176 and INDEX_HIT_RATIO_PERCENT is 99,64.

I must tell that we get a lot of messages for this bufferpool in the
db2diag.log like this: "no available buffer pool pages" . I'm sure why
this happens, but we have turned on STMM and I would expaect DB2 to
tune itself in a way so we can avoid this. Perhaps it is that message
that causes this stramge value?

Regards
Odd B

Sorry about my spelling errors:-)
But what I wanted to say: I'm NOT sure why this happens, *but we have
turned on STMM and I would expect DB2 to tune itself in a way
that would prevent these messages from occuring. Could it be that
there is a connection between these messages and the
negative value for bufferpool hit ratio that we are experiencing?

I can see you concern, the numbers are pretty wierd and I have no idea
on how to intepret them. How big is the bufferpool, how much of it is
used, and is the bufferpool started alright? You can probably find info
on size matters in SNAPDB_MEMORY_POOL. Also, what are the number for
sync vs async writes for the BP?

/Lennart

The bufferpool with negative hit ratio was defined with size set to
AUTOMATIC. Using the db2pd command I found that DB2 had its size set
to 1000 pages. I used the db2pd command on the tablespaces using this
bufferpool and found that the prefetch size for those to be 12800
(automatic). I turned off AUTOMATIC for both the bufferpool and the
prefetch size for the tablespaces. The size of the bufferpool was set
(by me) to 3000, and the prefetch size to 640. Then I restarted the
instance. Now the hit ratio for the bufferpool no longer is negative,
even if it is quite low.

I find it strange that STMM is not able to resize the bufferpool so
that it doesn't get smaller than the prefetch size, or even that the
prefetch size is not set to a higher value than the bufferpool. I
assume that I got the message "no available bufferpool pages" because
the bufferpool was too small to hold the prefetched data. The error
message is now gone.

Since both are set to AUTOMATIC I would expect db2 to be able to
resize those values to some reasonable values. But this doesn't happen
as far as I can see.

Regards
Odd B

#10

On 2011-04-06 14:26, oddbande wrote:
[...]

Quote:

The bufferpool with negative hit ratio was defined with size set to
AUTOMATIC. Using the db2pd command I found that DB2 had its size set
to 1000 pages. I used the db2pd command on the tablespaces using this
bufferpool and found that the prefetch size for those to be 12800
(automatic). I turned off AUTOMATIC for both the bufferpool and the
prefetch size for the tablespaces. The size of the bufferpool was set
(by me) to 3000, and the prefetch size to 640. Then I restarted the
instance. Now the hit ratio for the bufferpool no longer is negative,
even if it is quite low.

Ok, that might explain why there are more physical than logical I/O. It
also explains the error message.

Quote:

I find it strange that STMM is not able to resize the bufferpool so
that it doesn't get smaller than the prefetch size, or even that the
prefetch size is not set to a higher value than the bufferpool. I
assume that I got the message "no available bufferpool pages" because
the bufferpool was too small to hold the prefetched data. The error
message is now gone.

Since both are set to AUTOMATIC I would expect db2 to be able to
resize those values to some reasonable values. But this doesn't happen
as far as I can see.

I haven't used STMM myself. According to what I've heard and read, it
works well for some, and not so well for others. Perhaps you should open
a PMR regarding this, your case might give valuble feedback to the STMM
dev team.

/Lennart