rule of thumb or words of wisdom for btree cleaners?
NormaJean
Does anyone have a rule of thumb or words of wisdom regarding additonal
btree cleaners?
Looking at the output below it seems I could benefit from adding an
additional cleaner
Stats reset at 8:30 AM
HPUX 11
$ onstat -C
IBM Informix Dynamic Server Version 9.40.FC2W4 -- On-Line -- Up 3 days
14:50:17 -- 4460564 Kbytes
Btree Cleaner Info
BT scanner profile Information
==============================
Active Threads 1
Global Commands 0
Number of partition scans 23
Main Block 0xc0000000e0a97cc8
BTC Admin 0x0000000000000000
BTS info id Prio Partnum Key Cmd
0xc0000000e0eb58b0 0 Low 0x055007F4 1 100000 Scan index
Number of leaves pages scanned 10623920
Number of leaves with deleted items 122292
Time spent cleaning (sec) 22243
Number of index compresses 39214
Number of deleted items 4660843
Number of index range scans 0
Number of index leaf scans 259
Scan Type Leaf
Darren Jacobs/Carmax
Neil Truby
"NormaJean" <normajean...@tellabs.com> wrote in message
news:S8%Vd.6$Jo6....@news.uswest.net...
Michael Mueller
negative. But the most striking thing your numbers show is how much i/o
the btree cleaner wastes.
NormaJean wrote:
> All,
> Does anyone have a rule of thumb or words of wisdom regarding additonal
> btree cleaners?
> Looking at the output below it seems I could benefit from adding an
> additional cleaner
>
> Stats reset at 8:30 AM
> HPUX 11
> $ onstat -C
>
> IBM Informix Dynamic Server Version 9.40.FC2W4 -- On-Line -- Up 3 days
> 14:50:17 -- 4460564 Kbytes
>
> Btree Cleaner Info
> BT scanner profile Information
> ==============================
> Active Threads 1
> Global Commands 0
> Number of partition scans 23
> Main Block 0xc0000000e0a97cc8
> BTC Admin 0x0000000000000000
>
>
> BTS info id Prio Partnum Key Cmd
> 0xc0000000e0eb58b0 0 Low 0x055007F4 1 100000 Scan index
> Number of leaves pages scanned 10623920
> Number of leaves with deleted items 122292
This means that most of the btree cleaner i/o is wasted to visit pages
that don't need cleaning. That's the new btree scanner concept's big
weakness. If you add more cleaners it's possible that they produce even
more overhead.
You could increase the cleaning threshold or use range scans or run the
cleaner (or several of them) only at off hours to reduce the amount of
unnecessary i/o.
Warning: If you do mass deletes followed by mass reinserts of large
ranges of keys this strategy can lead to large insert performance
problems. In this case you have no choice and must do everything to
speed btree cleaning up. In this case you have to live with the large
additional i/o load.
Michael
Alexey Sonkin
Norma,
After a thorough investigation of how the BTSCANNER works,
I can suggest a rule of a thumb:
1. ALWAYS change btscanner from the default 'leaf scan'
to 'range scan' with a range scan threshold equal to
1-5 % of Informix cache, like:
onmode -C rangesize 10000
2. ALWAYS increase the 'number of dirty items' threshold
to something MUCH higher then the default CRAZY value of 500:
onmode -C threshold 50000
This threshold can be temporary changed to even higher value
during massive data deletes
3. If (1) and (2) is set properly, don't care about allocating
additional threads
-Alexey
> Time spent cleaning (sec) 22243
> Number of index compresses 39214
> Number of deleted items 4660843
> Number of index range scans 0
> Number of index leaf scans 259
> Scan Type Leaf
>
>
>
> Darren Jacobs/Carmax
>
sending to informix-list
Alexey Sonkin
Forgot to mention, the the same settings can be done
with the following $ONCONFIG parameter:
BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
-Alexey
sending to informix-list
Alexey Sonkin
Just put
BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
in Your $ONCONFIG - and Your desperation will go away
-Alexey
> -----Original Message-----
> From: owner-inf...@iiug.org
[mailto:owner-inf...@iiug.org] On
> Behalf Of Michael Mueller
>
> Perhaps I should call this "words of desperation". Sorry for being
> negative. But the most striking thing your numbers show is how much
i/o
> the btree cleaner wastes.
>
> NormaJean wrote:
> This means that most of the btree cleaner i/o is wasted to visit pages
> that don't need cleaning. That's the new btree scanner concept's big
> weakness. If you add more cleaners it's possible that they produce
even
> more overhead.
>
> You could increase the cleaning threshold or use range scans or run
the
> cleaner (or several of them) only at off hours to reduce the amount of
> unnecessary i/o.
>
> Warning: If you do mass deletes followed by mass reinserts of large
> ranges of keys this strategy can lead to large insert performance
> problems. In this case you have no choice and must do everything to
> speed btree cleaning up. In this case you have to live with the large
> additional i/o load.
>
> Michael
>
Michael Mueller
> Just put
>
> BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
>
> in Your $ONCONFIG - and Your desperation will go away
My desperation might turn into sadness, no more that that. In my opinion
the new btree scanner has at least two major design flaws (I'm not
saying that the old concept had none):
1) It has no idea which pages to clean and wastes a lot of i/o bandwidth
to find dirty pages by scanning whole indexes or at least whole physical
ranges of pages.
2) In some cases instant btree cleaning is a precondition for good
insert performance. The new concept cannot deliver that.
I would appreciate a discussion about this on this list.
Michael
Alexey Sonkin
There are two really big problems about the b-tree scanner:
1. It's mechanisms are not documented (Neil, thank You for your efforts
to compensate it)
2. The default configuration parameters are not suitable for any
real-world database: they cause infinite index page laundering and
replace any useful information in the Informix buffer cache with pages
of a single index currently being cleaned.
The fact is that, unlike old-style b-tree cleaner, b-tree scanner
MUST be properly configured - the effect of using the default
configuration parameters is comparable with leaving the 'BUFFERS' to
it's value from 'onconfig.std'
------------------------------------------
Alexey Sonkin
> From: owner-inf...@iiug.org
[mailto:owner-inf...@iiug.org]
> On Behalf Of Michael Mueller
>
sending to informix-list