Re: Connect to DB turns SLOW
Have you tried ipcshm connections? But it sounds like your CPU is just flat
out...?
What does sar say?
From: rico@wsx.wsex.com (Rico)
>Sparc Solaris 2.6 and IDS 7.30.UC6.
>
>A performance problem seems to have cropped up in the last few weeks and I
>cannot figure out what it could be. Occasionally during peak usage of the
>instance, it can take as long as 7 or 8 seconds to pull up the list of db's
>to connect to in dbaccess. Specifically, I first recognized all my database
>apps were running slower than normal on our application servers, what seems
>to be causing the problem is the length of time to actually connect to the
>db, because when I connect to the db using dbaccess locally or isql
>remotely, either way, when I select 'Query Language' it can take as long as
>7 or 8 seconds to display the possibilities. Once I select a DB it can then
>take another 5 or 6 seconds to actual connect. o
>
>This is not happening during a checkpoint. During this time I am running
>checkpoints every 5 minutes and they only last a maximum of 10 seconds.
>
>This connect slowdown happens in 4gl's, dbaccess, isql, etc. And usually is
>instantaneous when the sytem is not having this problem. The problem
>normally lasts around 10 or 15 minutes, and does seem to have a direct
>correlation with peak usage.
>
>I use tlitcp as my only connection method, remotely or locally.
>My NETTYPE looks like this: NETTYPE tlitcp,2,300,NET
>
>I am hoping some of you out there can steer me in the right direction in
>terms of isolating the cause of this slowdown.
>
>Thanks for your time whoever reads this.
>
>Olaf
>bjv@wsex.com
>
>Here is a onstat -g ath during a problem period:
>
>Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 15 days
>12:04:20 -- 131072 Kbytes
>
>Threads:
> tid tcb rstcb prty status vp-class name
> 2 f112368 0 2 sleeping forever 5lio vp 0
> 3 f112558 0 2 sleeping forever 6pio vp 0
> 4 f112748 0 2 sleeping forever 7aio vp 0
> 5 f112938 0 2 sleeping forever 8msc vp 0
> 6 f112c18 f040018 4 sleeping secs: 1 4cpu
>main_loop()
> 7 f1133c0 0 2 running 9tli
>tlitcppoll
> 8 f1138b8 0 2 running 10tli
>tlitcppoll
> 9 f113db0 0 3 sleeping forever 1cpu
>tlitcplst
> 10 f116350 f0404cc 2 sleeping forever 4cpu
>flush_sub(0)
> 11 f116518 f040980 2 sleeping forever 4cpu
>flush_sub(1)
> 12 f116708 f040e34 2 sleeping forever 4cpu
>flush_sub(2)
> 13 f1168f8 f0412e8 2 sleeping forever 4cpu
>flush_sub(3)
> 14 f116ae8 f04179c 2 sleeping forever 4cpu
>flush_sub(4)
> 15 f116cd8 f041c50 2 sleeping forever 4cpu
>flush_sub(5)
> 16 f116ec8 f042104 2 sleeping forever 4cpu
>flush_sub(6)
> 17 f1170b8 f0425b8 2 sleeping forever 1cpu
>flush_sub(7)
> 18 f1172a8 0 4 sleeping forever 7aio kaio
> 19 f1174f8 0 4 sleeping forever 1cpu kaio
> 20 f11a128 f042a6c 3 sleeping forever 3cpu
>aslogflush
> 21 f11a378 f042f20 2 sleeping secs: 58 3cpu
>btclean
> 36 f131cf0 0 4 running 3cpu kaio
> 38 f1343d8 f043d3c 4 sleeping secs: 1 1cpu
>onmode_mon
> 40 f1348f0 0 4 sleeping forever 4cpu kaio
> 83 f3e99e8 0 2 sleeping forever 11lio vp 1
> 84 f3ebce8 0 2 sleeping forever 12pio vp 1
> 107 f2247f8 f0441f0 2 cond wait netnorm 4cpu
>sqlexec
> 3739603 f201c70 f058674 2 sleeping secs: 4 4cpu ontape
> 4277081 f328d18 f060ed8 2 cond wait netnorm 3cpu
>sqlexec
> 4386942 f1d71d0 f05edec 2 sleeping forever 3cpu
>sqlexec
> 4387531 f1e96f0 f050778 2 running 4cpu
>sqlexec
> 4392994 f134c18 f05b0c8 2 cond wait netnorm 4cpu
>sqlexec
> 4393640 f3fedc8 f05449c 2 cond wait netnorm 3cpu
>sqlexec
> 4394664 fff9750 f051594 2 cond wait netnorm 3cpu
>sqlexec
> 4395036 f1b2630 f05c398 2 sleeping forever 3cpu
>sqlexec
> 4395083 f336718 f06138c 2 cond wait netnorm 3cpu
>sqlexec
> 4395142 10eaca60 f047a60 2 sleeping forever 3cpu
>sqlexec
> 4395145 f14c6d0 f05db1c 2 sleeping forever 3cpu
>sqlexec
> 4395148 f40d0f8 f051efc 2 cond wait closing 4cpu
>sqlexec
> 4395149 f40d7c8 f0502c4 2 mutex wait nsf.lock 1cpu
>sqlexec
>
>####################### HERE IS MY CONFIG ######################
>
>Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 17 days
>14:46:24 -- 131072 Kbytes
>
>Configuration File: /usr/local/informix/etc/onconfig.use
>#**************************************************************************
>#
># INFORMIX SOFTWARE, INC.
>#
># Title: onconfig.std
># Description: Informix Dynamic Server Configuration Parameters
>#
>#**************************************************************************
>
># Root Dbspace Configuration
>
>ROOTNAME dbroot # Root dbspace name
>ROOTPATH /dev/lrdbroot # Path for device containing root dbspace
>ROOTOFFSET 0 # Offset of root dbspace into device
>(Kbytes)
>ROOTSIZE 2090000 # Size of root dbspace (Kbytes)>
># Disk Mirroring Configuration Parameters
>
>MIRROR 1 # Mirroring flag (Yes = 1, No = 0)
>MIRRORPATH /dev/lrdbroot2 # Path for device containing mirrored root
>MIRROROFFSET 0 # Offset into mirrored device (Kbytes)>
># Physical Log Configuration
>
>PHYSDBS dbroot # Location (dbspace) of physical log
>PHYSFILE 8000 # Physical log file size (Kbytes)>
># Logical Log Configuration
>
>LOGFILES 55 # Number of logical log files
>LOGSIZE 12500 # Logical log size (Kbytes)>
># Diagnostics
>
>MSGPATH /usr/local/informix/online.log # System message log file
>path
>CONSOLE /usr/local/informix/console.log # System console message
>path
>ALARMPROGRAM /usr/local/informix/etc/log_full.sh2 # Alarm program path
>SYSALARMPROGRAM /usr/local/informix/etc/evidence.sh # System Alarm program
>path
>TBLSPACE_STATS 1>
># System Archive Tape Device
>
>TAPEDEV /dev/rmt/0 # Tape device path
>TAPEBLK 32 # Tape block size (Kbytes)
>TAPESIZE 12000000 # Maximum amount of data to put on tape
>(Kbytes)>
># Log Archive Tape Device
>
>LTAPEDEV /dev/rmt/1 # Log tape device path
>LTAPEBLK 32 # Log tape block size (Kbytes)
>LTAPESIZE 12000000 # Max amount of data to put on log tape
>(Kbytes)>
># Optical@@N