Re: Pls help to tune my informix engine .. thanks

Henry wrote: > Hi gurus: > > I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs > HP-UX server. > There are 57 dbspaces with a total of 85 chunks. > The informix engine is running very slow recently. > I was not sure whether it is the result of poor tuning or not. > Can you please help me to check and give me some recommendations on how > to tune it in order to get the best performance? > Thanks a lot for your help! > > Here is my Ifx environment: > > NUMCPUVPS 5 > RESIDENT 0 > BUFFERS 500000 > NUMAIOVPS 10 > PHYSBUFF 64 > LOGBUFF 32 > CLEANERS 16 > LRUS 16 > RA_PAGES 128 > RA_THRESHOLD 120 > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES 6 > DS_TOTAL_MEMORY 300000 > DS_MAX_SCANS 1048576 > OPTCOMPIND 1 <SNIP> Quick and DIrty analysis: KAIO is off (no kaio stats in the onstat -g iov output). KAIO will improve things. Until you get KAIO turned on you do not have enough AIO VPs running, you need one per chunk plus a few for overhead. Tuning/monitoring: onstat -g iov should show one aio vp with a io/wup value below 1.0, you have all over 1.0 and many over 3.0 that means that some IOs are waiting for others to complete before they can be serviced. Bufwaits Ratio (BR) = 0.89 which is fine. Buffer Turnover Rate (BTR): 23.9 or you are turning the buffer cache about every 2.5 minutes (or thrashing a subset even more frequently). Need more buffers or need to turn PDQ, increase DS_ settings, and retune large complex queries to use light scans. Your RA_ settings would be high for an OLTP engine, but for DSS it's probably fine supported by a ReadAhead Utilization of 99.2 which is OK. I agree that you should set RESIDENT to -1 and set NOAGE (especially important on HPUX and Solaris which are aggressive about aging long running processes like database servers. Art S. Kagel