Re: Performance advice

In article <33AD00E4.1FF58E42@weideneder.de>, Stefan <stefan@weideneder.de> writes >Hi, > >first of all, Informix never writes simultanously to different logical >logs. It makes sense to split all logical logs and your physical log However it can be writing to one logical log and writing another one to tape. Use two disks for logical logs and create them on alternating disks. >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 ). Correct - KAIO is definately perferable. > >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. Or increase number of BUFFERS!! > >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". > Agreed. >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. > Warning: This is an undocumented parameter and results may vary between versions! >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 onstat -a would also help, perhaps one session is doing a lot more reads then the other or maybe OPTCOMPIND = 2 instead of 0... -- David Williams