RE: Connect to DB turns SLOW
The clown wrote.
>
> Have you tried ipcshm connections? But it sounds like your
> CPU is just flat
> out...?
>
> What does sar say?
What no comments on the ONCONFIG :-) You must be busy Mr Clown.
A few thoughts. Your read ahead is configured quite closely.
This can cause the buffers to work to hard try
RA_PAGES 32
RA_THRESHOLD 16These probably are way off the correct tuning but I'm only giving
out concepts today :-(
Next if you have 3 cpuvpu then the nettype should have a 3 so as
to spread the load.
NETTYPE tlitcp,3,300,NETThis allows all three processors to accept connections and implies
that you expect at most 300 connections.
As a general rule you should always have at least one NETTYPE of type
CPU. SO there may be profit in adding another NETTYPE entry into your
ONCONFIG.
Your Initial virtual segment is small. You expect 300 max connections.
You experience problems at peak usage times.
Well I can't remember the in's and outs and more importantly today is only
concepts day. But onstat -g seg will show a list of all the memory segments.
A well configured system shouldn't have too many. User connections and
prepared statements etc all go into this virtual segment. It has a flag
V in the onstat -g seg output.
If you find that there are many then you should increase SHMVIRTSIZE
accordingly.
Looking through all you output I see ( with the magic eye )
Your 2 connection threads are running. Possibly chance but I think not.
Maybe you are just short of connection threads.
Also there doesn't seem to be anything running on CPU1. So you may not be
CPU bound.
Could be showing my ignorance but 4gl, isql and other wonders of informix
should all run on nettype CPU ( I think )
( aside )
Is peak usage lots of connections or lots of intensive scripts/sql running
at
the same time ??
If connections start playing with nettype.
If sql then start changing RA* values and add a CPU nettype.
Assumptions - KIO ??? I hope so..
Jo
>
> 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 --