Re: Performance problem after merging db server

Greg Moye wrote: > > Stephan Stresing wrote: > > > > Hi, > > we have a heavy performance problem after 'merging' two db systems > > to one. We did this because according to the Informix Performance guide > > and the advices of Informix experts. > > The most obvious thing is that BUFFERS 5000 is *WAY* too low. > > Assuming you have one instance with 2 databases, I'd bump it to 65536, > bounce the engine, and watch your read hit ratio (it should improve > dramatically). This may require the kernel parameter SHMMAX to be > bumped, if so have it set to 4 GB and forget about it. > > I'd also set RA_PAGES to 32, RA_THRESHOLD to 30 and LRU's to 127 I think that Greg is right on with his suggestions. I just want to add a few things. You have 2CPUs and NUMCPUVPS set to 2. There tends to be too much overhead in the multiprocessor code for only 2 physical CPUs to be used effectively, also when load increases there will not be any cycles free for OS functions which can effect the database as well. I would suggest either adding a third CPU and leave NUMCPUVPS at 2 or reduce NUMCPUVPS to 1 and set SINGLE_CPU_VP to 1 also so the engine will use more efficient single VP code. At anyrate you can improve the multiprocessor performance if you decide to keep 2 CPU VPs by setting the processor affinity parameters (AFF_SPROC and AFF_NPROCS). You have RESIDENT set to 0 set that to 1 and make sure the Solaris kernel permits enough resident memory. Also if the server is dedicated to Informix and its clients, and the level of filesystem I/O is not great have the sysadmins reduce the percent of RAM dedicated to the system buffer cache to <=10% many UNIX sysadmins habitually assign upwards of 40% of RAM to the buffer cache, this can have a detrimental effect on server performance ESPECIALLY of RESIDENT is 0. If you take Greg's advice and increase LRUS to 127 also increase CLEANERS to at least one per disk but <= LRUS. Check your log file. If your checkpoint duration is more than 4-7 seconds decrease LRU_MAX_DIRTY/LRU_MIN_DIRTY to 5/1 or even 2/0 until you get the duration down. You did not post the header portion of the top output which reports on memory usage. Go over this with the sysadmins and make sure there is enough RAM and swap on the system. Run onstat -p, -g seg, -F, -R, -g ioq, -g iov, -g iof and look for bottlenecks on resources or devices. Look at the RA values and cache %s and make sure they add up to see if Greg's suggestions about modifying the RA parameters will help and afterward to see if they are enough or too much. Look for high bufwaits and low cache %s to determine if you need more buffers or more LRUS. I cannot tell if you are using raw or cooked devices. RAW is 15-25% faster. If you use cooked files you need to increase NUMAIOVPS. Also your physical log, and presumable the logical logs as well, are in the rootdbs. This is a major performance hit. Create a separate dbspace for logs (ideally one each for logical and physical logs but one will do) on a different disk and controller from rootdbs and your data disks. I hope you do not have your database catalogs on the rootdbs either. That's another performance no-no. TAFN. Hope we have helped. Art S. Kagel