Re: Online 7.12 Tunning [more info]

Serafim Fonseca wrote: > > Hello, > > Following the tips from this group (which I thank), I have already > had improvements on the performance. > But I think it can be improved even more. So I'm sending some > more input which was asked. OK looking at your ONCONFIG file and onstat output I see several things. 1) You have LRU_MAX_DIRTY=10 and LRU_MIN_DIRTY=20 the latter should be less than the former. Try LRU_MIN_DIRTY 5 and leave LRU_MAX_DIRTY alone. Your onstat -F output shows that most physical I/O is chunk writes. Despite the manual, these are inefficient and make for long checkpoint times, with only 6800 buffers checkpoint times should be 1->2 seconds. You did not show your log output but I'd guess you are experiencing long checkpoints. If after fixing LRU_MIN_DIRTY you still have long checkpoints decrease LRU_MAX_DIRTY to 5 and LRU_MIN_DIRTY to 0 (zero). If long checkpoints happen only during high activity try reducing the size of the physical log to force earlier checkpoints. 2) You only have 10 LRUs and 3 cleaners. Try 32 LRUs and 16 cleaners. This will also speed up checkpointing and LRU write performance. Online 5.0x versions performed best with one cleaner per LRU but 7.xx versions seem to do OK with one cleaner for every two queues. 3) You have but one AIO VP, since the CPU VP does work for other threads while waiting for the AIO VP to return many IO requests can queue up. Informix recommends 1.5 AIO VPs per chunk for version after 7.20 and 2.5->3 AIO VPs for earlier ODS versions (the AIO code was rewritten in 7.20 and is much faster). With the 6 chunks you have you should configure 15-18 AIO VPs (yes even on a single CPU machine) lean toward 15 if adding chunks is a rare thing and 18 if the database is growing. 4) I see in the onstat output that many of the spin waits are waiting for data pages (ex: mutex waitlock, name = pt_300071). The changes above will improve this but I would suggest increasing the number of BUFFERS after you have tried the above if the number of spin waits on data pages does not reduce significantly (we normally show only a few or none at all and we do up to 10000 transactions per minute). 5) BTW manually increase LOGSMAX so that you will be able to addlogs in the future if you find the need. 6) Upgrade to 7.21 or later. There have been MANY bug fixes to 7.12 and 7.13 and the 7.2x codebase has several nice new features, but most important 7.13 and later versions are noticably faster than 7.1->7.12. Hope this helps, let me know. Art S. Kagel