Yes I was interested in this too.

I think a blue person came on here and said, "If you put this line in
your onconfig, all your problems with the b-tree scanner will be
forgotten". So I did and I forgot all about my b-tree scanner. But I
still don't know what the heck it's doing.

BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000



-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of
Darren_Jacobs@carmax.com
Sent: Wednesday, June 07, 2006 1:58 PM
To: TBP
Cc: informix-list-bounces@iiug.org; informix-list@iiug.org
Subject: Re: Checking indexes after large deletes

I recall sometime back that someone was going to generate a paper on
tuning the b-tree scanner. Anyone recall who it was? Jerry, weren't
you interested in this too?

Art, was it you by chance?

peace





TBP

<TheBigPotato@Not

Here.Co.Uk>
To
Sent by: informix-list@iiug.org

informix-list-bou
cc
nces@iiug.org


Subject
Re: Checking indexes after large

06/07/2006 02:30 deletes

PM















Adam Tauno Williams wrote:
>> I can't seem to get "onstat -g btc" to return anything in 9.21 FC4 or

>> 10.0 FC4.
>
> It doesn't do anything on my "10.00.UC4" box.
>
> There is "btree cleaner info", with "onstat -C". Is that the same
> thing?
>
Yes ...

onstat -C

Use the -C option to print the file information about the B-tree scanner
subsystem and each B-tree scanner thread. The following options are
available with the onstat -C command:

prof
Prints the profile information for the system and each B-tree
scanner thread hot
Prints the hot list index key in the order to be cleaned part
Prints all partitions with index statistics clean
Prints information about all the partitions that were cleaned or
need to be cleaned.
range
Prints the savings in pages processes by using index range scanning
all
Prints all onstat -C options
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list


_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

> BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000

Here is what this paramater means:

num= the number of btree scanner threads to start when the engine starts up

priority= low | high
low means run the btree scanner below a normal user thread and only
give the btree scanner extra CPU cycles
high means run the btree scanner thread equal to that of a normal users

threshold=
This is the number of times a user must encounter an index item which
has been marked for deletion, but has not been removed. Examples If you
delete 20,000 rows the delete will generate a count of 20,000 for this
index. If you then run a select that scans the index encountering 1/2
of the index items you just deleted then this will count as 10,000 for
this index. So each index item encountered when select, inserts,
updates, delete encounter an items marked for deletion in an index but
still consuming space will count as 1 hit on this index. When this
index exceed this count the index will be compressed and rebalanced.

rangesize=
Prior to 10.00.UC5 there was only two methods for cleaning an index,
leaf scanning (i.e. walking all leaf pages) and range scanning (i.e
walking a range of pages physically adjacent). The later is much, much
faster. Range scanning does a light scan on the index to read the
pages, avoiding saturating the buffer pool with unwanted index pages.
For small indexes they are already in the buffer pool so we will want to
user leaf scanning. This paramater determines the size cut off for
using the two different scanning methods.

If more detail on this topic is required I would be happy to do a talk
on this at the Information on Demand Conference in October, or a Chat
with the lab talk.

let me know.
John Miller


Hamilton, Jerry wrote:
>
> Yes I was interested in this too.
>
> I think a blue person came on here and said, "If you put this line in
> your onconfig, all your problems with the b-tree scanner will be
> forgotten". So I did and I forgot all about my b-tree scanner. But I
> still don't know what the heck it's doing.
>
> BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
>
>
>
> -----Original Message-----
> From: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org] On Behalf Of
> Darren_Jacobs@carmax.com
> Sent: Wednesday, June 07, 2006 1:58 PM
> To: TBP
> Cc: informix-list-bounces@iiug.org; informix-list@iiug.org
> Subject: Re: Checking indexes after large deletes
>
> I recall sometime back that someone was going to generate a paper on
> tuning the b-tree scanner. Anyone recall who it was? Jerry, weren't
> you interested in this too?
>
> Art, was it you by chance?
>
> peace
>
>
>
>
>
> TBP
>
> <TheBigPotato@Not
>
> Here.Co.Uk>
> To
> Sent by: informix-list@iiug.org
>
> informix-list-bou
> cc
> nces@iiug.org
>
>
> Subject
> Re: Checking indexes after large
>
> 06/07/2006 02:30 deletes
>
> PM
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Adam Tauno Williams wrote:
>
>>>I can't seem to get "onstat -g btc" to return anything in 9.21 FC4 or
>
>
>>>10.0 FC4.
>>
>>It doesn't do anything on my "10.00.UC4" box.
>>
>>There is "btree cleaner info", with "onstat -C". Is that the same
>>thing?
>>
>
> Yes ...
>
> onstat -C
>
> Use the -C option to print the file information about the B-tree scanner
> subsystem and each B-tree scanner thread. The following options are
> available with the onstat -C command:
>
> prof
> Prints the profile information for the system and each B-tree
> scanner thread hot
> Prints the hot list index key in the order to be cleaned part
> Prints all partitions with index statistics clean
> Prints information about all the partitions that were cleaned or
> need to be cleaned.
> range
> Prints the savings in pages processes by using index range scanning
> all
> Prints all onstat -C options
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

John,

This would be a great topic for a "Chat with the lab".

Thanks

Kernoal

John Miller wrote:
> > BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
>
> Here is what this paramater means:
>
> num= the number of btree scanner threads to start when the engine starts up
>
> priority= low | high
> low means run the btree scanner below a normal user thread and only
> give the btree scanner extra CPU cycles
> high means run the btree scanner thread equal to that of a normal users
>
> threshold=
> This is the number of times a user must encounter an index item which
> has been marked for deletion, but has not been removed. Examples If you
> delete 20,000 rows the delete will generate a count of 20,000 for this
> index. If you then run a select that scans the index encountering 1/2
> of the index items you just deleted then this will count as 10,000 for
> this index. So each index item encountered when select, inserts,
> updates, delete encounter an items marked for deletion in an index but
> still consuming space will count as 1 hit on this index. When this
> index exceed this count the index will be compressed and rebalanced.
>
> rangesize=
> Prior to 10.00.UC5 there was only two methods for cleaning an index,
> leaf scanning (i.e. walking all leaf pages) and range scanning (i.e
> walking a range of pages physically adjacent). The later is much, much
> faster. Range scanning does a light scan on the index to read the
> pages, avoiding saturating the buffer pool with unwanted index pages.
> For small indexes they are already in the buffer pool so we will want to
> user leaf scanning. This paramater determines the size cut off for
> using the two different scanning methods.
>
> If more detail on this topic is required I would be happy to do a talk
> on this at the Information on Demand Conference in October, or a Chat
> with the lab talk.
>
> let me know.
> John Miller
>
>
> Hamilton, Jerry wrote:
> >
> > Yes I was interested in this too.
> >
> > I think a blue person came on here and said, "If you put this line in
> > your onconfig, all your problems with the b-tree scanner will be
> > forgotten". So I did and I forgot all about my b-tree scanner. But I
> > still don't know what the heck it's doing.
> >
> > BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
> >
> >
> >
> > -----Original Message-----
> > From: informix-list-bounces@iiug.org
> > [mailto:informix-list-bounces@iiug.org] On Behalf Of
> > Darren_Jacobs@carmax.com
> > Sent: Wednesday, June 07, 2006 1:58 PM
> > To: TBP
> > Cc: informix-list-bounces@iiug.org; informix-list@iiug.org
> > Subject: Re: Checking indexes after large deletes
> >
> > I recall sometime back that someone was going to generate a paper on
> > tuning the b-tree scanner. Anyone recall who it was? Jerry, weren't
> > you interested in this too?
> >
> > Art, was it you by chance?
> >
> > peace
> >
> >
> >
> >
> >
> > TBP
> >
> > <TheBigPotato@Not
> >
> > Here.Co.Uk>
> > To
> > Sent by: informix-list@iiug.org
> >
> > informix-list-bou
> > cc
> > nces@iiug.org
> >
> >
> > Subject
> > Re: Checking indexes after large
> >
> > 06/07/2006 02:30 deletes
> >
> > PM
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Adam Tauno Williams wrote:
> >
> >>>I can't seem to get "onstat -g btc" to return anything in 9.21 FC4 or
> >
> >
> >>>10.0 FC4.
> >>
> >>It doesn't do anything on my "10.00.UC4" box.
> >>
> >>There is "btree cleaner info", with "onstat -C". Is that the same
> >>thing?
> >>
> >
> > Yes ...
> >
> > onstat -C
> >
> > Use the -C option to print the file information about the B-tree scanner
> > subsystem and each B-tree scanner thread. The following options are
> > available with the onstat -C command:
> >
> > prof
> > Prints the profile information for the system and each B-tree
> > scanner thread hot
> > Prints the hot list index key in the order to be cleaned part
> > Prints all partitions with index statistics clean
> > Prints information about all the partitions that were cleaned or
> > need to be cleaned.
> > range
> > Prints the savings in pages processes by using index range scanning
> > all
> > Prints all onstat -C options
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list
> >
> >
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list