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

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-informix-list@iiug.org [mailto:owner-informix-list@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: > > 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 3days > > 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 > > > > sending to informix-list