Re: Poor Informix performance
Does oncheck run clean, or have you a corrupted index somewhere that
is no longer been used?
has an index been dropped?
is update stats continuing to be run or has it not been run in a long
time?
Eric Herber <eric@I-hate-spam.org> wrote in message news:<c4j1o3$dqh2@news-1.bank.dresdner.net>...
> 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