RE: Checking indexes after large deletes

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, Don't forget Lester's call for presentation for the DC User's Forum! Chat w/Lab sounds great too. peace John Miller <jmiller@jfmiii.c om> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: Checking indexes after large 06/09/2006 03:09 deletes AM > 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 _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list