Re: Informix settings under high load.

Albert wisse wrote: [SNIP] > What I seen and have changed compared to a normal/intial Informix > setup. > - I got often "long transaction aborted" so I increased the logical > log size to eight times the amount under normal load then the problem > vanished. You might want to look at your applications. Either you are inserting/ updating larger numbers of rows without intermediate COMMITs or you are acquiring locks that may be outstanding for several hours due to an interactive user going out to lunch, etc. Rewrite using insert CURSORS and SELECT FOR UPDATE with CURSORS WITH HOLD and COMMIT every N rows to reduce the number of concurrent locks held by any process. > - My checkpoint time increased such that I saw that the Informix was > dead for the client for a while and that increased the stress on the > server. I lowered the LRU_MAX_DIRTY and LRU_MIN_DIRTY to 15 an 10 and > set CKPTINTVL to 60. > This lowered the checkpoint time. > It is posible to lower them even more because the disks can cope with > the (extra) load. Yes, you can lower LRU_MAX_DIRTY and LRU_MIN_DIRTY all the way to zero if needed. We typically keep LRU_MAX_DIRTY between 2 and 5 and LRU_MIN_DIRTY=0 so that there are very few dirty buffers at checkpoint time. If you have increased the checkpoint interval make sure that the PHYSICAL log is large enough that filling it over 75% is not forcing checkpoints more frequently than CKPTINTVL indicates. Besides defeating the purpose of increasing CKPRINTVL it may cause a "PHYSICAL LOG BUFFER OVERFLOW" error (due to a known bug) which will crash the engine. > - During the heavy load I got the Informix message that it waits for a > check point why I don't no yet . I did extra's checkpoints to > circumvent this problem. Reduce number of dirty buffers at checkpoint time as above, increase the number of LRU queues and CLEANER >= LRUS. > - I got the message that I was out of locks so I increased the number > of locks by four. See my point about long transactions. Beyond that you may want to increase that even more to allow for special processing and cleanup needs. > - I saw bufwaits, I have read in another news message of Art S. Kagel > that you have to increase the number of LRUS to avoid them but what is > the impact of many LRUS's say 64 LRUS instead of 8 LRUS. Is there a > guide line when to increase the number of LRUS to get ride of > bufwaits.With which other parameter do I to compare bufwaits to find > a ratio that indicates to increase the LRUS's. Compare BUFWAITS to bufwrits and dskwrits it should be <10% of either of these. I have not been able to get a good answer to this question from Informix myself, but this is the Rule of Thumb that I have taken to using based on my experiences with the BUFWAITS bug. The bug produces BUFWAITS values >25% of both combined. Busier servers will naturally have more BUFWAITS but increasing LRUS will reduce the waits. Start by doubling what you have but I have always used values >16 and at least 32 for busy servers. If you have a large buffer pool (>100000 pages) consider larger values seriously. Remember to increase CLEANERS to at least match LRUS and AVOID the specific values 64 and 96. (Also see one of my posts to: "Re: KAIO vps and buffers" today.) > For the rest it looks good: the cache hit ratio is good, I have no > Fore Ground Writes and the load of the disks are balanced well. What is your ratio of LRU Writes to Chunk Writes? This should be high, at least 3:1 with 10:1 being a practical upper limit for a busy, heavily updated, server. Increasing LRUS/CLEANERS and decreasing LRU_MIN_DIRTY/LRU_MAX_DIRTY will shift Chunk Writes to LRU Writes. Art S. Kagel