Formula for figuring out how many BTSCANNERS I nee

Was looking at my btscanners - IDS 11.5FC3 on Redhat linux. I see that it took 1379 seconds in cleaning and looks to be building a hot list is taking a while - As it is building now and the last one was built at 12:24 and it is 40 min later and still running. QUESTIONS: 1)Is the 1379 seconds (i.e. 22 min) the time it spent cleaning the time time a clean started? 2)If I created more BTSCANNERS - would the clean happen faster? 3)If I increased the BTSCANNERS - would the build of the hot list happen faster? Anybody have any recommendations on tuning the BTSCANNERS This is currently BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default I am running on a dual quad core box so 8 processors and I am debating increasing my BTSCANNERS to 4. Suggestions or Recommendations would be appreciated. Thanks in advance, Debbie Butler B-tree Scanner Information Profile ======= Active Threads 1 Global Commands 2000000 Building hot list Number of partition scans 5075 Main Block 0x000000006457ffd0 BTC Admin 0x0000000061e26388 BTS info id Prio Partnum Key Cmd 0x649c9a68 0 High 0x00000000 0 40 Yield N Number of leaves pages scanned 5029620 Number of leaves with deleted items 379457 Time spent cleaning (sec) 1379 Number of index compresses 44191 Number of deleted items 5170698 Number of index range scans 0 Number of index leaf scans 0 Number of index alice scans 1137 B-tree Scanner Information Index Hot List ============== Current Item 2 List Created 12:24:01 List Size 1 List expires in 0 sec Hit Threshold 5000 Range Scan Threshold -1 Partnum Key Hits 0x00A00008 1 5266 *

If you look at the IIUG site there is a presentation by Mark Jameson that goes into this very well Cheers Paul Paul Watson Tel: +1 913-745-5635 Mob: +1 913-387-7529 Web: www.oninit.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. It is better to be criticized by a wise person than to be praised by a fool. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Butler, Debbie Sent: Monday, June 01, 2009 12:31 PM To: ids@iiug.org Subject: Formula for figuring out how many BTSCANNERS I.... [15868] Was looking at my btscanners - IDS 11.5FC3 on Redhat linux. I see that it took 1379 seconds in cleaning and looks to be building a hot list is taking a while - As it is building now and the last one was built at 12:24 and it is 40 min later and still running. QUESTIONS: 1)Is the 1379 seconds (i.e. 22 min) the time it spent cleaning the time time a clean started? 2)If I created more BTSCANNERS - would the clean happen faster? 3)If I increased the BTSCANNERS - would the build of the hot list happen faster? Anybody have any recommendations on tuning the BTSCANNERS This is currently BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default I am running on a dual quad core box so 8 processors and I am debating increasing my BTSCANNERS to 4. Suggestions or Recommendations would be appreciated. Thanks in advance, Debbie Butler B-tree Scanner Information Profile ======= Active Threads 1 Global Commands 2000000 Building hot list Number of partition scans 5075 Main Block 0x000000006457ffd0 BTC Admin 0x0000000061e26388 BTS info id Prio Partnum Key Cmd 0x649c9a68 0 High 0x00000000 0 40 Yield N Number of leaves pages scanned 5029620 Number of leaves with deleted items 379457 Time spent cleaning (sec) 1379 Number of index compresses 44191 Number of deleted items 5170698 Number of index range scans 0 Number of index leaf scans 0 Number of index alice scans 1137 B-tree Scanner Information Index Hot List ============== Current Item 2 List Created 12:24:01 List Size 1 List expires in 0 sec Hit Threshold 5000 Range Scan Threshold -1 Partnum Key Hits 0x00A00008 1 5266 * **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Are you talking about this one. I had already searched there but the link to this document is not found. Do you have a copy? D14_Jamison_ALICE_and_Other_BTSCANNER_Tips.pdf On 6/1/09 1:39 PM, "Paul Watson" <paul@oninit.com> wrote: > If you look at the IIUG site there is a presentation by Mark Jameson that > goes into this very well > > Cheers > Paul > > Paul Watson > Tel: +1 913-745-5635 > Mob: +1 913-387-7529 > Web: www.oninit.com > > Failure is not as frightening as regret. > If you want to improve, be content to be thought foolish and stupid. > It is better to be criticized by a wise person than to be praised by a fool. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Butler, Debbie > Sent: Monday, June 01, 2009 12:31 PM > To: ids@iiug.org > Subject: Formula for figuring out how many BTSCANNERS I.... [15868] > > Was looking at my btscanners - IDS 11.5FC3 on Redhat linux. > > I see that it took 1379 seconds in cleaning and looks to be building a hot > list is taking a while - As it is building now and the last one was built at > > 12:24 and it is 40 min later and still running. > > QUESTIONS: > 1)Is the 1379 seconds (i.e. 22 min) the time it spent cleaning the time time > > a clean started? > > 2)If I created more BTSCANNERS - would the clean happen faster? > > 3)If I increased the BTSCANNERS - would the build of the hot list happen > faster? > > Anybody have any recommendations on tuning the BTSCANNERS > > This is currently > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > > I am running on a dual quad core box so 8 processors and I am debating > increasing my BTSCANNERS to 4. > > Suggestions or Recommendations would be appreciated. > > Thanks in advance, > > Debbie Butler > > B-tree Scanner Information > Profile > ======= > Active Threads 1 > Global Commands 2000000 Building hot list > Number of partition scans 5075 > Main Block 0x000000006457ffd0 > BTC Admin 0x0000000061e26388 > > BTS info id Prio Partnum Key Cmd > 0x649c9a68 0 High 0x00000000 0 40 Yield N > > Number of leaves pages scanned 5029620 > > Number of leaves with deleted items 379457 > > Time spent cleaning (sec) 1379 > > Number of index compresses 44191 > > Number of deleted items 5170698 > > Number of index range scans 0 > > Number of index leaf scans 0 > > Number of index alice scans 1137 > > B-tree Scanner Information > > Index Hot List > ============== > > Current Item 2 List Created 12:24:01 > > List Size 1 List expires in 0 sec > > Hit Threshold 5000 Range Scan Threshold -1 > > Partnum Key Hits > 0x00A00008 1 5266 * > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************> * > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Try http://presentations.oninit.com/public/2008IIUG/D14_Jamison_ALICE_and_Other_ BTSCANNER_Tips.pdf Cheers Paul Paul Watson Tel: +1 913-745-5635 Mob: +1 913-387-7529 Web: www.oninit.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. It is better to be criticized by a wise person than to be praised by a fool. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Butler, Debbie Sent: Monday, June 01, 2009 1:10 PM To: ids@iiug.org Subject: Re: Formula for figuring out how many BTSCANNE.... [15871] Are you talking about this one. I had already searched there but the link to this document is not found. Do you have a copy? D14_Jamison_ALICE_and_Other_BTSCANNER_Tips.pdf On 6/1/09 1:39 PM, "Paul Watson" <paul@oninit.com> wrote: > If you look at the IIUG site there is a presentation by Mark Jameson that > goes into this very well > > Cheers > Paul > > Paul Watson > Tel: +1 913-745-5635 > Mob: +1 913-387-7529 > Web: www.oninit.com > > Failure is not as frightening as regret. > If you want to improve, be content to be thought foolish and stupid. > It is better to be criticized by a wise person than to be praised by a fool. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Butler, Debbie > Sent: Monday, June 01, 2009 12:31 PM > To: ids@iiug.org > Subject: Formula for figuring out how many BTSCANNERS I.... [15868] > > Was looking at my btscanners - IDS 11.5FC3 on Redhat linux. > > I see that it took 1379 seconds in cleaning and looks to be building a hot > list is taking a while - As it is building now and the last one was built at > > 12:24 and it is 40 min later and still running. > > QUESTIONS: > 1)Is the 1379 seconds (i.e. 22 min) the time it spent cleaning the time time > > a clean started? > > 2)If I created more BTSCANNERS - would the clean happen faster? > > 3)If I increased the BTSCANNERS - would the build of the hot list happen > faster? > > Anybody have any recommendations on tuning the BTSCANNERS > > This is currently > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > > I am running on a dual quad core box so 8 processors and I am debating > increasing my BTSCANNERS to 4. > > Suggestions or Recommendations would be appreciated. > > Thanks in advance, > > Debbie Butler > > B-tree Scanner Information > Profile > ======= > Active Threads 1 > Global Commands 2000000 Building hot list > Number of partition scans 5075 > Main Block 0x000000006457ffd0 > BTC Admin 0x0000000061e26388 > > BTS info id Prio Partnum Key Cmd > 0x649c9a68 0 High 0x00000000 0 40 Yield N > > Number of leaves pages scanned 5029620 > > Number of leaves with deleted items 379457 > > Time spent cleaning (sec) 1379 > > Number of index compresses 44191 > > Number of deleted items 5170698 > > Number of index range scans 0 > > Number of index leaf scans 0 > > Number of index alice scans 1137 > > B-tree Scanner Information > > Index Hot List > ============== > > Current Item 2 List Created 12:24:01 > > List Size 1 List expires in 0 sec > > Hit Threshold 5000 Range Scan Threshold -1 > > Partnum Key Hits > 0x00A00008 1 5266 * > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > **************************************************************************** **> * > Forum Note: Use "Reply" to post a response in the discussion forum. > > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Debbie: When the btscanner has no work to do its idle state will be waiting on building the hot list. To build the hot list it looks at all tables in the "onstat -C clean" to see if any tables have deleted items ( column called "Dirty Hits") above the Hit Threshold. If it finds no tables above the Hit Threshold, the thread will go to sleep for a couple of minutes and try again. Here is what I would suggest. 1 Look at an "onstat -C hot". 2. The list at the bottom has three columns. If the items are followed by an asterisk then the index has been cleaned. If the asterisk is not present then it still has to clean this index.. 3. If you have several indexes on this list without asterisk that stay for a period you feel is to long then adding an btree scanner will help speed the cleaning. EXAMPLE showing asterisk following. all index have been cleaned. =================================================== B-tree Scanner Information Index Hot List ============== Current Item 4 List Created 12:33:42 List Size 3 List expires in 0 sec Hit Threshold 5000 Range Scan Threshold -1 Partnum Key Hits 0x00100002 1 4089 * 0x001000B5 1 3941 * 0x001000A5 1 3049 * My last bit of advice is 1) if you have upgraded from 7 2) you have more than 1 attached index 3) Important or hot table then drop and re-create the index as semi-detached or detached indexes. This will allow you to utilize alice cleaning and it will be significantly faster. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 06/01/2009 10:31:14 AM: > [image removed] > > Formula for figuring out how many BTSCANNERS I.... [15868] > > Butler, Debbie > > to: > > ids > > 06/01/2009 10:33 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Was looking at my btscanners - IDS 11.5FC3 on Redhat linux. > > I see that it took 1379 seconds in cleaning and looks to be building a hot > list is taking a while - As it is building now and the last one was built at > 12:24 and it is 40 min later and still running. > > QUESTIONS: > 1)Is the 1379 seconds (i.e. 22 min) the time it spent cleaning the time time > a clean started? > > 2)If I created more BTSCANNERS - would the clean happen faster? > > 3)If I increased the BTSCANNERS - would the build of the hot list happen > faster? > > Anybody have any recommendations on tuning the BTSCANNERS > > This is currently > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > > I am running on a dual quad core box so 8 processors and I am debating > increasing my BTSCANNERS to 4. > > Suggestions or Recommendations would be appreciated. > > Thanks in advance, > > Debbie Butler > > B-tree Scanner Information > Profile > ======= > Active Threads 1 > Global Commands 2000000 Building hot list > Number of partition scans 5075 > Main Block 0x000000006457ffd0 > BTC Admin 0x0000000061e26388 > > BTS info id Prio Partnum Key Cmd > 0x649c9a68 0 High 0x00000000 0 40 Yield N > > Number of leaves pages scanned 5029620 > > Number of leaves with deleted items 379457 > > Time spent cleaning (sec) 1379 > > Number of index compresses 44191 > > Number of deleted items 5170698 > > Number of index range scans 0 > > Number of index leaf scans 0 > > Number of index alice scans 1137 > > B-tree Scanner Information > > Index Hot List > ============== > > Current Item 2 List Created 12:24:01 > > List Size 1 List expires in 0 sec > > Hit Threshold 5000 Range Scan Threshold -1 > > Partnum Key Hits > 0x00A00008 1 5266 * > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Something else JMIII wrote can be seen http://www.oninit.com/reference/index.php?page=btscanner.html Sorry I forgot about it earlier Cheers Paul Paul Watson Tel: +1 913-745-5635 Mob: +1 913-387-7529 Web: www.oninit.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. It is better to be criticized by a wise person than to be praised by a fool. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Miller iii Sent: Monday, June 01, 2009 3:18 PM To: ids@iiug.org Subject: Re: Formula for figuring out how many BTSCANNE.... [15873] Debbie: When the btscanner has no work to do its idle state will be waiting on building the hot list. To build the hot list it looks at all tables in the "onstat -C clean" to see if any tables have deleted items ( column called "Dirty Hits") above the Hit Threshold. If it finds no tables above the Hit Threshold, the thread will go to sleep for a couple of minutes and try again. Here is what I would suggest. 1 Look at an "onstat -C hot". 2. The list at the bottom has three columns. If the items are followed by an asterisk then the index has been cleaned. If the asterisk is not present then it still has to clean this index.. 3. If you have several indexes on this list without asterisk that stay for a period you feel is to long then adding an btree scanner will help speed the cleaning. EXAMPLE showing asterisk following. all index have been cleaned. =================================================== B-tree Scanner Information Index Hot List ============== Current Item 4 List Created 12:33:42 List Size 3 List expires in 0 sec Hit Threshold 5000 Range Scan Threshold -1 Partnum Key Hits 0x00100002 1 4089 * 0x001000B5 1 3941 * 0x001000A5 1 3049 * My last bit of advice is 1) if you have upgraded from 7 2) you have more than 1 attached index 3) Important or hot table then drop and re-create the index as semi-detached or detached indexes. This will allow you to utilize alice cleaning and it will be significantly faster. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 06/01/2009 10:31:14 AM: > [image removed] > > Formula for figuring out how many BTSCANNERS I.... [15868] > > Butler, Debbie > > to: > > ids > > 06/01/2009 10:33 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Was looking at my btscanners - IDS 11.5FC3 on Redhat linux. > > I see that it took 1379 seconds in cleaning and looks to be building a hot > list is taking a while - As it is building now and the last one was built at > 12:24 and it is 40 min later and still running. > > QUESTIONS: > 1)Is the 1379 seconds (i.e. 22 min) the time it spent cleaning the time time > a clean started? > > 2)If I created more BTSCANNERS - would the clean happen faster? > > 3)If I increased the BTSCANNERS - would the build of the hot list happen > faster? > > Anybody have any recommendations on tuning the BTSCANNERS > > This is currently > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > > I am running on a dual quad core box so 8 processors and I am debating > increasing my BTSCANNERS to 4. > > Suggestions or Recommendations would be appreciated. > > Thanks in advance, > > Debbie Butler > > B-tree Scanner Information > Profile > ======= > Active Threads 1 > Global Commands 2000000 Building hot list > Number of partition scans 5075 > Main Block 0x000000006457ffd0 > BTC Admin 0x0000000061e26388 > > BTS info id Prio Partnum Key Cmd > 0x649c9a68 0 High 0x00000000 0 40 Yield N > > Number of leaves pages scanned 5029620 > > Number of leaves with deleted items 379457 > > Time spent cleaning (sec) 1379 > > Number of index compresses 44191 > > Number of deleted items 5170698 > > Number of index range scans 0 > > Number of index leaf scans 0 > > Number of index alice scans 1137 > > B-tree Scanner Information > > Index Hot List > ============== > > Current Item 2 List Created 12:24:01 > > List Size 1 List expires in 0 sec > > Hit Threshold 5000 Range Scan Threshold -1 > > Partnum Key Hits > 0x00A00008 1 5266 * > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.