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.

In article <889bhv$faa$1@news.xmission.com>, Parker, J <JParker@WescoDist.com> writes > > >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> Check the Informix FAQ at www.smooth1.demon.co.uk. You should not use soctcp on CPU VP. Change this to NETTYPE soctcp,3,100,NET >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. > > > -- David Williams