Re: rule of thumb or words of wisdom for btree cleaners?

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: > 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 > 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 > >