Connect to db turns slow?

Hello All, Sparc/SunOS5.6 IDS 7.30.UC6 I am looking for possible reasons why connecting to the database would get extremely slow. Specifically, if I run dbaccess locally on the machine the instance is on, when I click on 'Query-Language' it can take almost 5 or 6 seconds to display the database choices. Then when I click to on a db to select, it can take up to another 5 or 6 seconds before it lets me start typing sql. This problem is affecting all of my 4gl's and scripts on our application servers, I bring up the dbaccess example, because that is running locally on the machine with the instance. Our db usage looks something ike this: busy, busier, busiest, stop. This problem seems to just pop up right around the 10 or 15 minute peak of usage, and then when the usage all of the sudden go's away, so does this connect problem. My sqlhosts is ontlitcp only. Below I ran some 'onstat -g ath' a few seconds apart hoping one of you gurus could lead me in the right direction of solving this problem. Thanks for your help people. Olaf rico@wsex.com Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 8 days 04:55:11 -- 262144 Kbytes Threads: tid tcb rstcb prty status vp-class name 2 17112368 0 2 sleeping forever 5lio vp 0 3 17112558 0 2 sleeping forever 6pio vp 0 4 17112748 0 2 sleeping forever 7aio vp 0 5 17112938 0 2 sleeping forever 8msc vp 0 6 17112c18 17040018 4 sleeping secs: 1 3cpu main_loop() 7 171133c0 0 2 running 9tli tlitcppoll 8 171138b8 0 2 running 10tli tlitcppoll 9 17113db0 0 2 running 11tli tlitcppoll 10 171163e0 0 3 running 1cpu tlitcplst 11 17116820 170404cc 2 sleeping forever 3cpu flush_sub(0) 12 17116a10 17040980 2 sleeping forever 4cpu flush_sub(1) 13 17116c00 17040e34 2 sleeping forever 3cpu flush_sub(2) 14 17116df0 170412e8 2 sleeping forever 4cpu flush_sub(3) 15 17116fe0 1704179c 2 sleeping forever 4cpu flush_sub(4) 16 171171d0 17041c50 2 sleeping forever 1cpu flush_sub(5) 17 171173c0 17042104 2 sleeping forever 1cpu flush_sub(6) 18 171175b0 170425b8 2 sleeping forever 1cpu flush_sub(7) 19 17117800 0 4 sleeping forever 7aio kaio 20 17117d08 0 4 sleeping forever 1cpu kaio 21 1711a128 0 4 sleeping forever 3cpu kaio 22 1711a7a0 17042a6c 3 sleeping forever 3cpu aslogflush 23 1711aa80 17042f20 2 sleeping secs: 56 1cpu btclean 39 17134540 17043d3c 4 sleeping secs: 1 1cpu onmode_mon 41 17134a10 0 4 sleeping forever 4cpu kaio 42 17134c48 0 2 sleeping forever 12lio vp 1 43 17134e38 0 2 sleeping forever 13pio vp 1 114 171b6a08 17043888 2 cond wait netnorm 4cpu sqlexec 1595 17134170 170454c0 2 sleeping secs: 1 1cpu ontape 2262679 1726ade8 1704eb40 2 cond wait netnorm 3cpu sqlexec 2291034 171c8170 1704e1d8 2 cond wait netnorm 4cpu sqlexec 2318527 171e8888 17046790 2 cond wait netnorm 3cpu sqlexec 2319397 171a7558 17045974 2 cond wait netnorm 3cpu sqlexec 2320387 171492d0 17047f14 2 cond wait netnorm 4cpu sqlexec 2320582 175f68e8 1704cf08 2 cond wait netnorm 3cpu sqlexec 2320921 17c9c988 170433d4 2 cond wait netnorm 1cpu sqlexec 2320946 17cd3f00 17045e28 2 cond wait netnorm 1cpu sqlexec 2320951 171d78b0 17049b4c 2 ready 1cpu sqlexec Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 8 days 04:55:12 -- 262144 Kbytes Threads: tid tcb rstcb prty status vp-class name 2 17112368 0 2 sleeping forever 5lio vp 0 3 17112558 0 2 sleeping forever 6pio vp 0 4 17112748 0 2 sleeping forever 7aio vp 0 5 17112938 0 2 sleeping forever 8msc vp 0 6 17112c18 17040018 4 sleeping secs: 1 3cpu main_loop() 7 171133c0 0 2 running 9tli tlitcppoll 8 171138b8 0 2 running 10tli tlitcppoll 9 17113db0 0 2 running 11tli tlitcppoll 10 171163e0 0 3 running 1cpu tlitcplst 11 17116820 170404cc 2 sleeping forever 3cpu flush_sub(0) 12 17116a10 17040980 2 sleeping forever 4cpu flush_sub(1) 13 17116c00 17040e34 2 sleeping forever 3cpu flush_sub(2) 14 17116df0 170412e8 2 sleeping forever 4cpu flush_sub(3) 15 17116fe0 1704179c 2 sleeping forever 4cpu flush_sub(4) 16 171171d0 17041c50 2 sleeping forever 1cpu flush_sub(5) 17 171173c0 17042104 2 sleeping forever 1cpu flush_sub(6) 18 171175b0 170425b8 2 sleeping forever 1cpu flush_sub(7) 19 17117800 0 4 sleeping forever 7aio kaio 20 17117d08 0 4 sleeping forever 1cpu kaio 21 1711a128 0 4 sleeping forever 3cpu kaio 22 1711a7a0 17042a6c 3 sleeping forever 3cpu aslogflush 23 1711aa80 17042f20 2 sleeping secs: 55 1cpu btclean 39 17134540 17043d3c 4 sleeping secs: 1 1cpu onmode_mon 41 17134a10 0 4 sleeping forever 4cpu kaio 42 17134c48 0 2 sleeping forever 12lio vp 1 43 17134e38 0 2 sleeping forever 13pio vp 1 114 171b6a08 17043888 2 cond wait netnorm 4cpu sqlexec 1595 17134170 170454c0 2 sleeping secs: 5 4cpu ontape 2262679 1726ade8 1704eb40 2 cond wait netnorm 3cpu sqlexec 2291034 171c8170 1704e1d8 2 cond wait netnorm 4cpu sqlexec 2318527 171e8888 17046790 2 cond wait netnorm 3cpu sqlexec 2319397 171a7558 17045974 2 cond wait netnorm 3cpu sqlexec 2320387 171492d0 17047f14 2 cond wait netnorm 4cpu sqlexec 2320921 17c9c988 170433d4 2 cond wait netnorm 1cpu sqlexec 2320946 17cd3f00 17045e28 2 cond wait netnorm 1cpu sqlexec 2320971 17148fe0 1704cf08 2 cond wait closing 3cpu sqlexec Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 8 days 04:55:14 -- 262144 Kbytes Threads: tid tcb rstcb prty status vp-class name 2 17112368 0 2 sleeping forever 5lio vp 0 3 17112558 0 2 sleeping forever 6pio

In article <83o5le$22t$1@news.xmission.com>, rico@wsx.wsex.com (Rico) wrote: > > Hello All, > > Sparc/SunOS5.6 IDS 7.30.UC6 > > I am looking for possible reasons why connecting to the database would get extremely slow. > > Specifically, if I run dbaccess locally on the machine the instance is on, when I click on 'Query-Language' it can take almost 5 or 6 seconds to display the database choices. Then when I click to on a db to select, it can take up to another 5 or 6 seconds before it lets me start typing sql. > > This problem is affecting all of my 4gl's and scripts on our application servers, I bring up the dbaccess example, because that is running locally on the machine with the instance. > > Our db usage looks something ike this: busy, busier, busiest, stop. This problem seems to just pop up right around the 10 or 15 minute peak of usage, and then when the usage all of the sudden go's away, so does this connect problem. > > My sqlhosts is ontlitcp only. Below I ran some 'onstat -g ath' a few seconds apart hoping one of you gurus could lead me in the right direction of solving this problem. > > Thanks for your help people. > > Olaf > rico@wsex.com > I see two things. First, you should probably decrease your LRU MIN and MAX DIRTY to something more like 1 and 2. Second, if your primary access type is tlitcp, you should change the type from NET to CPU. This next one's a guess without more information, but you might want to look at decreasing your RA_THRESHOLD a bit, since if the pages aren't used, it's just extra overhead. Also, I notice that you have MAX_PDQPRIORITY set to 100. Do an onstat -g mgm and make sure that no-one is in the wait queue for that (or anything else, for that matter). That's all I can think of... -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.