Re: Informix Performance Issues

Art S. Kagel wrote: > TheBigPotatoe wrote: > >> "Art S. Kagel" <kagel@bloomberg.net> wrote in message ... >> <snip> > > Let me say this for everyone and for all time. Excessive readahead is > the single most pervasive cause of poor performance and requiring > excessive caching than any other cause. Perhaps than all other causes > combined! > Yep :), or perhaps leaving BUFFERS at 200? >> I think I stated to "do something stupid once in your life" (I have >> acheived that regularly) and set : > Yep :) > > Read the manuals. Any single query that's actually performing > sequential reads of 1088 pages in a row will be using light scans which > neither use the RA_ parameters nor the buffer pool! Besides, unless the > disk farm serving this instance is JBOD and those disks and controllers > are very old, the drives are all performing their own readahead to their > own cache as is the SCSI controller (noone's running Informix on an ATA > controller right? <smirk>) itself to it's own cache. > Humm, read the manuals, and they don't say "Sequential reads of 1088 pages in a row will cause light scans" :P Rumour has it that the light scan buffer size is based on RA_PAGES. Loads of intel users use ATA disks. >> RA_PAGES 1024 RA_THRESHOLD 64>> >> which is pretty stupid UNLESS, I am the only person on this system and >> I want to fly through a huge table, and then it is pretty good. > Pretty stupid / hog wild :) > > IF you are the only person using the server and you are flying through a > huge table with a hughe sequential scan then A) the scan is likely using > light scan buffers not the buffer pool, IF I am in dirty read and the table is larger than buffers. > B) if the query does not lend > itself to light scanning then the data either is not yet in the buffer > pool in which case it has to be read in anyway. If the larger RA can > keep up with a single user screaming through the data then so can the > normal levels of RA. If it cannot then the disk farm is not fast enough > and it does not matter! "Buy faster disks" -> yep :) > > A single user is not the one who is helped by RA and not the one who is > hurt. RA helps during multi-user operations when IOs can be > prescheduled by RA while the user's threads are suspended to allow other > users to run. > Humm, okay. > The down side of this is that all that RA will thrash the buffer pool > and force other needed pages out of the pool. With RA set to 1024/64 > even a small sequential read of 4 pages will trigger a 1024 page read > which will flush 1020 pages that noone is interested in and noone will > every reuse while forcing another 1020 pages of perhaps recently > accessed data that may be needed again soon to be overwritten. > "Do something stupid" and, yep, 1024 / 64 is pretty stupid! But if it isn't tried then how do you know? (I guess we are not doing light scans now :O)) >> Looking at a compromise (and we do not know what this geezer has), >> let's say he has 8 big report type users, and 32 little OLTP type >> users, then : >> >> RA_PAGES 128 >> RA_THRESHOLD 32>> >> would be okey dokey. >> >> BUT ... proof is in the pudding, not in the decimal places :) >> >> So, what did the geezer do? > > > I can only speak from my own experience which is with over 50 instances > serving over MANY 10s of thousands of users with many terabytes of data > 24x7x365 here on our systems and on the hundreds of sites around the > world that have asked for help over the years. The overwhelming > conclusion is that when it comes to RA and performance - less is more. > > Art S. Kagel I sort of completely agree with you, and I sort of don't. BUT without knowing more about what this geezer is doing (and the user base), I think it a bit "extreme" to state 128 / 32 is completely wrong. Also, OTC does have a valid point about bad indexes - missing indexes will cause buffer flooding as well, which affects everyone :O. Allright, 32 / 4 is nice :) - depends on 4k or 2k platform, and also what is read from disk in one go - ooops :O Can you flame potatoes? Probably :) -- "I have enough problems remembering my opinions, let alone justifying them." "Sorry, I thought you tapped me."

TBP wrote: > Art S. Kagel wrote: > >> TheBigPotatoe wrote: >> >>> "Art S. Kagel" <kagel@bloomberg.net> wrote in message ... >>> <snip> <more scissor work> > > I sort of completely agree with you, and I sort of don't. BUT without > knowing more about what this geezer is doing (and the user base), I > think it a bit "extreme" to state 128 / 32 is completely wrong. OK. > Also, OTC does have a valid point about bad indexes - missing indexes > will cause buffer flooding as well, which affects everyone :O. Definitely. But I normally ignore OTC, why change now? > Allright, 32 / 4 is nice :) - depends on 4k or 2k platform, and also > what is read from disk in one go - ooops :O Ya. > Can you flame potatoes? Probably :) I LOVE roasted potatoes! Art S. Kagel