Re: Informix Performance Issues

"Art S. Kagel" <kagel@bloomberg.net> wrote in message ... <snip> > Time since reset: 202.33 > ixda-RA: 38469248 > idx-RA: 109333718 > da-RA: 748405431 > RA-pgsused: 894562606 > > BR = (96935964 / (96935964 + 2242129221)) * 100.00 = 4.1400 > BTR = (((96935964 + 2242129221) / 300000) / 202.33) = 38.5354/hour > RAU = (894562606/(38469248+109333718+748405431)) * 100.00 = 99.8100 > <snip> > Your ReadAhead Utilization rate is 99.81% which is just fine! DO NOT MUCK > WITH THE RA values much. You may try 32 & 4 instead of 8 & 4 but DO NOT go > hog wild as TBP suggests. I think that will be disasterous for throughput. > Humm, well, proof would be in the pudding, but I think that is a "rash" statement to make without knowing more details about what the geezer is using this instance for. 99.8100 is a good number, but doesn't that infer that there is scope for improved usage if there is more read ahead? I think I stated to "do something stupid once in your life" (I have acheived that regularly) and set : 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. 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?

TheBigPotatoe wrote: > "Art S. Kagel" <kagel@bloomberg.net> wrote in message ... > <snip> > >>Time since reset: 202.33 >>ixda-RA: 38469248 >>idx-RA: 109333718 >>da-RA: 748405431 >>RA-pgsused: 894562606 >> >>BR = (96935964 / (96935964 + 2242129221)) * 100.00 = 4.1400 >>BTR = (((96935964 + 2242129221) / 300000) / 202.33) = 38.5354/hour >>RAU = (894562606/(38469248+109333718+748405431)) * 100.00 = 99.8100 >> > > <snip> > >>Your ReadAhead Utilization rate is 99.81% which is just fine! DO NOT MUCK >>WITH THE RA values much. You may try 32 & 4 instead of 8 & 4 but DO NOT go >>hog wild as TBP suggests. I think that will be disasterous for throughput. >> > > > Humm, well, proof would be in the pudding, but I think that is a > "rash" statement to make without knowing more details about what the > geezer is using this instance for. > 99.8100 is a good number, but doesn't that infer that there is scope > for improved usage if there is more read ahead? 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! > I think I stated to "do something stupid once in your life" (I have > acheived that regularly) and set : 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. > 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. 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, 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! 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. 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. > 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