Connect to DB turns SLOW-PT2

Hello all, Thanks for some of your responses. Just to clarify, this problem seems to have come out of the blue, meaning the instance used to run fine through these peak times, and now I get this problem with the connect hanging. I was hoping there could be a easy fix because it seems as though once connected to the DB, it runs fast. Just waiting for dbaccess to display the different db's to connect to, or for a 4gl to connect is what takes so long, even when run locally on the same machine the instance is on. In terms of what is 'peak' on our system, well it is definitely OLTP, with lots of queries, inserts, none taking longer than a second or two, I have never seen more than 35 active user threads. Thanks for any input from all of you. Thanks Olaf rico@wsex.com > > The clown wrote. > > > > Have you tried ipcshm connections? But it sounds like your > > CPU is just flat > > out...? > > > > What does sar say? ipcshm will not help because all our apps run remotely. However it might help in isolating what the problem is. I will try setting it up locally and see if I encounter the same problem when running dbaccess. I turned sar on today, and will run some stats the next time this happens. > 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 16> These 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,NET> This allows all three processors to accept connections and implies > that you expect at most 300 connections. How can I see how many connections are currently running? I will see if adding another NET vp helps. > 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. I will try this as well. > > 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. onstat -g seg shows only to, one dynamically added. > > 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. Not sure what 'CPU Bound' means, can someone clarify? > > Could be showing my ignorance but 4gl, isql and other wonders of informix > should all run on nettype CPU ( I think ) That doesn't sound right. > > ( 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.. I am using KAIO. > > 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