IDS Forum

Re: Significance of high Btree percentage?

Posted By: Art Kagel
Date: Tuesday, 6 October 2009, at 11:23 a.m.

In Response To: RE: Significance of high Btree percentage? (Brian Amstutz)

Yes, 35-45% is just about right.

Yes, the BTREE %age is the percentage of the buffer cache contents that are
index pages.

Index pages have a higher priority than data pages, so they tend to stay in
the cache longer than data pages, but they do age out.

NOAGE is a parameter that was introduced along with a failed change to the
buffer cache aging algorithm in early 7.31 releases. That bad algorithm was
the cause of caches going to 90% BTREE pages and higher. That new algorithm
was introduced to eliminate the need for the 7.30 RESIDENT feature (which
allowed the DBA to specify that a table's data pages should have a higher
priority than even index pages in the cache) which was a performance
disaster. (It looked good on paper, but was too easy to abuse and too hard
to use effectively.)

NOAGE=1 was used to disable that "new" algorithm and revert the engine to
the previous algorithm. IDS 10.00 and later engines have another even newer
- and substantially improved - buffer aging algorithm. NOAGE is no longer
needed. I'm not sure if it even does anything any longer.

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 Tue, Oct 6, 2009 at 10:31 AM, Brian Amstutz <
brian.amstutz@asburyseminary.edu> wrote:

> 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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--00151744843a0f10f0047545ce7e

Messages In This Thread

• Significance of high Btree percentage?
  Brian Amstutz -- Monday, 5 October 2009, at 3:09 p.m.
  • Re: Significance of high Btree percentage?
    Art Kagel -- Monday, 5 October 2009, at 4:20 p.m.
    • RE: Significance of high Btree percentage?
      Brian Amstutz -- Tuesday, 6 October 2009, at 10:31 a.m.
      • Re: Significance of high Btree percentage?
        Art Kagel -- Tuesday, 6 October 2009, at 11:23 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.