All 8 messages in topic - view as tree
From: Andrew Ford - view profile
Date: Fri, Dec 1 2006 6:53 pm
Email: "Andrew Ford"
We are upgrading from 9.3 to 10.0 and have found the BTSCANNER defaults to be a must change. Based on the recommendations in a post from Alexey Sonkin in March 2005 we've changed our production parameters and have seen a significant improvement.
Out of the box our 10.0 btree scanner was running at high priority and performing a leaf scan our large indexes way too often. This was causing high checkpoints and causing our cache hit % to drop severely since we were reading every index page into our buffers.
Most of you know this already but there may be some of us out there still running with the defaults (especially since the BTSCANNER onconfig parameter is not included in the 10.0 onconfig.std file).
Out of the box values
priority: high
threshold: 500
rangesize: -1 # leaf scan all indexes, i.e read all index pages into the buffer pool and kill your read cache
Our new values:
priority: low
threshold: 50000
rangesize: 10000 # range scan indexes with >= 10000 pages, 4% of our buffers
These values can be changed on the fly via the onmode -C commands and can be made permanent via the BTSCANNER onconfig parameter.
http://www.informix-support.co.uk/btscanner.htm has some awesome info on the btree scanner.
What is an alice scan?
Andrew
Reply » Rate this post: Text for clearing space
From: alexxd...@gmail.com - view profile
Date: Mon, Dec 4 2006 4:13 pm
Email: alexxd...@gmail.com
Our default (out of the box) priority value of btscanner (checked from
onstat -C) is low, IDS10.00.UC4 and UC5
Reply » Rate this post: Text for clearing space
From: Andrew Ford - view profile
Date: Tues, Dec 5 2006 4:54 pm
Email: "Andrew Ford"
I was expecting to see the btscanner thread running at low priority as the
default but this was not the case. Maybe it has something to do with
upgrading from 9.3 to 10.0 vs installing 10.0 clean. I can say with 100%
certainty that onstat -C showed the btscanner thread running with a priority
of high after the upgrade until I forced a change with onmode -C low.
Andrew
- Hide quoted text -
- Show quoted text -
----- Original Message -----
From:
Newsgroups: comp.databases.informix
To:
Sent: Monday, December 04, 2006 8:13 AM
Subject: Re: BTSCANNER defaults
> Our default (out of the box) priority value of btscanner (checked from
> onstat -C) is low, IDS10.00.UC4 and UC5
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: Mark Jamison - view profile
Date: Tues, Dec 5 2006 5:58 pm
Email: Mark Jamison
Sorry for the repeat I forgot to include the whole list
Inlater versions of 10, 10.00.FC5 and greater I believe, the Default is still LOW, but now it will actually switch to High to run any critical sections, like compresses. It will reset back to LOW when doing scans, or while at rest, but if the threads are doing a lot of compresses, or a lot of cleaning, then the threads will appear to be HIGH all the time.
----- Original Message ----
>What is an alice scan?
ALICE stands for (A)daptive (L)inear (I)ndex (C)l(E)aning.
It's new functionality that begins in 10.00.xC5. It is disabled by default.
You can turn it on by including the value
alice=<1-9> at the end of your BTSCANNER entry in onconfig.
You can look at the results of the work ALICE does by using the following commands
onstat -C alice
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: Andrew Ford - view profile
Date: Tues, Dec 5 2006 6:05 pm
Email: "Andrew Ford"
10.0.UC5 here.
Thanks for the info, what you say makes sense. The threads were very active
and probably dynamically changed themselves to run at a high priority.
We are upgrading our last 9.3 instances to 10.0 tonight and I am explicitly
setting the BTSCANNER onconfig parameter to low priority. I will see if it
changes to high on the fly.
Do you know of any guidelines as to when an ALICE scan would be preferred
over a leaf or range scan?
Andrew
- Hide quoted text -
- Show quoted text -
----- Original Message -----
From: Mark Jamison
To: Andrew Ford ; informix-l...@iiug.org
Sent: Tuesday, December 05, 2006 9:58 AM
Subject: Re: BTSCANNER defaults
Sorry for the repeat I forgot to include the whole list
Inlater versions of 10, 10.00.FC5 and greater I believe, the Default is
still LOW, but now it will actually switch to High to run any critical
sections, like compresses. It will reset back to LOW when doing scans, or
while at rest, but if the threads are doing a lot of compresses, or a lot of
cleaning, then the threads will appear to be HIGH all the time.
----- Original Message ----
>What is an alice scan?
ALICE stands for (A)daptive (L)inear (I)ndex (C)l(E)aning.
It's new functionality that begins in 10.00.xC5. It is disabled by default.
You can turn it on by including the value
alice=<1-9> at the end of your BTSCANNER entry in onconfig.
You can look at the results of the work ALICE does by using the following
commands
onstat -C alice
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
From: Mark Jamison - view profile
Date: Tues, Dec 5 2006 9:00 pm
Email: Mark Jamison
>Do you know of any guidelines as to when an ALICE scan would be preferred
>over a leaf or range scan?
Sure. ALICE will be preferred over everything. It is more efficient that leaf or range scans. It is new functionality though, so there may be bumps along the way.
Honestly I would be suggest starting ALICE at 3, and then use onstat -C alice to see how efficiently the BTSCANNER threads are working.
Here is an example of an onstat -C alice from a test system I have
(informix) /usr/informix > onstat -C alice
IBM Informix Dynamic Server Version 10.00.FC5W5 -- On-Line -- Up 1 days 03:40:24 -- 245148 Kbytes
Btree Cleaner Info
ALICE Cleaning Statistics
=========================
System wide ALICE mode 4
Partnum Mode BM_Sz Used_Pg Examined Dirty_Pg # I/O Found Eff
0x0010001b 4 64 15 0 0 0 0 0.0 %
0x00400008 4 64 9 0 0 0 0 0.0 %
0x00400008 4 64 9 0 0 0 0 0.0 %
0x00400009 4 64 18 0 0 0 0 0.0 %
0x00400009 4 64 18 0 0 0 0 0.0 %
0x0040000a 4 64 25 0 0 0 0 0.0 %
0x0040000a 4 64 25 0 0 0 0 0.0 %
0x0040000b 4 64 11 0 0 0 0 0.0 %
0x0040000b 4 64 11 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400016 4 64 4 0 0 0 0 0.0 %
0x00400016 4 64 4 0 0 0 0 0.0 %
0x0040001f 4 64 15 15 2 2 1 50.0 %
0x00400020 4 64 3 0 0 0 0 0.0 %
0x00400034 4 64 4 0 0 0 0 0.0 %
0x00400034 4 64 4 0 0 0 0 0.0 %
0x00400043 4 64 80 0 0 0 0 0.0 %
0x00400045 4 64 71 71 19 17 6 35.3 %
I've set ALICE to 4 on this particular box.
Reply » Rate this post: Text for clearing space
From: Andrew Ford - view profile
Date: Tues, Dec 5 2006 9:10 pm
Email: "Andrew Ford"
Excellent. Thanks for the info Mark.
Do you know where I can find the documentation for this new feature? A search of the 10.0 Informix Center doesn't produce anything interesting.
Andrew
- Hide quoted text -
- Show quoted text -
----- Original Message -----
From: Mark Jamison
To: Andrew Ford ; informix-l...@iiug.org
Sent: Tuesday, December 05, 2006 1:00 PM
Subject: Re: BTSCANNER defaults
>Do you know of any guidelines as to when an ALICE scan would be preferred
>over a leaf or range scan?
Sure. ALICE will be preferred over everything. It is more efficient that leaf or range scans. It is new functionality though, so there may be bumps along the way.
Honestly I would be suggest starting ALICE at 3, and then use onstat -C alice to see how efficiently the BTSCANNER threads are working.
Here is an example of an onstat -C alice from a test system I have
(informix) /usr/informix > onstat -C alice
IBM Informix Dynamic Server Version 10.00.FC5W5 -- On-Line -- Up 1 days 03:40:24 -- 245148 Kbytes
Btree Cleaner Info
ALICE Cleaning Statistics
=========================
System wide ALICE mode 4
Partnum Mode BM_Sz Used_Pg Examined Dirty_Pg # I/O Found Eff
0x0010001b 4 64 15 0 0 0 0 0.0 %
0x00400008 4 64 9 0 0 0 0 0.0 %
0x00400008 4 64 9 0 0 0 0 0.0 %
0x00400009 4 64 18 0 0 0 0 0.0 %
0x00400009 4 64 18 0 0 0 0 0.0 %
0x0040000a 4 64 25 0 0 0 0 0.0 %
0x0040000a 4 64 25 0 0 0 0 0.0 %
0x0040000b 4 64 11 0 0 0 0 0.0 %
0x0040000b 4 64 11 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400016 4 64 4 0 0 0 0 0.0 %
0x00400016 4 64 4 0 0 0 0 0.0 %
0x0040001f 4 64 15 15 2 2 1 50.0 %
0x00400020 4 64 3 0 0 0 0 0.0 %
0x00400034 4 64 4 0 0 0 0 0.0 %
0x00400034 4 64 4 0 0 0 0 0.0 %
0x00400043 4 64 80 0 0 0 0 0.0 %
0x00400045 4 64 71 71 19 17 6 35.3 %
I've set ALICE to 4 on this particular box.
Reply » Rate this post: Text for clearing space
From: Mark Jamison - view profile
Date: Tues, Dec 5 2006 9:53 pm
Email: Mark Jamison
Alas, at the moment, since we are between version 10 and cheetah, no clear documentation exists. It should be there in Cheetah documentation, but until then, documentation is scarce.
- Hide quoted text -
- Show quoted text -
----- Original Message ----
From: Andrew Ford
To: Mark Jamison
Sent: Tuesday, December 5, 2006 1:10:15 PM
Subject: Re: BTSCANNER defaults
Excellent. Thanks for the info Mark.
Do you know where I can find the documentation for this new feature? A search of the 10.0 Informix Center doesn't produce anything interesting.
Andrew
----- Original Message -----
From: Mark Jamison
To: Andrew Ford ; informix-l...@iiug.org
Sent: Tuesday, December 05, 2006 1:00 PM
Subject: Re: BTSCANNER defaults
>Do you know of any guidelines as to when an ALICE scan would be preferred
>over a leaf or range scan?
Sure. ALICE will be preferred over everything. It is more efficient that leaf or range scans. It is new functionality though, so there may be bumps along the way.
Honestly I would be suggest starting ALICE at 3, and then use onstat -C alice to see how efficiently the BTSCANNER threads are working.
Here is an example of an onstat -C alice from a test system I have
(informix) /usr/informix > onstat -C alice
IBM Informix Dynamic Server Version 10.00.FC5W5 -- On-Line -- Up 1 days 03:40:24 -- 245148 Kbytes
Btree Cleaner Info
ALICE Cleaning Statistics
=========================
System wide ALICE mode 4
Partnum Mode BM_Sz Used_Pg Examined Dirty_Pg # I/O Found Eff
0x0010001b 4 64 15 0 0 0 0 0.0 %
0x00400008 4 64 9 0 0 0 0 0.0 %
0x00400008 4 64 9 0 0 0 0 0.0 %
0x00400009 4 64 18 0 0 0 0 0.0 %
0x00400009 4 64 18 0 0 0 0 0.0 %
0x0040000a 4 64 25 0 0 0 0 0.0 %
0x0040000a 4 64 25 0 0 0 0 0.0 %
0x0040000b 4 64 11 0 0 0 0 0.0 %
0x0040000b 4 64 11 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400012 4 64 5 0 0 0 0 0.0 %
0x00400016 4 64 4 0 0 0 0 0.0 %
0x00400016 4 64 4 0 0 0 0 0.0 %
0x0040001f 4 64 15 15 2 2 1 50.0 %
0x00400020 4 64 3 0 0 0 0 0.0 %
0x00400034 4 64 4 0 0 0 0 0.0 %
0x00400034 4 64 4 0 0 0 0 0.0 %
0x00400043 4 64 80 0 0 0 0 0.0 %
0x00400045 4 64 71 71 19 17 6 35.3 %
I've set ALICE to 4 on this particular box.
Reply » Rate this post:
End of messages
No comments:
Post a Comment