Significance of high Btree percentage?
A DBA on IDS 10.00 (HP-UX) asked what a high Btree percentage in 'onstat -P' means, seeing ~56% Btree vs 43% data, and how to tune it. Art Kagel replied that there is no universal 'bad' figure: it depends on access patterns, with typical OLTP systems under ~35% Btree pages in the buffer cache, data warehouses above ~65%, and DSS in between. He also noted an old bug in early 7.31 releases where index pages wrongly dominated the cache (>90%), but that 10.00 is not affected. The poster's follow-up questions about buffer flushing, LRUAGE and which parameters to adjust received no answer in the thread, so no further resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
HPUX 11vi2, 4x440MHz cpu, 8GB RAM, Informix 10.00.HC5=20
So, what is the significance/meaning of a high Btree percentage (onstat
-P)?
Often (but not always) ours is quite high which, from the little I've
read, is not a good thing. For example:
onstat -P | tail -10
Totals: 400000 223186 173344 3470 299
Percentages:
Data 43.34
Btree 55.80
Other 0.87
Is it OK/normal for it to fluctuate between a low and high percentage?
What settings should I be looking at to tune?
Thanks!
Brian Amstutz
Asbury Theological Seminary
"normal" is going to depend on your access patterns. A typical OLTP system
will show less than 35% BTREE pages in the buffer cache, a Data Warehouse
will typically show upwards of 65% BTREE pages, DSS somewhere in between.
There were versions, early 7.31 releases, that ran into a bug that caused
index pages to incorrectly dominate the cache (>90%) causing performance
problems, however, 10.00 does not suffer from this bug.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Oct 5, 2009 at 3:09 PM, Brian Amstutz <
brian.amstutz@asburyseminary.edu> wrote:
> HPUX 11vi2, 4x440MHz cpu, 8GB RAM, Informix 10.00.HC5=20
>
> So, what is the significance/meaning of a high Btree percentage (onstat
> -P)?
>
> Often (but not always) ours is quite high which, from the little I've
> read, is not a good thing. For example:
>
> onstat -P | tail -10>
> Totals: 400000 223186 173344 3470 299
>
> Percentages:
> Data 43.34
> Btree 55.80
> Other 0.87
>
> Is it OK/normal for it to fluctuate between a low and high percentage?
> What settings should I be looking at to tune?
>
> Thanks!
>
> Brian Amstutz
> Asbury Theological Seminary
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747b76e650ac5047535d868
So if ours is mostly an OLTP system with a little bit of Data Warehousing
activity thrown in, then a BTree of say, 35%-45% (or lower) is probably
"OK", correct.?
As far as 'meaning' - is it correct to say, then, that this represents
(the percentage of) btree indexes that were loaded into the BUFFER and are
still sitting there?
Are these flushed during normal BUFFER recycling?
Is a high Btree% potentially not a good thing because that means there's
less BUFFER space for data?
If I see this continually staying high, what parameters do I need to be
looking at in order to bring it back down (i.e. #buffers, #lrus, ...)?
I saw a reference to an LRUAGE environment variable - would this be
something I need to explore using if Btree % is continually high?
Thanks!
Brian
Asbury Theological Seminary
>-----Original Message-----
>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
>Sent: Monday, October 05, 2009 4:21 PM
>To: ids@iiug.org
>Subject: Re: Significance of high Btree percentage? [17345]
>
>"normal" is going to depend on your access patterns. A typical OLTP
system
>will show less than 35% BTREE pages in the buffer cache, a Data Warehouse
>will typically show upwards of 65% BTREE pages, DSS somewhere in between.
>
>There were versions, early 7.31 releases, that ran into a bug that caused
>index pages to incorrectly dominate the cache (>90%) causing performance
>problems, however, 10.00 does not suffer from this bug.
>
>Art
>
>Art S. Kagel
>Oninit (www.oninit.com)
>IIUG Board of Directors (art@iiug.org)
>
>Disclaimer: Please keep in mind that my own opinions are my own opinions
and
>do not reflect on my employer, Oninit, the IIUG, nor any other
organization
>with which I am associated either explicitly or implicitly. Neither do
>those opinions reflect those of other individuals affiliated with any
entity
>with which I am affiliated nor those of the entities themselves.
>
>On Mon, Oct 5, 2009 at 3:09 PM, Brian Amstutz <
>brian.amstutz@asburyseminary.edu> wrote:
>
>> HPUX 11vi2, 4x440MHz cpu, 8GB RAM, Informix 10.00.HC5=20
>>
>> So, what is the significance/meaning of a high Btree percentage (onstat
>> -P)?
>>
>> Often (but not always) ours is quite high which, from the little I've
>> read, is not a good thing. For example:
>>
>> onstat -P | tail -10>>
>> Totals: 400000 223186 173344 3470 299
>>
>> Percentages:
>> Data 43.34
>> Btree 55.80
>> Other 0.87
>>
>> Is it OK/normal for it to fluctuate between a low and high percentage?
>> What settings should I be looking at to tune?
>>
>> Thanks!
>>
>> Brian Amstutz
>> Asbury Theological Seminary
>>
>>
>>
>>
>*************************************************************************
******
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
>--00151747b76e650ac5047535d868
>
>
>*************************************************************************
******
> Forum Note: Use "Reply" to post a response in the discussion forum.