Informix UC7.41 - slow

Hi, My Informix installation seems to be running quite slowly. It's running on a Sun Blade 1000 with 2 x 750Mhz processors and 2Gb RAM. Here is the onconfig file - anything obviously wrong or tuneable? ************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: Informix Dynamic Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name ROOTPATH /usr/informix/data/data1 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 200000 # 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 rootdbs # Physical log file size (Kbytes) PHYSFILE 32768 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 45 # Number of logical log files LOGSIZE 2048 # Logical log size (Kbytes) # Diagnostics MSGPATH /usr/informix/online.log # System message log file path CONSOLE /usr/informix/console.log # System console message path ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path SYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program path TBLSPACE_STATS 1 # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 256 # Tape block size (Kbytes) TAPESIZE 128000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 256 # Log tape block size (Kbytes) LTAPESIZE 128000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # System Configuration SERVERNUM 0 # Unique id corresponding to a Dynamic Serverinstance DBSERVERNAME mlgw333 # 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 0 # 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 1 AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 550000 # Maximum number of locks BUFFERS 8192 # Maximum number of shared buffers NUMAIOVPS # Number of IO vps PHYSBUFF 1024 # Size of root dbspace (Kbytes) LOGBUFF 1024 # Logical log buffer size (Kbytes)LOGSMAX 200 # Maximum number of logical log files CLEANERS 16 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 16384 # initial virtual shared memory segment size SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 16 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water mark percentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x4b0 # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - Dynamic Server no longer supports this configuration parameter. # To determine the page size used by Dynamic Server 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 # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover 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_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes) CDR_EVALTHREADS 1,1 # 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_LOGDELTA 30 # % of log space allowed in queue memory CDR_NUMCONNECT 16 # Expected connections per server CDR_NIFRETRY 300 # Connection retry (seconds) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) # Backup/Restore variables BAR_ACT_LOG /tmp/bar_act.log BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 # Informix Storage Manager variables ISM_DATA_POOL ISMData # If the data pool name is changed, be sure to # update $INFORMIXDIR/bin/onbar. Change to # ism_catalog -create_bootstrap -pool <new name> ISM_LOG_POOL ISMLogs # Read Ahead Variables RA_PAGES 8 RA_THRESHOLD 2 # DBSPACETEMP: # Dynamic Server equivalent of DBTEMP for SE. This is the list of dbspaces # that the Dynamic Server SQL Engine will use to create temp tables etc. # If specified it must be a colon separated list of dbspaces that exist # when the Dynamic Server system is brought online. If not specified, or if # all dbspaces specified are invalid, various ad hoc queries will create # temporary files in /tmp instead. DBSPACETEMP # Default temp dbspaces # DUMP*: # The following parameters control the type of diagnostics information which # is preserved when an unanticipated error condition (assertion failure) occurs # during Dynamic Server operations. # For DUMPSHMEM, DUMPGCORE and DUMP

Geezer From The Freezer wrote: > Hi, > > My Informix installation seems to be running quite slowly. It's running on a Sun > Blade > 1000 with 2 x 750Mhz processors and 2Gb RAM. > > Here is the onconfig file - anything obviously wrong or tuneable? Yes :D No such version as UC7.41, but presumably this is something like 7.31.UC7?? <snip> > TAPEDEV /dev/null # Tape device path > TAPEBLK 256 # Tape block size (Kbytes) > TAPESIZE 128000 # Maximum amount of data to put on tape (Kbytes)> > # Log Archive Tape Device > > LTAPEDEV /dev/null # Log tape device path > LTAPEBLK 256 # Log tape block size (Kbytes) > LTAPESIZE 128000 # Max amount of data to put on log tape (Kbytes)> So, either no backups at all using ontape, or onbar -b -w backups? <snip> > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) Why not resident?? <snip> > # Shared Memory Parameters > > LOCKS 550000 # Maximum number of locks > BUFFERS 8192 # Maximum number of shared buffers Why only 16 Mb of BUFFERS on your 2 Gig of RAM machine?? > NUMAIOVPS # Number of IO vps I would actually specific a number here (4 if using KAIO, or, guessing here, half the number of chunks. > PHYSBUFF 1024 # Size of root dbspace (Kbytes) > LOGBUFF 1024 # Logical log buffer size (Kbytes) This is a HUGE value - default is 32 I think for both LOGBUFF and PHYSBUFF > LOGSMAX 200 # Maximum number of logical log files > CLEANERS 16 # Number of buffer cleaner processes > SHMBASE 0xa000000 # Shared memory base address > SHMVIRTSIZE 16384 # initial virtual shared memory segment size Again, this would seem small at 16 Mb - this instance probably runs in under 50 Mb or has loads of extra virtual segments (check onstat -g seg) > TXTIMEOUT 0x4b0 # Transaction timeout (in sec) What happened here?? Default is 0x12c <snip> > DBSPACETEMP # Default temp dbspaces> No temporary dbspaces?? I would suggest that you have at least three non-logged temporary dbspaces. TBP

TBP wrote: > > Geezer From The Freezer wrote: > > Hi, > > > > My Informix installation seems to be running quite slowly. It's running on a Sun > > Blade > > 1000 with 2 x 750Mhz processors and 2Gb RAM. > > > > Here is the onconfig file - anything obviously wrong or tuneable? > > Yes :D > > No such version as UC7.41, but presumably this is something like 7.31.UC7?? oops. 7.31.UC5 even, my mistake :D

Geezer From The Freezer wrote: > > TBP wrote: > >>Geezer From The Freezer wrote: >> >>>Hi, >>> >>>My Informix installation seems to be running quite slowly. It's running on a Sun >>>Blade >>>1000 with 2 x 750Mhz processors and 2Gb RAM. >>> >>>Here is the onconfig file - anything obviously wrong or tuneable? >> >>Yes :D >> >>No such version as UC7.41, but presumably this is something like 7.31.UC7?? > > > oops. 7.31.UC5 even, my mistake :D OK, you're on the right track now. Repost your ONCONFIG info (so we don't have to hunt down the original post) along with ALL of the following and someone will help: Time since startup or since onstat -z was run if later. Output from: onstat -p onstat -d onstat -P onstat -D onstat -m onstat -F onstat -g glo onstat -g iov onstat -g iof onstat -g rea Art S. Kagel

"Art S. Kagel" wrote: > > Geezer From The Freezer wrote: > > > > TBP wrote: > > > >>Geezer From The Freezer wrote: > >> > >>>Hi, > >>> > >>>My Informix installation seems to be running quite slowly. It's running on a Sun > >>>Blade > >>>1000 with 2 x 750Mhz processors and 2Gb RAM. > >>> > >>>Here is the onconfig file - anything obviously wrong or tuneable? > >> > >>Yes :D > >> > >>No such version as UC7.41, but presumably this is something like 7.31.UC7?? > > > > > > oops. 7.31.UC5 even, my mistake :D > > OK, you're on the right track now. Repost your ONCONFIG info (so we don't > have to hunt down the original post) along with ALL of the following and > someone will help: > > Time since startup or since onstat -z was run if later. Output from: > onstat -p > onstat -d > onstat -P > onstat -D > onstat -m > onstat -F > onstat -g glo > onstat -g iov > onstat -g iof > onstat -g rea> > Art S. Kagel ok ran onstat -z to remove stuff - ran some simple export commands from the application running over informix. Here is more info - onconfig file # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name ROOTPATH /usr/informix/data/data1 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 200000 # 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 rootdbs # Physical log file size (Kbytes) PHYSFILE 32768 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 45 # Number of logical log files LOGSIZE 2048 # Logical log size (Kbytes) # Diagnostics MSGPATH /usr/informix/online.log # System message log file path CONSOLE /usr/informix/console.log # System console message path ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path SYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program path TBLSPACE_STATS 1 # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 256 # Tape block size (Kbytes) TAPESIZE 128000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 256 # Log tape block size (Kbytes) LTAPESIZE 128000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # System Configuration SERVERNUM 0 # Unique id corresponding to a Dynamic Serverinstance DBSERVERNAME mlgw333 # 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 0 # 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 1 AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 550000 # Maximum number of locks BUFFERS 50000 # Maximum number of shared buffers NUMAIOVPS # Number of IO vps PHYSBUFF 1024 # Size of root dbspace (Kbytes) LOGBUFF 1024 # Logical log buffer size (Kbytes)LOGSMAX 200 # Maximum number of logical log files CLEANERS 16 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 16384 # initial virtual shared memory segment size SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 16 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water mark percentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x4b0 # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - Dynamic Server no longer supports this configuration parameter. # To determine the page size used by Dynamic Server 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 # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover 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_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes) CDR_EVALTHREADS 1,1 # 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_LOGDELTA 30 # % of log space allowed in queue memory CDR_NUMCONNECT 16 # Expected connections per server CDR_NIFRETRY 300 # Connection retry (seconds) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) # Backup/Restore variables BAR_ACT_LOG /tmp/bar_act.log BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 # Informix Storage Manager variables ISM_DATA_POOL ISMData # If the data pool name is changed, be sure to # update $INFORMIXDIR/bin/onbar. Change to # ism_catalog -create_bootstrap -pool <new name> ISM_LOG_POOL ISMLogs # Read Ahead Variables RA_PAGES 8 RA_THRESHOLD 2 # DBSPACETEMP: # Dynamic Server equivalent o