Re: Performance advice

Hi, first of all, Informix never writes simultanously to different logical logs. It makes sense to split all logical logs and your physical log across different disks. Best solution is to store all the logical logs on a very fast, seperate disk. In order to write parallel to your disks you must use either Kernel Async. I/O or must use 1 AIO-vp per disk ( NUMAIOVPS ). If most I/O is "writing I/O", try to force sorted writes. This can be done by increasing LRU_MAX_DIRTY and LRU_MIN_DIRTY. Your checkpoint duration will increase, however, and for other OLTP applications this might cause some disadvantages, but your INSERTs will become quicker. Using the TCP-B Benchmarks we found the following when we compared dynamic SQL vs. Stored Procedures: Slow -> Quick -> Quicker: Static SQL -> Stored Procedures -> Dynamic SQL ( prepared statements ) This is because a Stored Procedure must be read from the dictionary and must be interpreted by the server. This is an additional overhead that is not neccessary if you are using "prepared statements". If you want to find out the bottlenecks in your system, do the following: 1. Add the configuration parameter WSTATS to your $ONCONFIG file and set the value to 1. 2. Re-start your OnLine instance. 3. Start the following query in an interval of 10 to 20 seconds and save the resulting rows in a separate file. Run this query during your system peaks ! select sum(cumtime), reason from sysseswts where reason != "condition" and cumtime > 1000 group by reason order by 1 desc This query will show you the internal wait-times for the different events your threads are waiting for. While you are measuring the waiting times, you should run your specific system activity report ( sar or vmstat/iostat), too. I can't say, what you have to do, until I have the result of the wait-statistics. Maybe it's an internal locking problem ( most time spent for waiting on locks during inserts ) or maybe it's an I/O problem. 4. Just because I would like to see the performance of your disks, please run the following test for the different chunks configured in your OnLine system: time dd if=/dev/yourchunk of=/dev/null bs=2048 count=5000 Save the "real-time" in a seperate file and run this "dd" for each chunk. If you want, you can calculate how much time is spent inside the OnLine instance. But it's interesting, how much time it would take to write your data without Informix. Okay, I think there a lot to do, have a nice day, Stefan