Poor Informix performance

Hi, We have a database, a rather huge one, with about 10G of metadata...and almost 400 users... We have serious performance problems over the past few mnths....and all sorts of tuning etc...didnt seem to help much.. The System Admins claim that nothing is wrong with the network and its all the database's fault...Sometimes, the users are just waiting and waiting till a query returns.. Running 'top' on the system gives this O/P load averages: 3.42, 3.49, 3.56 16:33:39 291 processes: 287 sleeping, 4 on cpu CPU states: 59.4% idle, 30.9% user, 9.7% kernel, 0.0% iowait, 0.0% swap Memory: 16G real, 9110M free, 4350M swap in use, 25G swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND 16164 ccm_root 1 0 0 23M 22M cpu/10 185.0H 12.43% ccm_aci 15187 informix 1 41 -10 2853M 2140M sleep 187.7H 5.82% oninit 15189 informix 1 40 -10 2853M 2490M cpu/2 193.8H 5.69% oninit 15190 informix 1 40 -10 2853M 2133M cpu/3 166.8H 5.03% oninit 15191 informix 1 59 -10 2853M 2142M sleep 152.3H 4.09% oninit 15192 informix 1 52 -10 2853M 2808M sleep 143.1H 2.57% oninit 15198 informix 1 59 -10 2853M 2017M sleep 754:11 0.93% oninit 15200 informix 1 59 -10 2848M 2012M sleep 554:28 0.60% oninit 15201 informix 1 59 -10 2848M 2012M sleep 504:17 0.47% oninit 25836 ccm_root 1 58 0 9832K 8144K sleep 0:17 0.33% ccm_eng_inf 15202 informix 1 59 -10 2848M 2012M sleep 534:22 0.28% oninit 15199 informix 1 59 -10 2848M 2012M sleep 525:20 0.23% oninit 15193 informix 1 59 -10 2846M 2005M sleep 230:16 0.18% oninit 15194 informix 1 59 -10 2846M 2005M sleep 117:16 0.12% oninit 15195 informix 1 59 -10 2846M 2036M sleep 86:45 0.05% oninit And its almost always like this... In fact I have seen the load averages to be upto 8 and 9 sometimes... Here's our onconfig.. #************************************************************************** # # INFORMIX SOFTWARE, INC. & Continuus Software Corporation # # Title: onconfig.std # Description: INFORMIX-OnLine Configuration Parameters # C/CM Server: sdcms001 # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name ROOTPATH /local/groups/gscm/informix/sdcms001/sdcms001_root.dbs # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 553248 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing rootdbspace mirror MIRROROFFSET 0 # Offset into mirror device (Kbytes) # Physical Log Configuration PHYSDBS rootdbs # Name of dbspace that contains physical log PHYSFILE 50176 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 1024 # Number of logical log files LOGSIZE 1024 # Size of each logical log file (Kbytes) # Message Files MSGPATH /apps/ccm/informix/log/sdcms001.log # OnLine message log pathname CONSOLE /apps/ccm/informix/log/sdcms001.msg # System console message pathname # Archive Tape Device TAPEDEV /apps/ccm/informix/etc/sdcms001.tapedev # Archive tape device pathname TAPEBLK 16 # Archive tape block size (Kbytes) TAPESIZE 1024000 # Max amount of data to put on tape (Kbytes)# Logical Log Backup Tape Device LTAPEDEV /dev/null # Logical log tape device pathname LTAPEBLK 16 # Logical log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 1 # Unique id associated with thisOnLine instance DBSERVERNAME sdcms001 # Unique name of this OnLine instance DBSERVERALIASES sdcms001_net # List of alternate dbservernames NETTYPE ipcshm,5,100,CPU # Configure poll thread(s) fornettype NETTYPE tlitcp,5,100,NET # Configure poll thread(s) fornettype DEADLOCK_TIMEOUT 60 # Max time to wait for lock indistributed env. RESIDENT -1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 5 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 1 # Affinity start processor AFF_NPROCS 5 # Affinity number of processors # Shared Memory Parameters LOCKS 750000 # Maximum number of locks BUFFERS 900000 # Maximum number of shared memorybuffers NUMAIOVPS # Number of IO vps PHYSBUFF 256 # Size of physical log buffers (Kbytes) LOGBUFF 1024 # Size of logical log buffers (Kbytes)LOGSMAX 1024 # Maximum number of logical log files CLEANERS 32 # Number of page-cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 100000 # initial virtual shared memorysegment size SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Checkpoint interval (in seconds) LRUS 127 # Number of LRU queues LRU_MAX_DIRTY 1 # LRU modified begin-cleaning limit (percent) LRU_MIN_DIRTY 0 # LRU modified end-cleaning limit (percent) LTXHWM 50 # Long TX high-water mark (percent) LTXEHWM 60 # Long TX exclusive high-water mark (percent) TXTIMEOUT 0x12c # Transaction timeout (in seconds) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - OnLine no longer supports this configuration parameter. # To determine the page size used by OnLine on your platform # see the last line of output from the command, 'onstat -b'. # Machine- and Product-Specific Parameters # Recovery Variables # OFF_RECVRY_THREADS: # Number of parallel worker threads during fast recovery or an offline restore. # ON_RECVRY_THREADS: # Number of parallel worker threads during an online restore. OFF_RECVRY_THREADS 10 # Default number of offline workerthreads ON_RECVRY_THREADS 1 # Default number of online wor

Viji wrote: > Hi, > > We have a database, a rather huge one, with about 10G of > metadata...and almost 400 users... > We have serious performance problems over the past few mnths....and > all sorts of tuning etc...didnt seem to help much.. > The System Admins claim that nothing is wrong with the network and its > all the database's fault...Sometimes, the users are just waiting and > waiting till a query returns.. > Running 'top' on the system gives this O/P > > > load averages: 3.42, 3.49, 3.56 > 16:33:39 > 291 processes: 287 sleeping, 4 on cpu > CPU states: 59.4% idle, 30.9% user, 9.7% kernel, 0.0% iowait, 0.0% > swap > Memory: 16G real, 9110M free, 4350M swap in use, 25G swap free > > PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND > 16164 ccm_root 1 0 0 23M 22M cpu/10 185.0H 12.43% ccm_aci > 15187 informix 1 41 -10 2853M 2140M sleep 187.7H 5.82% oninit > 15189 informix 1 40 -10 2853M 2490M cpu/2 193.8H 5.69% oninit > 15190 informix 1 40 -10 2853M 2133M cpu/3 166.8H 5.03% oninit > 15191 informix 1 59 -10 2853M 2142M sleep 152.3H 4.09% oninit > 15192 informix 1 52 -10 2853M 2808M sleep 143.1H 2.57% oninit > 15198 informix 1 59 -10 2853M 2017M sleep 754:11 0.93% oninit > 15200 informix 1 59 -10 2848M 2012M sleep 554:28 0.60% oninit > 15201 informix 1 59 -10 2848M 2012M sleep 504:17 0.47% oninit > 25836 ccm_root 1 58 0 9832K 8144K sleep 0:17 0.33% > ccm_eng_inf > 15202 informix 1 59 -10 2848M 2012M sleep 534:22 0.28% oninit > 15199 informix 1 59 -10 2848M 2012M sleep 525:20 0.23% oninit > 15193 informix 1 59 -10 2846M 2005M sleep 230:16 0.18% oninit > 15194 informix 1 59 -10 2846M 2005M sleep 117:16 0.12% oninit > 15195 informix 1 59 -10 2846M 2036M sleep 86:45 0.05% oninit > > And its almost always like this... > > In fact I have seen the load averages to be upto 8 and 9 sometimes... > > Here's our onconfig.. > > #************************************************************************** > # > # INFORMIX SOFTWARE, INC. & Continuus Software > Corporation > # > # Title: onconfig.std > # Description: INFORMIX-OnLine Configuration Parameters > # C/CM Server: sdcms001 > # > #************************************************************************** > > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name > ROOTPATH /local/groups/gscm/informix/sdcms001/sdcms001_root.dbs> # Path for device containing root > dbspace > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 553248 # Size of root dbspace (Kbytes)> > # Disk Mirroring Configuration > > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing root> dbspace mirror > MIRROROFFSET 0 # Offset into mirror device (Kbytes)> > # Physical Log Configuration > > PHYSDBS rootdbs # Name of dbspace that contains > physical log > PHYSFILE 50176 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 1024 # Number of logical log files > LOGSIZE 1024 # Size of each logical log file > (Kbytes)> > # Message Files > > MSGPATH /apps/ccm/informix/log/sdcms001.log > # OnLine message log pathname > CONSOLE /apps/ccm/informix/log/sdcms001.msg > # System console message pathname > > # Archive Tape Device > > TAPEDEV /apps/ccm/informix/etc/sdcms001.tapedev > # Archive tape device pathname > TAPEBLK 16 # Archive tape block size (Kbytes) > TAPESIZE 1024000 # Max amount of data to put on tape > (Kbytes)> # Logical Log Backup Tape Device > > LTAPEDEV /dev/null # Logical log tape device pathname > LTAPEBLK 16 # Logical log tape block size (Kbytes) > LTAPESIZE 10240 # Max amount of data to put on log > tape (Kbytes)> > # Optical > > STAGEBLOB # INFORMIX-OnLine/Optical staging area > > # System Configuration > > SERVERNUM 1 # Unique id associated with this> OnLine instance > DBSERVERNAME sdcms001 # Unique name of this OnLine instance > DBSERVERALIASES sdcms001_net # List of alternate dbservernames > NETTYPE ipcshm,5,100,CPU # Configure poll thread(s) for> nettype > NETTYPE tlitcp,5,100,NET # Configure poll thread(s) for> nettype > DEADLOCK_TIMEOUT 60 # Max time to wait for lock in> distributed env. > RESIDENT -1 # Forced residency flag (Yes = 1, No = > 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 5 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > > NOAGE 1 # Process aging > AFF_SPROC 1 # Affinity start processor > AFF_NPROCS 5 # Affinity number of processors> > # Shared Memory Parameters > > LOCKS 750000 # Maximum number of locks > BUFFERS 900000 # Maximum number of shared memory> buffers > VPS # Number of IO vps > PHYSBUFF 256 # Size of physical log buffers > (Kbytes) > LOGBUFF 1024 # Size of logical log buffers (Kbytes)> LOGSMAX 1024 # Maximum number of logical log files > CLEANERS 32 # Number of page-cleaner processes > SHMBASE 0xa000000 # Shared memory base address > SHMVIRTSIZE 100000 # initial virtual shared memory> segment size > SHMADD 32768 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 300 # Checkpoint interval (in seconds) > > LRUS 127 # Number of LRU queues > LRU_MAX_DIRTY 1 # LRU modified begin-cleaning limit > (percent) > LRU_MIN_DIRTY 0 # LRU modified end-cleaning limit > (percent) > LTXHWM 50 # Long TX high-water mark (percent) > LTXEHWM 60 # Long TX exclusive high-water mark > (percent) > TXTIMEOUT 0x12c # Transaction timeout (in seconds) > STACKSIZE 32 # Stack size (Kbytes)> > # System Page Size > # BUFFSIZE - OnLine no longer supports this configuration parameter. > # To determine the page size used by OnLine on your > platform > # see the last line of output from the command, 'onstat > -b'. > # Machine- and Product-Specific Parameters > > # Recovery Variables > # OFF_RECVRY_THREADS:@@N

You don't mention the OS, the informix version or anything about the server. But ... you appear to be using cooked files you say there are 400 users but you've configure for 200 at startup there are no temp dbspaces configured what is ccm_aci Fire out your onstat -R, onstat -p, onstat -F, onstat -m, onstat -l Viji wrote: > > Hi, > > We have a database, a rather huge one, with about 10G of > metadata...and almost 400 users... > We have serious performance problems over the past few mnths....and > all sorts of tuning etc...didnt seem to help much.. > The System Admins claim that nothing is wrong with the network and its > all the database's fault...Sometimes, the users are just waiting and > waiting till a query returns.. > Running 'top' on the system gives this O/P > > load averages: 3.42, 3.49, 3.56 > 16:33:39 [cutting] -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #