Informix Performance

Hi, I am having informix performance problems. I don't know where to start. Sometimes the database hangs, always from 00:00am to 5:00 am, but it gives no error in the log file. Can you help me? Sometimes the users says that the system is slow, but I can't identify why. First, the result of onstat -p: Informix Dynamic Server Version 9.30.UC1 -- On-Line -- Up 9 days 21:06:09 -- 1582656 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 67223627 173897813 2077681908 96.76 12042759 61104633 222993237 94.60 isamtot open start read write rewrite delete commit rollbk 2790216351 184683421 616967012 4148277112 65017319 12219659 28562330 8807460 884 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 7 0 0 336038.88 116702.45 1385 4099 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 7192942 14028 903998567 4 0 6224 6824574 39496093 ixda-RA idx-RA da-RA RA-pgsused lchwaits 12311760 5075181 15389056 32074507 56519934 Second my onconfig file: #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.producao # Description: Informix Dynamic Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /cooked_file/dbs_rootP # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 471039 # Size of root dbspace (Kbytes)# Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes)# Physical Log Configuration PHYSDBS wpdphlog # Location (dbspace) of physical log PHYSFILE 60000 # Physical log file size (Kbytes)# Logical Log Configuration LOGFILES 40 # Number of logical log files LOGSIZE 10000 # Logical log size (Kbytes)# Diagnostics MSGPATH /opt/informix/producao.log # System message log file path CONSOLE /dev/console # System console message path ALARMPROGRAM /opt/informix/etc/log_full.sh # Alarm program path TBLSPACE_STATS 1 # Maintain tblspace statistics# System Archive Tape Device TAPEDEV /dev/st0 # Tape device path TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 100000000 # Maximum amount of data to put on tape (Kbytes)# Log Archive Tape Device LTAPEDEV /dev/st0 # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 100000000 # Max amount of data to put on log tape (Kbytes) # OpticalSTAGEBLOB # Informix Dynamic Server staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME producao # Name of default database server DBSERVERALIASES # List of alternate dbservernames DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor # NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one # NOAGE 0 # Process aging # AFF_SPROC 0 # Affinity start processor # AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 200000 # Maximum number of locks BUFFERS 600000 # Maximum number of shared buffers# NUMAIOVPS # Number of IO vps PHYSBUFF 128 # Physical log buffer size (Kbytes) LOGBUFF 128 # Logical log buffer size (Kbytes) CLEANERS 4 # Number of buffer cleaner processes SHMBASE 0x50000000 # Shared memory base address SHMVIRTSIZE 154000 # initial virtual shared memorysegment size SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) LRUS 64 # Number of LRU queues LRU_MAX_DIRTY 3 # LRU percent dirty begin cleaninglimit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) 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'. # 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 workerthreads # Data Replication Variables DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found filepath # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # CDR queue smart blob space CDR_QDATA_SBFLAGS 0 # Log/no-log (default no log)# Backup/Restore variables BAR_ACT_LOG /home/informix/bar_act.log # ON-Bar Log file - not in /tmp please BAR_DEBUG_LOG /home/informix/bar_dbug.log # ON-Bar Debug Log - not in /tmp please BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0# Informix Storage Manager variables ISM_DATA_POOL ISMData ISM_LOG_POOL ISMLogs # Read Ahead Variables RA_PAGES 32 # Number of pages to attempt to readahead RA_THR

Application? OS? Hardware, esp amount of physical memory? Profile of usage of application: eg times of heavy online use; times of batch job runs etc? Output of onstat -g iov also please. "Daniel Castro" <daniel.castro@feliciorocho.org.br> wrote in message news:f86fe5d3.0402261055.34916f88@posting.google.com... > Hi, > > I am having informix performance problems. I don't know where to > start. > Sometimes the database hangs, always from 00:00am to 5:00 am, but it > gives no error in the log file. Can you help me? > Sometimes the users says that the system is slow, but I can't identify > why. > First, the result of onstat -p: > > Informix Dynamic Server Version 9.30.UC1 -- On-Line -- Up 9 days > 21:06:09 -- 1582656 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 67223627 173897813 2077681908 96.76 12042759 61104633 222993237 > 94.60 > > isamtot open start read write rewrite delete commit > rollbk > 2790216351 184683421 616967012 4148277112 65017319 12219659 28562330 > 8807460 884 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 7 0 0 336038.88 116702.45 1385 4099 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress > seqscans > 7192942 14028 903998567 4 0 6224 6824574 > 39496093 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 12311760 5075181 15389056 32074507 56519934 > > Second my onconfig file: > > #************************************************************************** > # > # INFORMIX SOFTWARE, INC. > # > # Title: onconfig.producao > # Description: Informix Dynamic Server Configuration Parameters > # > #************************************************************************** > # Root Dbspace Configuration > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /cooked_file/dbs_rootP # Path for device containing > root dbspace > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 471039 # 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 wpdphlog # Location (dbspace) of physical log > PHYSFILE 60000 # Physical log file size (Kbytes)> # Logical Log Configuration > LOGFILES 40 # Number of logical log files > LOGSIZE 10000 # Logical log size (Kbytes)> # Diagnostics > MSGPATH /opt/informix/producao.log # System message log file > path > CONSOLE /dev/console # System console message path > ALARMPROGRAM /opt/informix/etc/log_full.sh # Alarm program path > TBLSPACE_STATS 1 # Maintain tblspace statistics> # System Archive Tape Device > TAPEDEV /dev/st0 # Tape device path > TAPEBLK 16 # Tape block size (Kbytes) > TAPESIZE 100000000 # Maximum amount of data to put on > tape (Kbytes)> # Log Archive Tape Device > LTAPEDEV /dev/st0 # Log tape device path > LTAPEBLK 16 # Log tape block size (Kbytes) > LTAPESIZE 100000000 # 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 OnLine> instance > DBSERVERNAME producao # Name of default database server > DBSERVERALIASES # List of alternate dbservernames > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> distributed env. > RESIDENT 0 # Forced residency flag (Yes = 1, No = > 0) > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > # NUMCPUVPS 1 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > # NOAGE 0 # Process aging > # AFF_SPROC 0 # Affinity start processor > # AFF_NPROCS 0 # Affinity number of processors > # Shared Memory Parameters > LOCKS 200000 # Maximum number of locks > BUFFERS 600000 # Maximum number of shared buffers> # NUMAIOVPS # Number of IO vps > PHYSBUFF 128 # Physical log buffer size (Kbytes) > LOGBUFF 128 # Logical log buffer size (Kbytes) > CLEANERS 4 # Number of buffer cleaner processes > SHMBASE 0x50000000 # Shared memory base address > SHMVIRTSIZE 154000 # initial virtual shared memory> segment size > SHMADD 32768 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 600 # Check point interval (in sec) > LRUS 64 # Number of LRU queues > LRU_MAX_DIRTY 3 # LRU percent dirty begin cleaning> limit > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit > TXTIMEOUT 0x12c # Transaction timeout (in sec) > 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'. > # 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 worker> threads > 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 /usr/informix/etc/dr.lostfound # DR lost+found file> path > # CDR Variables > CDR_EVALTHREADS 1,2 # evaluator threads > (per-cpu-vp,additional) > CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) > CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR > queue (Kbytes) > CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 > none, 9 max) > CDR_SERIAL 0,0 # Serial Column Sequence > CDR_DBSPACE # dbspace for syscdr database > CDR_QHDR_DBSPACE # CDR queue dbspace (default same as > catalog) > CDR_QDATA_SBSPACE # CDR queue smart blob space > CDR_QDATA_SBFLAGS 0 # Log/no-log (defa