Re: Database Access Slow

<dhaya.snidhi@gmail.com> wrote in message news:1144756189.411783.105400@u72g2000cwu.googlegroups.com... > Can any body help me in finding out the reason why our Database Server > access all of a sudden becomes extremly slow. ( "dbaccess" take a long > time to connect ). Our application also takes a long time to connect. > > We are using Informix Verson 7.31.UD4 on SCO-Unix Release 3.2v5.0.6. > > Please advice how to find the problem. > > Dhaya DNS I expect.

Apologies for posting such a dum query.. Our database access suddenly becomes very slow. I tried to checked the current SQL's status and found certain SQL's status was in "Wait". See output of onstat -g sql. Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 2113 - - - Not Wait 0 0 9.24 2112 - - - Not Wait 0 0 9.24 2111 - online CR Wait 0 0 9.24 2102 - online CR Not Wait 0 0 9.22 2099 - online CR Not Wait 0 0 9.24 2097 SELECT online CR Wait 0 0 9.24 2093 - online CR Not Wait 0 0 9.24 2092 SELECT online CR Not Wait 0 0 9.22 2083 - online CR Not Wait 0 0 9.24 2081 SELECT online CR Wait 0 0 9.24 2079 SELECT online CR Wait 0 0 9.24 2076 - online CR Not Wait 0 0 9.24 2075 - online CR Not Wait 0 0 9.24 2071 SELECT online CR Not Wait 0 0 9.22 2062 - online CR Not Wait 0 0 9.25 2059 - online CR Not Wait 0 0 9.24 2055 SELECT online DR Not Wait 0 0 9.22 2052 - online CR Wait 0 0 9.24 2035 - online CR Wait 0 0 9.24 2029 - online CR Not Wait 0 0 9.24 1990 - sysmaster CR Not Wait 0 0 9.28 1987 - online CR Not Wait 0 0 9.24 1985 - online CR Wait 0 0 9.24 1984 - online CR Not Wait 0 0 9.22 1972 - online CR Wait 0 0 9.24 1970 - online CR Not Wait 0 0 9.24 1957 - online CR Not Wait 0 0 9.22 1909 - online CR Not Wait 0 0 9.24 1901 - online CR Wait 0 0 9.24 1867 - online CR Not Wait 0 0 9.24 1864 - online CR Wait 0 0 9.24 1851 SELECT online CR Wait 0 0 9.24 1808 - online CR Not Wait 0 0 9.24 1566 - online CR Wait 0 0 9.24 1551 - online CR Wait 0 0 9.24 1548 - online CR Not Wait 0 0 9.24 1540 SELECT online CR Wait 0 0 9.24 1424 SELECT online CR Wait 0 0 9.24 1408 SELECT online CR Wait 0 0 9.24 1325 - online CR Not Wait 0 0 9.24 1284 - online CR Not Wait 0 0 9.24 1266 - online CR Not Wait 0 0 9.24 1177 - online CR Not Wait 0 0 9.24 1125 - online CR Wait 0 0 9.24 1124 - online CR Not Wait 0 0 9.24 559 - online CR Not Wait 0 0 9.24 547 - online CR Not Wait 0 0 9.28 429 - online CR Not Wait 0 0 9.24 425 - online CR Not Wait 0 0 9.24 398 - online CR Not Wait 0 0 9.24 397 - online CR Not Wait 0 0 9.24 379 - online CR Not Wait 0 0 9.24 309 - online CR Not Wait 0 0 9.24 291 - online CR Not Wait 0 0 9.24 288 - online CR Not Wait 0 0 9.24 265 - online CR Not Wait 0 0 9.24 247 - online CR Not Wait 0 0 9.24 223 - online CR Not Wait 0 0 9.24 222 - online CR Not Wait 0 0 9.24 218 - online CR Not Wait 0 0 9.24 217 - online CR Not Wait 0 0 9.24 201 - online CR Not Wait 0 0 9.24 186 - online CR Not Wait 0 0 9.24 185 - online CR Not Wait 0 0 9.24 184 - online CR Not Wait 0 0 9.24 173 - online CR Not Wait 0 0 9.24 156 - online CR Not Wait 0 0 9.24 150 - online CR Not Wait 0 0 9.24 143 - online CR Not Wait 0 0 9.24 137 - online CR Not Wait 0 0 9.24 135 - online CR Not Wait 0 0 9.24 130 - online CR Not Wait 0 0 9.24 127 - online CR Not Wait 0 0 9.24 44 UPDATE online CR Wait 0 0 9.24 43 SELECT online CR Wait 0 0 9.24 42 UPDATE online CR Wait 0 0 9.24 32 - online CR Not Wait 0 0 9.24 29 SELECT online CR Not Wait 0 0 9.24 28 SELECT online CR Not Wait 0 0 9.24 27 SELECT online CR Not Wait 0 0 9.24 19 SELECT online CR Wait 0 0 9.24 18 - online CR Not Wait 0 0 9.24 17 SELECT online CR Wait 0 0 9.24 16 UPDATE online CR Wait 0 0 9.24 15 - online CR Not Wait 0 0 9.24 14 - - - Wait 30 0 0 9.24 13 UPDATE online CR Wait 0 0 9.24 5 INSERT online CR Not Wait 0 0 9.24 $ onstat -g ath Informix Dynamic Server Version 7.31.UD4 -- On-Line -- Up 07:34:50 -- 98304 Kbytes Threads: tid tcb rstcb prty status vp-class name 2 14407900 0 2 sleeping forever 5lio lio vp 0 3 14407b18 0 2 sleeping forever 6pio pio vp 0 4 14407d30 0 2 sleeping forever 7aio aio vp 0 5 1440e130 0 2 sleeping forever 8msc msc vp 0 6 1440e290 0 2 sleeping forever 9aio aio vp 1 7 1440e4a8 1445a018 4 sleeping secs: 1 3cpu main_loop() 8 1440e6c0 0 2 running 1cpu tlitcppoll 9 1440e8d8 0 3 sleeping forever 1cpu tlitcp

SHMVIRTSIZE 2000 What is onstat -g seg ? BUFFERS 24000 48Mb Buffers - extremely small. How much memory do you have on the server? Why so little for the database? Also look at onstat -p Start by looking at what the server is doing during these slow periods. Do you have an IO or CPU issue? MW -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of dhaya.snidhi@gmail.com Sent: Wednesday, 12 April 2006 3:52 a.m. To: informix-list@iiug.org Subject: Re: Database Access Slow Apologies for posting such a dum query.. Our database access suddenly becomes very slow. I tried to checked the current SQL's status and found certain SQL's status was in "Wait". See output of onstat -g sql. [cut] Our onconfig details :- Configuration File: /usr/informi/etc/onconfig #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: Informix Dynamic Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name ROOTPATH /dev/ronline0211 # Path for device containing rootdbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 2000000 # 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 rootdbs # Location (dbspace) of physical log PHYSFILE 25000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 49 # Number of logical log files LOGSIZE 20000 # Logical log size (Kbytes) # Diagnostics MSGPATH /usr/informix/online.log # System message log file path CONSOLE /dev/console # System console message path ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program pathSYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program path TBLSPACE_STATS 1 # System Archive Tape Device TAPEDEV /dev/rct0 # Tape device path#TAPEDEV /dev/null # Tape device path TAPEBLK 32 # Tape block size (Kbytes) TAPESIZE 20000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server/Optical staging area # System Configuration SERVERNUM 0 # Unique id corresponding to a DynamicServer instance DBSERVERNAME onlinelv # Name of default database server DBSERVERALIASES onlinelvr # List of alternate dbservernames DEADLOCK_TIMEOUT 0 # 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 3 # 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 300000 # Maximum number of locks BUFFERS 24000 # Maximum number of shared buffers NUMAIOVPS # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 50 # Maximum number of logical log files CLEANERS 3 # Number of buffer cleaner processes SHMBASE 0x10000000 # Shared memory base address SHMVIRTSIZE 2000 # initial virtual shared memory segmentsize SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 0 # Check point interval (in sec) LRUS 8 # Number of LRU queues LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaninglimit LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # 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 workerthreads ON_RECVRY_THREADS 1 # Default number of online workerthreads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # QATC req DR max time between DRbuffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found filepath # CDR Variables CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes) 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_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 /usr/informix/bar_act.log # ON-Bar Log file - not in /tmp please BAR_DEBUG_LOG /usr/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 # Informix Storage Manager variables ISM_DATA_