Re: database connection delays

Have you tried NET cpu VP instead of "CPU" ? I mean, NETTYPE onsoctcp,3,100,NETOther thing, no. of max. connections works fine for shared memory connection but not for tcp. Alkesh Vipani. ----- Original Message ----- From: "Parker, J" <JParker@WescoDist.com> To: <informix-list@iiug.org> Sent: Monday, February 14, 2000 9:27 AM Subject: database connection delays > > Here an interesting problem for you guru's out there. > Thanks in advance for any help you can provide. > > > Problem: > > Excessive database connection time ( > 20 seconds) > We don't believe the problem to be related to excessive network traffic and > we're not running DNS. > > Informix Database: > 7.31 FC2 > > Operating System: > HP-UX 11 > > Environment: > > We have Informix 4gl applications running on a dedicated application server > which use "soctcp" connections to get to our OLTP database which resides on > a dedicated database server. > > We have an Informix session load of ~ 350 sessions > > Informix DB Server onconfig: > NETTYPE soctcp,3,100,CPU> > Informix Application Server sqlhosts: > profin onsoctcp 307.152.233.113 profinsoc > profin2 onsoctcp 300.1.1.2 profinsoc2> > Informix DB Server sqlhosts: > profin onsoctcp 307.152.233.113 profinsoc > profin2 onsoctcp 300.1.1.2 profinsoc2> > NOTE: 2 different sockets > > Problem Detail: > > Most of the time users can connect within 4 seconds. However, sometimes the > connect time can exceed 20 seconds. During the time of delays, it is always > the case that: > > onstat -g wai> > will show at least 1 of our 2 listen threads in a "sleeping secs" countdown > ( i.e. 55,54,53, ...) > > 93 c0000000412ddf58 0 3 sleeping forever 1cpu > soctcplst > 94 c0000000412fa050 0 3 sleeping secs: 55 1cpu > soctcplst > > During this time, all users for one of the servers (profin or profin2) will > be affected - they will have long connection delays. > If both listen threads are in the wait queue with "sleeping secs:##" , then > ALL new connects will be affected. > > 3 Interesting side facts: > 1- the listen threads always run on cpu vp 1. This happens to be our most > overloaded CPU vp as you can see from onstat -g glo > vp pid class usercpu syscpu total > 1 413 cpu 4571.28 2173.66 6744.94 > 3 415 cpu 3082.04 696.40 3778.44 > 4 416 cpu 4591.27 3113.36 7704.63 > 5 417 cpu 3879.29 51.80 3931.09 > 6 418 cpu 1837.43 33.90 1871.33 > 7 419 cpu 811.50 17.64 829.14 > 8 420 cpu 332.90 9.63 342.53 > > > 2 - Supposing that a program has already made an initial connection when the > listen threads go to into their sleep countdown, the program will NOT suffer > any delays when switching databases (using SAME Informix server name). Yet, > other programs trying to make their initial connection will be waiting > during this same time period. > i.e. > main > while true > database wespro@profin > database standard@profin > end while > end main > > 3 - Supposing that a program has already made an initial connection when the > listen threads go to into their sleep countdown, the program WILL suffer > delays when switching databases (using DIFFERENT Informix server name) . > Just like all other programs trying to make their initial connection. > i.e. > main > while true > database wespro@profin > database standard@profin2 > end while > end main > > > > > Questions: > 1 - can anyone shed any insight on the delays? > 2 - why does the listen thread go into the countdown wait - its like its > waiting for a resource? > 3 - NETTYPE has a field which represents MAXUSERCONNECTS. It doesn't seem > to have any effect. If (on our test system) I set MAXCONNECTS to 1 (w/ 1 > poll thread), it seemingly still allows an indefinite number of connections. > > >