Informix Performance Issues

All, I am using 9.30 on HP-UX 11i. The select queries take quite a long tpo return. The queries are taking index paths and statistics are also updated. When I tried to look in to the I/O activity, I could find that kio is having maxlen around 16-17. All other things do not have any thing in maxlen. I am attaching the first few lines of onstat -g ioq and onstat -g iov outputs and later the onconfig. RAM on m/c: 8 GB CPUs : 4 Could someone please help me nail down the root cause ? onstat -g ioqq name/id len maxlen totalops dskread dskwrite dskcopy kio 0 0 16 413046691 409498127 3548564 0 kio 1 0 17 311094309 307235554 3858755 0 kio 2 0 16 218761675 215065216 3696459 0 adt 0 0 0 0 0 0 0 msc 0 0 1 37339 0 0 0 aio 0 0 29 38152 1090 34347 0 onstat -g iovclass/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors kio 0 s 542.1 266043030 262575149 3467881 0 505359119 0.5 0 kio 1 s 301.4 147943204 144184932 3758272 0 268265145 0.6 0 kio 2 s 317.3 155740487 152138171 3602316 0 287022047 0.5 0 msc 0 i 0.1 37339 0 0 0 37327 1.0 0 aio 0 i 0.1 37966 1089 34192 0 37924 1.0 0 aio 1 i 0.0 160 1 154 0 200 0.8 0 aio 2 i 0.0 3 0 1 0 3 1.0 0 ONCONFIG : ROOTNAME app3_rootdbs # Root dbspace name ROOTPATH /dev/infdev/app3_rootdbs.1 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 2048000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS app3_plogdbs # Location (dbspace) of physical log PHYSFILE 409494 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 30 # Number of logical log files LOGSIZE 200000 # Logical log size (Kbytes) # Diagnostics MSGPATH /var/opt/informix/1/logs/online.log # System message log file path CONSOLE /dev/null # System console message path ALARMPROGRAM /opt/informix/etc/log_full_1.sh # Alarm program path SYSALARMPROGRAM /opt/informix/etc/evidence_1.sh TBLSPACE_STATS 0 # Maintain tblspace statistics # System Archive Tape Device #TAPEDEV /dev/null # Tape device path TAPEDEV /appadm2/informix/tapedev TAPEBLK 4096 # Tape block size (Kbytes) TAPESIZE 40000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /opt/informix/etc/ltapedev_1 # Log tape device path LTAPEBLK 64 # Log tape block size (Kbytes) LTAPESIZE 2000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME app3_ea_srv # Name of default database serverDBSERVERALIASES gisops88_1,gisops88_1_soc # List of alternate dbservernames NETTYPE soctcp,2,350,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 4294967295 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps toone NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 500000 # Maximum number of locks BUFFERS 300000 # Maximum number of shared buffers NUMAIOVPS 30 # Number of IO vps PHYSBUFF 320 # Physical log buffer size (Kbytes) LOGBUFF 256 # Logical log buffer size (Kbytes) CLEANERS 20 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 1024000 # initial virtual shared memory segment size SHMADD 512000 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 1200 # Check point interval (in sec) LRUS 256 # Number of LRU queues LRU_MAX_DIRTY 5 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) # Dynamic Logging # DYNAMIC_LOGS: # 2 : server automatically add a new logical log when necessary. (ON) # 1 : notify DBA to add new logical logs when necessary. (ON) # 0 : cannot add logical log on the fly. (OFF) # # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM, # because the server can add new logical logs during long transaction rollback. # However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM # can be set to smaller values. # # If dynamic logging is off, LTXHWM/LTXEHWM NEED to be set to smaller values # to avoid long transaction rollback hanging the server due to lack of logical # log space, i.e. 50/60 or lower. DYNAMIC_LOGS 2 LTXHWM 70 LTXEHWM 80 # 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'. # 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 worker threads# Data Replication Variables DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /var/opt/informix/1/dr.lost+found # DR lost+found file path # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)CDR_DSLOCKWAI

"Anirudh Chitnis via DBMonster.com" <forum@DBMonster.com> wrote in message news:4d71b87ec26b42cbba3bfa8ae42593e7@DBMonster.com... > All, > > I am using 9.30 on HP-UX 11i. The select queries take quite a long tpo > return. The queries are taking index paths and statistics are also > updated. > When I tried to look in to the I/O activity, I could find that kio is > having maxlen around 16-17. All other things do not have any thing in > maxlen. I am attaching the first few lines of onstat -g ioq and onstat -g > iov outputs and later the onconfig. > RAM on m/c: 8 GB > CPUs : 4 Can you post the query path from one of the long-running queries (use set explain on;)? You should be able safely to increase BUFFERS to, say 800000. I wonder what effect this has: RESIDENT 4294967295?!!