Re: Informix Performance Issues

Anirudh Chitnis via DBMonster.com wrote: > > I am using 9.30 on HP-UX 11i. The select queries take quite a long tpo > return. The queries are taking index paths and statistics are also updated. > When I tried to look in to the I/O activity, I could find that kio is > having maxlen around 16-17. All other things do not have any thing in > maxlen. I am attaching the first few lines of onstat -g ioq and onstat -g > iov outputs and later the onconfig. > RAM on m/c: 8 GB > CPUs : 4 > > Could someone please help me nail down the root cause ? OK, here are the metrics I use to start looking at things: > calc_ratios Pagreads: 2242129221 Bufwrits: 96935964 Bufwaits: 96935964 BUFFERS: 300000 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 Your Bufwaits Ratio (BR) is fine at 4 so your LRUS settings are OK (however, you have LRUS set to 256 and the max value is 128! What does onstat -R report as the number of buffer LRU queue pairs? 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. Your Buffer Turnover Rate of 38.5354/hour is way too high and indicates that you could likely increase your BUFFERS several times to at least 600000 but maybe as high as 1500000. The lowish read cache percentage supports the same conclusion. Start slow by doubling it and see how that goes. Monitor onstat -P to see if there might be a small number of tables which aretrading off thrashing each other out of the buffer pool. Your NUMAIOVPS is too high for a server that uses KAIO. Reset to 6 and monitor onstat -g iov. If there are any aio vps with io/wup that are 0.0 you can drop that many more. Normally a KAIO server uses 4-8 aio vps depending on how much it writes to the message log, console log, etc. Your KAIO queue lengths are not terrible at 17 but could be better. Can you reconfigure the disk farm into a quicker service? Perhaps by trading RAID5 in for RAID10, single spindles in for an array, or a single RAID10 array in for multiple ones or for a PLAID of several arrays? Change RESIDENT to -1 that will load all possible current and future shared segments as locked and will try to compress the resident segment with the first virtual segment to reduce overhead. Set CLEANERS >= to LRUS, I would set both to 128. This minimizes the time spent flushing the LRU queues under peak load. <Details snipped> Art S. Kagel