Parameters LRU_MAX_DIRTY & LRU_MIN_DIRTY - UNIX

Our Informix Dynamic Server 7.22 running on Sun Solaris comes to a crawl when usage reaches high levels. However, I've noticed that "SELECTS" continue to be quick while UPDATES and INSERTS can take from 20 to 60 seconds each. I noticed that our DBA has the LRU_MAX_DIRTY parameter set to 2% and LRU_MIN_DIRTY set to 1% while Informix recommends defaults as high as 60% and 50% respectively. The DBA insists this is not our problem and refuses to even change these values a little. Has anyone had experience tuning these values? Am I correct to believe that these values are forcing such a high rate of flushing dirty buffers to disk that this unnecessary I/O is impacting UPDATES and INSERTS which create dirty pages? Thanks for any help in this regard, Jack O'Hara johara@writeme.com

John (Jack) O'Hara wrote: > > Our Informix Dynamic Server 7.22 running on Sun Solaris comes to a crawl > when usage reaches high levels. However, I've noticed that "SELECTS" > continue to be quick while UPDATES and INSERTS can take from 20 to 60 > seconds each. I noticed that our DBA has the LRU_MAX_DIRTY parameter > set to 2% and LRU_MIN_DIRTY set to 1% while Informix recommends defaults > as high as 60% and 50% respectively. The DBA insists this is not our > problem and refuses to even change these values a little. > > Has anyone had experience tuning these values? Am I correct to believe > that these values are forcing such a high rate of flushing dirty buffers > to disk that this unnecessary I/O is impacting UPDATES and INSERTS which > create dirty pages? No Jack, your DBA is following the acknowledged wisdom of hundreds of Informix DBAs. The problem is more likely to be too frequent and too long checkpoints. If this only happens during peak load it may be that the physical log is not large enough for peak requirements and it is filling up causing premature checkpoints. If your checkpoints, for example, are taking 10 seconds and normally occur on a scheduled basis (CKPTINTVL) every 10 minutes then 1/60 of your processing time is spent during checkpoints which WILL stall INSERTS/UPDATES/DELETES. If, however, load causes the checkpoint to occur when the Physical log fills this may be happening every 20 seconds and a checkpoint may still take 10 seconds meaning that your updates etc can only process half the time. Check the online log and the time between checkpoints during peak load when the problem is apparent. Look for unscheduled checkpoints as checkpoints occurring sooner than expected. Also run onstat -l in a loop (-r option) and watch the percent used of thephysical log, when it reaches 75% an unscheduled checkpoint is initiated. If this is the situation you just need to increase the size of the Physical log. If the checkpoints are occurring twice as often as desired double the log, if after 2/3 of the time has expired add 1/3 etc. Adjust from there. Art S. Kagel