Re.slow response

Hello Everybody, Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. Our system is running fine with acceptable response, but since our company is growing we are getting more transactions/more hits on the database and started having slow response. 1) All tables are within 2 extents 2) Indexes/data is in good shape 3) Good update statistics script Need suggestion/guidance from you folks as which area we should look after and what would be the problem. Thanks in advance, Sushil... _________________________________________________________________ Get fast, reliable Internet access with MSN 9 Dial-up – now 3 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/

Sushil Shir.... said: > Hello Everybody, > > Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > > Our system is running fine with acceptable response, but since our company > is growing we are getting more transactions/more hits on the database and > started having slow response. > > 1) All tables are within 2 extents > 2) Indexes/data is in good shape What do you mean by this? Are you sure you have the right indexes? > 3) Good update statistics script Are you using dostats? > Need suggestion/guidance from you folks as which area we should look after > and what would > be the problem. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco http://www.catb.org/~esr/faqs/smart-questions.html

Hi, Thanks for your email. Re. Indexes, yes we are using right indexes. I captured most of the SQLs and by using SQEXPLAIN ON noticed most of them are using INDEX PATH, and the one which are not using INDEX PATH are the one which has < 100 rows in the table, not sure if the SEQ.SCAN can create problem for us at this stage. No we are not using dostats, I have developed the UPDATE-STATISTICS script which runs on daily basis and different script to run on weekly basis. These scripts are running probably more than a year now, I did checked the script to make sure I did not miss any tables or index columns used in the where clause. Sushil... >From: "Obnoxio The Clown" <obnoxio@serendipita.com> >To: "Sushil Shir...." <sushilps@hotmail.com> >CC: ids@iiug.org >Subject: Re: Re.slow response [3090] >Date: Thu, 10 Jun 2004 07:56:46 +0100 (BST) >MIME-Version: 1.0 >Received: from linux.serendipita.com ([82.68.157.177]) by >mc8-f21.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Wed, 9 Jun 2004 >23:57:48 -0700 >Received: (qmail 13337 invoked from network); 10 Jun 2004 06:56:46 -0000 >Received: from localhost (HELO www.serendipita.com) (127.0.0.1) by >localhost with SMTP; 10 Jun 2004 06:56:46 -0000 >Received: from 82.68.157.177 (SquirrelMail authenticated user >obnoxio) by www.serendipita.com with HTTP; Thu, 10 Jun 2004 >07:56:46 +0100 (BST) >X-Message-Info: JGTYoYF78jEHjJx36Oi8+YDSEg8qKPPD >Message-ID: <22323.82.68.157.177.1086850606.squirrel@www.serendipita.com> >In-Reply-To: <200406100127.i5A1R0at009558@ace.iiug.org> >References: <200406100127.i5A1R0at009558@ace.iiug.org> >User-Agent: SquirrelMail/1.4.1 >Return-Path: obnoxio@serendipita.com >X-OriginalArrivalTime: 10 Jun 2004 06:57:48.0461 (UTC) >FILETIME=[3D6B55D0:01C44EB8] > > >Sushil Shir.... said: > > Hello Everybody, > > > > Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > > > > Our system is running fine with acceptable response, but since our >company > > is growing we are getting more transactions/more hits on the database >and > > started having slow response. > > > > 1) All tables are within 2 extents > > 2) Indexes/data is in good shape > >What do you mean by this? Are you sure you have the right indexes? > > > 3) Good update statistics script > >Are you using dostats? > > > Need suggestion/guidance from you folks as which area we should look >after > > and what would > > be the problem. > >-- >Bye now, >Obnoxio > >"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" > - Coluche > >"I'm trying to see things your way, but I can't get my head up my ass" > - JCH > >"Ogni uomo mi guarda come se fossi una testa di cazzo" > - Marco > >http://www.catb.org/~esr/faqs/smart-questions.html _________________________________________________________________ Stop worrying about overloading your inbox - get MSN Hotmail Extra Storage! http://join.msn.click-url.com/go/onm00200362ave/direct/01/

Partition your most active tables over separate disks to spread out the=20 I/O. Also, could you furnish more info from your config file, especially the=20 area concerning System Config and Shared Memory? "Sushil Shir...." <sushilps@hotmail.com> Sent by: forum.subscriber@iiug.org 06/09/2004 08:27 PM =20 To: ids@iiug.org cc:=20 Subject: Re.slow response [3090] Hello Everybody, Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. Our system is running fine with acceptable response, but since our company = is growing we are getting more transactions/more hits on the database and=20 started having slow response. 1) All tables are within 2 extents 2) Indexes/data is in good shape 3) Good update statistics script Need suggestion/guidance from you folks as which area we should look after = and what would be the problem. Thanks in advance, Sushil... =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months FREE! = http://join.msn.click-url.com/go/onm00200361ave/direct/01/

El jue, 10 de 06 de 2004 a las 12:36, Sushil Shir.... escribió: > Hi, > > Thanks for your email. > > Re. Indexes, yes we are using right indexes. I captured most of the SQLs > and by using SQEXPLAIN ON noticed most of them are using INDEX PATH, and > the one which are not using INDEX PATH are the one which has < 100 rows in > the table, not sure if the SEQ.SCAN can create problem for us at this stage. I think that it depends the size and number of access to table > > No we are not using dostats, I have developed the UPDATE-STATISTICS script > which runs on daily basis and different script to run on weekly basis. > These scripts are running probably more than a year now, I did checked the > script to make sure I did not miss any tables or index columns used > in the where clause. > > Sushil... >

FWIW, you may want to detach the indexes onto separate disks. If you decide to do that and know what your indexed data will look like, you might want to instead also fragment by expression to even the I/O over the disks. Russ -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of JHAYS2@sears.com Sent: Thursday, June 10, 2004 9:07 AM To: ids@iiug.org Subject: Re: Re.slow response [3093] Partition your most active tables over separate disks to spread out the=20 I/O. Also, could you furnish more info from your config file, especially the=20 area concerning System Config and Shared Memory? "Sushil Shir...." <sushilps@hotmail.com> Sent by: forum.subscriber@iiug.org 06/09/2004 08:27 PM =20 To: ids@iiug.org cc:=20 Subject: Re.slow response [3090] Hello Everybody, Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. Our system is running fine with acceptable response, but since our company = is growing we are getting more transactions/more hits on the database and=20 started having slow response. 1) All tables are within 2 extents 2) Indexes/data is in good shape 3) Good update statistics script Need suggestion/guidance from you folks as which area we should look after = and what would be the problem. Thanks in advance, Sushil... =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months FREE! = http://join.msn.click-url.com/go/onm00200361ave/direct/01/

Sushil Shirodkar said: > Hi, > > Thanks for your email. > > Re. Indexes, yes we are using right indexes. I captured most of the SQLs > and by using SQEXPLAIN ON noticed most of them are using INDEX PATH, and > the one which are not using INDEX PATH are the one which has < 100 rows in > the table, not sure if the SEQ.SCAN can create problem for us at this > stage. No, you are not necessarily using the CORRECT indexes. There can be a major difference between using an index and using the right index. Are you sure you have the correct indexes? > No we are not using dostats, I have developed the UPDATE-STATISTICS script > which runs on daily basis and different script to run on weekly basis. > These scripts are running probably more than a year now, I did checked the > script to make sure I did not miss any tables or index columns used > in the where clause. I'd consider switching to dostats. :o) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco http://www.catb.org/~esr/faqs/smart-questions.html

Hi, Basically we are talking about 6 core tables which we think might be the problem, for these 6 tables we do have detached indexes in its own dbspaces and fragemented by round-robin which also has its own dbspace. sushil... >From: "Russell J. ...." <rclancy@rotech.com> >To: ids@iiug.org >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 >-0400 (EDT) >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by mc3-s3.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 11:30:59 >-0400 (EDT) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) >FILETIME=[E0AF24C0:01C44F02] > >FWIW, you may want to detach the indexes onto separate disks. If you decide >to do that and know what your indexed data will look like, you might want >to >instead also fragment by expression to even the I/O over the disks. > >Russ > >-----Original Message----- >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On >Behalf >Of JHAYS2@sears.com >Sent: Thursday, June 10, 2004 9:07 AM >To: ids@iiug.org >Subject: Re: Re.slow response [3093] > >Partition your most active tables over separate disks to spread out the=20 >I/O. > >Also, could you furnish more info from your config file, especially the=20 >area concerning System Config and Shared Memory? > > > > >"Sushil Shir...." <sushilps@hotmail.com> >Sent by: forum.subscriber@iiug.org >06/09/2004 08:27 PM > >=20 > To: ids@iiug.org > cc:=20 > Subject: Re.slow response [3090] > > >Hello Everybody, > >Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > >Our system is running fine with acceptable response, but since our company >= > > >is growing we are getting more transactions/more hits on the database >and=20 >started having slow response. > >1) All tables are within 2 extents >2) Indexes/data is in good shape >3) Good update statistics script > >Need suggestion/guidance from you folks as which area we should look after >= > > >and what would >be the problem. > >Thanks in advance, >Sushil... > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F >Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months FREE! >= > > >http://join.msn.click-url.com/go/onm00200361ave/direct/01/ > > > > > > > > _________________________________________________________________ Looking to buy a house? Get informed with the Home Buying Guide from MSN House & Home. http://coldwellbanker.msn.com/

how are you buffer hit ratios, and other standard "perf tuning" things (do you have a perf tuning manual to review)? how about you disk system.... do you have caching at that level... i.e., we have EMC disk storage with a GB or so of cache... that's an extra performance level to check. o/s system running fine to? is your slow response ALL the time or at certain times... what else is happening on the system at those times? (both the DB system and O/S)... maybe backup interference? -----Original Message----- From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] Sent: Thursday, June 10, 2004 11:35 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: RE: Re.slow response [3097] Hi, Basically we are talking about 6 core tables which we think might be the problem, for these 6 tables we do have detached indexes in its own dbspaces and fragemented by round-robin which also has its own dbspace. sushil... >From: "Russell J. ...." <rclancy@rotech.com> >To: ids@iiug.org >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 >-0400 (EDT) >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by mc3-s3.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 11:30:59 >-0400 (EDT) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) >FILETIME=[E0AF24C0:01C44F02] > >FWIW, you may want to detach the indexes onto separate disks. If you decide >to do that and know what your indexed data will look like, you might want >to >instead also fragment by expression to even the I/O over the disks. > >Russ > >-----Original Message----- >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On >Behalf >Of JHAYS2@sears.com >Sent: Thursday, June 10, 2004 9:07 AM >To: ids@iiug.org >Subject: Re: Re.slow response [3093] > >Partition your most active tables over separate disks to spread out the=20 >I/O. > >Also, could you furnish more info from your config file, especially the=20 >area concerning System Config and Shared Memory? > > > > >"Sushil Shir...." <sushilps@hotmail.com> >Sent by: forum.subscriber@iiug.org >06/09/2004 08:27 PM > >=20 > To: ids@iiug.org > cc:=20 > Subject: Re.slow response [3090] > > >Hello Everybody, > >Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > >Our system is running fine with acceptable response, but since our company >= > > >is growing we are getting more transactions/more hits on the database >and=20 >started having slow response. > >1) All tables are within 2 extents >2) Indexes/data is in good shape >3) Good update statistics script > >Need suggestion/guidance from you folks as which area we should look after >= > > >and what would >be the problem. > >Thanks in advance, >Sushil... > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 F=5F= >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 F=5F= >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F >Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months FREE! >= > > >http://join.msn.click-url.com/go/onm00200361ave/direct/01/ > > > > > > > > _________________________________________________________________ Looking to buy a house? Get informed with the Home Buying Guide from MSN House & Home. http://coldwellbanker.msn.com/ ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

Hi, Here is the output of onstat -p, buffers read/write looks good. Checkpoint is always 0-2 secs, and checked couple of other things nowhere I can find the problem/issue. I do have perf. manual for reference. We have fiber disk with cache 512 mb, we are planning to put 2 gb cache its under consideration. O/S looks good except the oninit process which is eating 80-90 % of the power, this happens whenever we try to open window for more business, and start getting bigger volume of transactions into the database(irrespective of the time of the day) Backup takes place during night time. -------------------------------------------------------------------------------- ------------------------------------------ Profile dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched 4593473 5000224 2424607883 99.81 2036594 3058089 113185276 98.20 isamtot open start read write rewrite delete commit rollbk 2418731304 35461283 17699289 2117912282 15758216 1343207 44413 1181780 5 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 35322.40 4795.57 19 7245 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 525818 1 140500918 0 0 254 7932843 276854 ixda-RA idx-RA da-RA RA-pgsused lchwaits 437940 834684 402338 1674814 4470596 -------------------------------------------------------------------------------- ------------------------------------- Sushil... >From: NormaJean.Sebastian@tellabs.com >To: forum.subscriber@iiug.org, ids@iiug.org, sushilps@hotmail.com >Subject: RE: Re.slow response [3097] >Date: Thu, 10 Jun 2004 12:45:51 -0500 >MIME-Version: 1.0 >Received: from tellabs.com ([204.154.129.57]) by mc1-f9.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 10:47:27 -0700 >Received: from ([172.23.207.11])by mx4.tellabs.com with ESMTP ;Thu, 10 Jun >2004 12:45:51 -0500 >Received: from localhost (root@localhost)by mailw01.hq.tellabs.com >(8.11.1/8.8.6) with ESMTP id i5AHjp302710;Thu, 10 Jun 2004 12:45:51 -0500 >(CDT) >X-Message-Info: JGTYoYF78jEeFDEaX/cCYoJC6GHZkbHF >X-OpenMail-Hops: 1 >Message-Id: <H00016dd15cfcf99.1086889550.mail@MHS> >Return-Path: normajean.sebastian@tellabs.com >X-OriginalArrivalTime: 10 Jun 2004 17:47:27.0782 (UTC) >FILETIME=[FEE7B460:01C44F12] > > >how are you buffer hit ratios, and other standard "perf tuning" things >(do you have a perf tuning manual to review)? > >how about you disk system.... do you have caching at that level... i.e., >we have EMC disk storage with a GB or so of cache... that's an extra >performance level to check. >o/s system running fine to? > >is your slow response ALL the time or at certain times... what else is >happening on the system at those times? (both the DB system and O/S)... >maybe backup interference? > > > > >-----Original Message----- >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] >Sent: Thursday, June 10, 2004 11:35 AM >To: ids@iiug.org; forum.subscriber@iiug.org >Subject: RE: Re.slow response [3097] > > >Hi, > >Basically we are talking about 6 core tables which we think might be the > >problem, for these 6 tables we do have detached indexes in its own >dbspaces >and fragemented by round-robin which also has its own dbspace. > >sushil... > > >From: "Russell J. ...." <rclancy@rotech.com> > >To: ids@iiug.org > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 > > >-0400 (EDT) > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by >mc3-s3.hotmail.com > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 > >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com >with > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 > >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org > >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 >11:30:59 > >-0400 (EDT) > >Received: (from nobody@localhost)by ace.iiug.org >(8.12.10-14/8.12.8/Submit) > >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) > >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK > >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> > >Apparently-To: forum.subscriber@iiug.org > >Precedence: bulk > >Return-Path: nobody@ace.iiug.org > >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) > >FILETIME=[E0AF24C0:01C44F02] > > > >FWIW, you may want to detach the indexes onto separate disks. If you >decide > >to do that and know what your indexed data will look like, you might >want > >to > >instead also fragment by expression to even the I/O over the disks. > > > >Russ > > > >-----Original Message----- > >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On > >Behalf > >Of JHAYS2@sears.com > >Sent: Thursday, June 10, 2004 9:07 AM > >To: ids@iiug.org > >Subject: Re: Re.slow response [3093] > > > >Partition your most active tables over separate disks to spread out >the=20 > >I/O. > > > >Also, could you furnish more info from your config file, especially >the=20 > >area concerning System Config and Shared Memory? > > > > > > > > > >"Sushil Shir...." <sushilps@hotmail.com> > >Sent by: forum.subscriber@iiug.org > >06/09/2004 08:27 PM > > > >=20 > > To: ids@iiug.org > > cc:=20 > > Subject: Re.slow response [3090] > > > > > >Hello Everybody, > > > >Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > > > >Our system is running fine with acceptable response, but since our >company > >= > > > > > >is growing we are getting more transactions/more hits on the database > >and=20 > >started having slow response. > > > >1) All tables are within 2 extents > >2) Indexes/data is in good shape > >3) Good update statistics script > > > >Need suggestion/guidance from you folks as which area we should look >after > >= > > > > > >and what would > >be the problem. > > > >Thanks in advance, > >Sushil... > > > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 >F=5F= > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 >F=5F= > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F > >Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months >FREE! > >= > > > > > >http://join.msn.click-url.com/go/onm00200361ave/direct/01/ > > > > > > > > > > > > > > > > > >_________________________________________________________________ >Looking to buy a house? Get informed with the Home Buying Guide from MSN > >House & Home. http://coldwellbanker.msn.com/ > > > > > > >----------------------------------------- >============================================================ >The information contained in this message may be privileged >and confidential and protected from disclosure. If the >reader of this message is not the intended recipient, or an >employee or agent resp

I was also going to ask this! For an OLTP, if buffers are set too high, your system might actually be running shower because it may be avoiding light scans. Also, if the OPTCOMPIND is not set to 0, it may not be giving preference to nested-loop joins, which can slow you down too. Finally (although I'm sure you've already checked this) what are your PDQ settings? Stephanie Peltier Database Administrator Texas Northern Probation 214/753-2529 Pager: 214/439-0594 ----- Forwarded by Stephanie Peltier/TXNP/05/USCOURTS on 06/10/2004 01:33 PM ----- "NormaJean.S...." <NormaJean.Sebastian@tellabs.com> Sent by: forum.subscriber@iiug.org 06/10/2004 12:50 PM To ids@iiug.org cc Subject RE: Re.slow response [3098] how are you buffer hit ratios, and other standard "perf tuning" things (do you have a perf tuning manual to review)? how about you disk system.... do you have caching at that level... i.e., we have EMC disk storage with a GB or so of cache... that's an extra performance level to check. o/s system running fine to? is your slow response ALL the time or at certain times... what else is happening on the system at those times? (both the DB system and O/S)... maybe backup interference? -----Original Message----- From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] Sent: Thursday, June 10, 2004 11:35 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: RE: Re.slow response [3097] Hi, Basically we are talking about 6 core tables which we think might be the problem, for these 6 tables we do have detached indexes in its own dbspaces and fragemented by round-robin which also has its own dbspace. sushil... >From: "Russell J. ...." <rclancy@rotech.com> >To: ids@iiug.org >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 >-0400 (EDT) >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by mc3-s3.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 11:30:59 >-0400 (EDT) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) >FILETIME=[E0AF24C0:01C44F02] > >FWIW, you may want to detach the indexes onto separate disks. If you decide >to do that and know what your indexed data will look like, you might want >to >instead also fragment by expression to even the I/O over the disks. > >Russ > >-----Original Message----- >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On >Behalf >Of JHAYS2@sears.com >Sent: Thursday, June 10, 2004 9:07 AM >To: ids@iiug.org >Subject: Re: Re.slow response [3093] > >Partition your most active tables over separate disks to spread out the=20 >I/O. > >Also, could you furnish more info from your config file, especially the=20 >area concerning System Config and Shared Memory? > > > > >"Sushil Shir...." <sushilps@hotmail.com> >Sent by: forum.subscriber@iiug.org >06/09/2004 08:27 PM > >=20 > To: ids@iiug.org > cc:=20 > Subject: Re.slow response [3090] > > >Hello Everybody, > >Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > >Our system is running fine with acceptable response, but since our company >= > > >is growing we are getting more transactions/more hits on the database >and=20 >started having slow response. > >1) All tables are within 2 extents >2) Indexes/data is in good shape >3) Good update statistics script > >Need suggestion/guidance from you folks as which area we should look after >= > > >and what would >be the problem. > >Thanks in advance, >Sushil... > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 F=5F= >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 F=5F= >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F >Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months FREE! >= > > >http://join.msn.click-url.com/go/onm00200361ave/direct/01/ > > > > > > > > _________________________________________________________________ Looking to buy a house? Get informed with the Home Buying Guide from MSN House & Home. http://coldwellbanker.msn.com/ ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

What type of box are you running on? What is the total physical mem on box? What type of app is running against it? Is the app running on the same box? How many processors do you have? What does onstat -u look like when the slowness occurs? Are you seeing blocking locks? How many buffers do you have configured? what is your virt mem set at? what about read aheads? Are you using pdq? How many concurrent connections? 512 mb is nothing. are you sharing disk with another system? We need more info. "Sushil Shir...." <sushilps@hotmail.com> Sent by: forum.subscriber@iiug.org 06/10/04 02:25 PM To ids@iiug.org cc Subject RE: Re.slow response [3099] Hi, Here is the output of onstat -p, buffers read/write looks good. Checkpoint is always 0-2 secs, and checked couple of other things nowhere I can find the problem/issue. I do have perf. manual for reference. We have fiber disk with cache 512 mb, we are planning to put 2 gb cache its under consideration. O/S looks good except the oninit process which is eating 80-90 % of the power, this happens whenever we try to open window for more business, and start getting bigger volume of transactions into the database(irrespective of the time of the day) Backup takes place during night time. -------------------------------------------------------------------------------------------------------------------------- Profile dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched 4593473 5000224 2424607883 99.81 2036594 3058089 113185276 98.20 isamtot open start read write rewrite delete commit rollbk 2418731304 35461283 17699289 2117912282 15758216 1343207 44413 1181780 5 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 35322.40 4795.57 19 7245 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 525818 1 140500918 0 0 254 7932843 276854 ixda-RA idx-RA da-RA RA-pgsused lchwaits 437940 834684 402338 1674814 4470596 --------------------------------------------------------------------------------------------------------------------- Sushil... >From: NormaJean.Sebastian@tellabs.com >To: forum.subscriber@iiug.org, ids@iiug.org, sushilps@hotmail.com >Subject: RE: Re.slow response [3097] >Date: Thu, 10 Jun 2004 12:45:51 -0500 >MIME-Version: 1.0 >Received: from tellabs.com ([204.154.129.57]) by mc1-f9.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 10:47:27 -0700 >Received: from ([172.23.207.11])by mx4.tellabs.com with ESMTP ;Thu, 10 Jun >2004 12:45:51 -0500 >Received: from localhost (root@localhost)by mailw01.hq.tellabs.com >(8.11.1/8.8.6) with ESMTP id i5AHjp302710;Thu, 10 Jun 2004 12:45:51 -0500 >(CDT) >X-Message-Info: JGTYoYF78jEeFDEaX/cCYoJC6GHZkbHF >X-OpenMail-Hops: 1 >Message-Id: <H00016dd15cfcf99.1086889550.mail@MHS> >Return-Path: normajean.sebastian@tellabs.com >X-OriginalArrivalTime: 10 Jun 2004 17:47:27.0782 (UTC) >FILETIME=[FEE7B460:01C44F12] > > >how are you buffer hit ratios, and other standard "perf tuning" things >(do you have a perf tuning manual to review)? > >how about you disk system.... do you have caching at that level... i.e., >we have EMC disk storage with a GB or so of cache... that's an extra >performance level to check. >o/s system running fine to? > >is your slow response ALL the time or at certain times... what else is >happening on the system at those times? (both the DB system and O/S)... >maybe backup interference? > > > > >-----Original Message----- >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] >Sent: Thursday, June 10, 2004 11:35 AM >To: ids@iiug.org; forum.subscriber@iiug.org >Subject: RE: Re.slow response [3097] > > >Hi, > >Basically we are talking about 6 core tables which we think might be the > >problem, for these 6 tables we do have detached indexes in its own >dbspaces >and fragemented by round-robin which also has its own dbspace. > >sushil... > > >From: "Russell J. ...." <rclancy@rotech.com> > >To: ids@iiug.org > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 > > >-0400 (EDT) > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by >mc3-s3.hotmail.com > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 > >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com >with > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 > >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org > >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 >11:30:59 > >-0400 (EDT) > >Received: (from nobody@localhost)by ace.iiug.org >(8.12.10-14/8.12.8/Submit) > >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) > >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK > >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> > >Apparently-To: forum.subscriber@iiug.org > >Precedence: bulk > >Return-Path: nobody@ace.iiug.org > >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) > >FILETIME=[E0AF24C0:01C44F02] > > > >FWIW, you may want to detach the indexes onto separate disks. If you >decide > >to do that and know what your indexed data will look like, you might >want

If nothing has changed except transaction volume, consider adding another=20 vcpu. Also consider lowering your LRU=5FMAX=5FDIRTY, LRU=5FMIN=5FDIRTY to = something very low, like maybe 6 and 5 respectively. This will make your=20 cleaners hyperactive. What about your waiters? Are threads waiting on=20 anything? Is the ready queue backed up? "Sushil Shir...." <sushilps@hotmail.com> Sent by: forum.subscriber@iiug.org 06/10/2004 01:25 PM =20 To: ids@iiug.org cc:=20 Subject: RE: Re.slow response [3099] Hi, Here is the output of onstat -p, buffers read/write looks good. Checkpoint = is always 0-2 secs, and checked couple of other things nowhere I can find the problem/issue. I = do have perf. manual for reference. We have fiber disk with cache 512 mb, we are planning to put 2 gb cache=20 its=20 under consideration. O/S looks good except the oninit process which is eating 80-90 % of the=20 power, this happens whenever we try to open window for more business, and=20 start getting bigger volume of transactions into the database(irrespective = of the time of the day) Backup takes place during night time. ---------------------------------------------------------------------------= ----------------------------------------------- Profile dskreads pagreads bufreads =CAched dskwrits pagwrits bufwrits =CAched 4593473 5000224 2424607883 99.81 2036594 3058089 113185276 98.20 isamtot open start read write rewrite delete commit=20 rollbk 2418731304 35461283 17699289 2117912282 15758216 1343207 44413 1181780 = =20 5 gp=5Fread gp=5Fwrite gp=5Frewrt gp=5Fdel gp=5Falloc gp=5Ffree gp=5Fcurs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 35322.40 4795.57 19 7245 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 525818 1 140500918 0 0 254 7932843 276854 ixda-RA idx-RA da-RA RA-pgsused lchwaits 437940 834684 402338 1674814 4470596 ---------------------------------------------------------------------------= ------------------------------------------ Sushil... >From: NormaJean.Sebastian@tellabs.com >To: forum.subscriber@iiug.org, ids@iiug.org, sushilps@hotmail.com >Subject: RE: Re.slow response [3097] >Date: Thu, 10 Jun 2004 12:45:51 -0500 >MIME-Version: 1.0 >Received: from tellabs.com ([204.154.129.57]) by mc1-f9.hotmail.com with=20 >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 10:47:27 -0700 >Received: from ([172.23.207.11])by mx4.tellabs.com with ESMTP ;Thu, 10=20 Jun=20 >2004 12:45:51 -0500 >Received: from localhost (root@localhost)by mailw01.hq.tellabs.com=20 >(8.11.1/8.8.6) with ESMTP id i5AHjp302710;Thu, 10 Jun 2004 12:45:51 -0500 = >(CDT) >X-Message-Info: JGTYoYF78jEeFDEaX/cCYoJC6GHZkbHF >X-OpenMail-Hops: 1 >Message-Id: <H00016dd15cfcf99.1086889550.mail@MHS> >Return-Path: normajean.sebastian@tellabs.com >X-OriginalArrivalTime: 10 Jun 2004 17:47:27.0782 (UTC)=20 >FILETIME=3D[FEE7B460:01C44F12] > > >how are you buffer hit ratios, and other standard "perf tuning" things >(do you have a perf tuning manual to review)? > >how about you disk system.... do you have caching at that level... i.e., >we have EMC disk storage with a GB or so of cache... that's an extra >performance level to check. >o/s system running fine to? > >is your slow response ALL the time or at certain times... what else is >happening on the system at those times? (both the DB system and O/S)... >maybe backup interference? > > > > >-----Original Message----- >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] >Sent: Thursday, June 10, 2004 11:35 AM >To: ids@iiug.org; forum.subscriber@iiug.org >Subject: RE: Re.slow response [3097] > > >Hi, > >Basically we are talking about 6 core tables which we think might be the > >problem, for these 6 tables we do have detached indexes in its own >dbspaces >and fragemented by round-robin which also has its own dbspace. > >sushil... > > >From: "Russell J. ...." <rclancy@rotech.com> > >To: ids@iiug.org > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 > > >-0400 (EDT) > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by >mc3-s3.hotmail.com > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 > >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com >with > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 > >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org > >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 >11:30:59 > >-0400 (EDT) > >Received: (from nobody@localhost)by ace.iiug.org >(8.12.10-14/8.12.8/Submit) > >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) > >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK > >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> > >Apparently-To: forum.subscriber@iiug.org > >Precedence: bulk > >Return-Path: nobody@ace.iiug.org > >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) > >FILETIME=3D[E0AF24C0:01C44F02] > > > >FWIW, you may want to detach the indexes onto separate disks. If you >decide > >to do that and know what your indexed data will look like, you might >want > >to > >instead also fragment by expression to even the I/O over the disks. > > > >Russ > > > >-----Original Message----- > >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On > >Behalf > >Of JHAYS2@sears.com > >Sent: Thursday, June 10, 2004 9:07 AM > >To: ids@iiug.org > >Subject: Re: Re.slow response [3093] > > > >Partition your most active tables over separate disks to spread out >the=3D20 > >I/O. > > > >Also, could you furnish more info from your config file, especially >the=3D20 > >area concerning System Config and Shared Memory? > > > > > > > > > >"Sushil Shir...." <sushilps@hotmail.com> > >Sent by: forum.subscriber@iiug.org > >06/09/2004 08:27 PM > > > >=3D20 > > To: ids@iiug.org > > cc:=3D20 > > Subject: Re.slow response [3090] > > > > > >Hello Everybody, > > > >Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > > > >Our system is running fine with acceptable response, but since our >company > >=3D > > > > > >is growing we are getting more transactions/more hits on the database > >and=3D20 > >started having slow response. > > > >1) All tables are within 2 extents > >2) Indexes/data is in good shape > >3) Good update statistics script > > > >Need suggestion/guidance from you folks as which area we should look >after > >=3D > > > > > >and what would > >be the problem. > > > >Thanks in advance, > >Sushil... > > > >=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F= =3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5 >F=3D5F=3D > >=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F= =3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5 >F=3D5F=3D > >=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5F=3D5

Are you running client-server applications? Can the bottleneck be at the Application servers or Web servers? If IDS is well tuned and running at its full speed, and yet you are still CPU bound (CPU usage constantly at 90+%). It maybe time to upgrade the hardware. Keith > Hi, > > Here is the output of onstat -p, buffers read/write looks good. Checkpoint > is always 0-2 secs, > and checked couple of other things nowhere I can find the problem/issue. I > do have perf. manual for reference. > We have fiber disk with cache 512 mb, we are planning to put 2 gb cache its > under consideration. > O/S looks good except the oninit process which is eating 80-90 % of the > power, this happens whenever we try to open window for more business, and > start getting bigger volume of transactions into the database(irrespective > of the time of the day) > Backup takes place during night time. > -------------------------------------------------------------------------------- > ------------------------------------------ > Profile > > dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched > 4593473 5000224 2424607883 99.81 2036594 3058089 113185276 98.20 > > isamtot open start read write rewrite delete commit > rollbk > 2418731304 35461283 17699289 2117912282 15758216 1343207 44413 1181780 > 5 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 35322.40 4795.57 19 7245 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 525818 1 140500918 0 0 254 7932843 276854 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 437940 834684 402338 1674814 4470596 > -------------------------------------------------------------------------------- > ------------------------------------- > Sushil... > > >From: NormaJean.Sebastian@tellabs.com > >To: forum.subscriber@iiug.org, ids@iiug.org, sushilps@hotmail.com > >Subject: RE: Re.slow response [3097] > >Date: Thu, 10 Jun 2004 12:45:51 -0500 > >MIME-Version: 1.0 > >Received: from tellabs.com ([204.154.129.57]) by mc1-f9.hotmail.com with > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 10:47:27 -0700 > >Received: from ([172.23.207.11])by mx4.tellabs.com with ESMTP ;Thu, 10 Jun > >2004 12:45:51 -0500 > >Received: from localhost (root@localhost)by mailw01.hq.tellabs.com > >(8.11.1/8.8.6) with ESMTP id i5AHjp302710;Thu, 10 Jun 2004 12:45:51 -0500 > >(CDT) > >X-Message-Info: JGTYoYF78jEeFDEaX/cCYoJC6GHZkbHF > >X-OpenMail-Hops: 1 > >Message-Id: <H00016dd15cfcf99.1086889550.mail@MHS> > >Return-Path: normajean.sebastian@tellabs.com > >X-OriginalArrivalTime: 10 Jun 2004 17:47:27.0782 (UTC) > >FILETIME=[FEE7B460:01C44F12] > > > > > >how are you buffer hit ratios, and other standard "perf tuning" things > >(do you have a perf tuning manual to review)? > > > >how about you disk system.... do you have caching at that level... i.e., > >we have EMC disk storage with a GB or so of cache... that's an extra > >performance level to check. > >o/s system running fine to? > > > >is your slow response ALL the time or at certain times... what else is > >happening on the system at those times? (both the DB system and O/S)... > >maybe backup interference? > > > > > > > > > >-----Original Message----- > >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] > >Sent: Thursday, June 10, 2004 11:35 AM > >To: ids@iiug.org; forum.subscriber@iiug.org > >Subject: RE: Re.slow response [3097] > > > > > >Hi, > > > >Basically we are talking about 6 core tables which we think might be the > > > >problem, for these 6 tables we do have detached indexes in its own > >dbspaces > >and fragemented by round-robin which also has its own dbspace. > > > >sushil... > > > > >From: "Russell J. ...." <rclancy@rotech.com> > > >To: ids@iiug.org > > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 > > > > >-0400 (EDT) > > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by > >mc3-s3.hotmail.com > > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 > > >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com > >with > > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 > > >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org > > >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 > >11:30:59 > > >-0400 (EDT) > > >Received: (from nobody@localhost)by ace.iiug.org > >(8.12.10-14/8.12.8/Submit) > > >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) > > >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK > > >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> > > >Apparently-To: forum.subscriber@iiug.org > > >Precedence: bulk > > >Return-Path: nobody@ace.iiug.org > > >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) > > >FILETIME=[E0AF24C0:01C44F02] > > > > > >FWIW, you may want to detach the indexes onto separate disks. If you > >decide > > >to do that and know what your indexed data will look like, you might > >want > > >to > > >instead also fragment by expression to even the I/O over the disks. > > > > > >Russ > > > > > >-----Original Message----- > > >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On > > >Behalf > > >Of JHAYS2@sears.com > > >Sent: Thursday, June 10, 2004 9:07 AM > > >To: ids@iiug.org > > >Subject: Re: Re.slow response [3093] > > > > > >Partition your most active tables over separate disks to spread out > >the=20 > > >I/O. > > > > > >Also, could you furnish more info from your config file, especially > >the=20 > > >area concerning System Config and Shared Memory? > > > > > > > > > > > > > > >"Sushil Shir...." <sushilps@hotmail.com> > > >Sent by: forum.subscriber@iiug.org > > >06/09/2004 08:27 PM > > > > > >=20 > > > To: ids@iiug.org > > > cc:=20 > > > Subject: Re.slow response [3090] > > > > > > > > >Hello Everybody, > > > > > >Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > > > > > >Our system is running fine with acceptable response, but since our > >company > > >= > > > > > > > > >is growing we are getting more transactions/more hits on the database > > >and=20 > > >started having slow response. > > > > > >1) All tables are within 2 extents > > >2) Indexes/data is in good shape > > >3) Good update statistics script > > > > > >Need suggestion/guidance from you folks as which area we should look > >after > > >= > > > > > > > > >and what would > > >be the problem. > > > > > >Thanks in advance, > > >Sushil... > > > > > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 > >F=5F= > > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 > >F=5F= > > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F > > >Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months > >FREE! > > >= > > > > > > > > >http://join.msn.click

For what it's worth; I always look at I/O. While it's possible to have a problem elsewhere, and I've heard of people having them, my experience has been that there's ALWAYS an I/O bottleneck to be found when you're talking about performance issues. My troubleshooting approach moves from the O/S upwards. I tend to start with an iostat or a sar to determine which disks are particularly "hot". On my Solaris box here, I use iostat -xCn 5 100 I am particularly interested in the %w number; that's the percentage of time that there are "transactions" (not necessarily database transactions) waiting on the disk, regardless of %b (busy). The idea is that even if the disk is not busy, if you have stuff waiting on it, you're still in trouble. Likewise, if the disk is busy, but generally nobody is waiting on it, that might not be such a bad thing (though I'd probably still want to distribute data off of it if I could). Once you've done that, use the device column in the iostat output to go back to your dbspaces (this can sometimes take some work, depending on your volume mananger -- I have a hard time reading Veritas Volume Manager output), and determine which dbspaces are your hot spots. You might be surprised that these are not your big tables, but contain things like your logfiles, temp space, etc. Once you know what dbspaces you have issues with, you can start to resolve your problems. If you've got I/O problems with temp space, consider either striping the disk at the O/S or hardware level, or increasing the number of temp spaces (Informix should round-robin requests across the different temp spaces). If the issue is with your logfiles, some sort of striping seems like the right solution to me. If the issue is with your table data, consider doing something other than round-robin fragmentation, and across multiple disks. If the issue is with indexes, definately consider some sort of fragmentation there. From an application perspective, make sure that you'e not doing a "select *" where selecting individual columns will do. Remember that if you select only columns that are included in an index, you'll get all of you data without going to the table at all, which really reduces your I/O. To that end, you might want to consider adding some columns to your existing indexes, even if they're not included in your where clause, so that you can do more index-only reads to retrieve your data. Be careful, though, you do incurr some "cost" with that. For what it's worth; I'm not a big fan of round-robin fragmentation. To be honest, I think that current operating systems and/or hardware do that kind of "striping" faster and better, and it seems to me not to be worthwhile just to emulate disk striping at the row level. I'm sure that someone much smarter than I will disabuse me of that notion, but I don't think I've ever done or recommended round-robin fragmentation. Feel free to write me directly if you have questions or something that I've said doesn't make sense. If it doesn't make sense, it's likely wrong... <G> But you never know... <G> Thanks. Dan Michaelis >From: "Sushil Shir...." <sushilps@hotmail.com> >To: ids@iiug.org >Subject: RE: Re.slow response [3099] Date: Thu, 10 Jun 2004 14:25:37 -0400 >(EDT) > >Hi, > >Here is the output of onstat -p, buffers read/write looks good. Checkpoint >is always 0-2 secs, >and checked couple of other things nowhere I can find the problem/issue. I >do have perf. manual for reference. >We have fiber disk with cache 512 mb, we are planning to put 2 gb cache its >under consideration. >O/S looks good except the oninit process which is eating 80-90 % of the >power, this happens whenever we try to open window for more business, and >start getting bigger volume of transactions into the database(irrespective >of the time of the day) >Backup takes place during night time. >------------------------------------------------------------------------------- ------------------------------------------- >Profile > >dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched >4593473 5000224 2424607883 99.81 2036594 3058089 113185276 98.20 > >isamtot open start read write rewrite delete commit >rollbk >2418731304 35461283 17699289 2117912282 15758216 1343207 44413 1181780 >5 > >gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs >0 0 0 0 0 0 0 > >ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >0 0 0 35322.40 4795.57 19 7245 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans >525818 1 140500918 0 0 254 7932843 276854 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits >437940 834684 402338 1674814 4470596 >------------------------------------------------------------------------------- -------------------------------------- >Sushil... > > >From: NormaJean.Sebastian@tellabs.com > >To: forum.subscriber@iiug.org, ids@iiug.org, sushilps@hotmail.com > >Subject: RE: Re.slow response [3097] > >Date: Thu, 10 Jun 2004 12:45:51 -0500 > >MIME-Version: 1.0 > >Received: from tellabs.com ([204.154.129.57]) by mc1-f9.hotmail.com with > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 10:47:27 -0700 > >Received: from ([172.23.207.11])by mx4.tellabs.com with ESMTP ;Thu, 10 >Jun > >2004 12:45:51 -0500 > >Received: from localhost (root@localhost)by mailw01.hq.tellabs.com > >(8.11.1/8.8.6) with ESMTP id i5AHjp302710;Thu, 10 Jun 2004 12:45:51 -0500 > >(CDT) > >X-Message-Info: JGTYoYF78jEeFDEaX/cCYoJC6GHZkbHF > >X-OpenMail-Hops: 1 > >Message-Id: <H00016dd15cfcf99.1086889550.mail@MHS> > >Return-Path: normajean.sebastian@tellabs.com > >X-OriginalArrivalTime: 10 Jun 2004 17:47:27.0782 (UTC) > >FILETIME=[FEE7B460:01C44F12] > > > > > >how are you buffer hit ratios, and other standard "perf tuning" things > >(do you have a perf tuning manual to review)? > > > >how about you disk system.... do you have caching at that level... i.e., > >we have EMC disk storage with a GB or so of cache... that's an extra > >performance level to check. > >o/s system running fine to? > > > >is your slow response ALL the time or at certain times... what else is > >happening on the system at those times? (both the DB system and O/S)... > >maybe backup interference? > > > > > > > > > >-----Original Message----- > >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] > >Sent: Thursday, June 10, 2004 11:35 AM > >To: ids@iiug.org; forum.subscriber@iiug.org > >Subject: RE: Re.slow response [3097] > > > > > >Hi, > > > >Basically we are talking about 6 core tables which we think might be the > > > >problem, for these 6 tables we do have detached indexes in its own > >dbspaces > >and fragemented by round-robin which also has its own dbspace. > > > >sushil... > > > > >From: "Russell J. ...." <rclancy@rotech.com> > > >To: ids@iiug.org > > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 > > > > >-0400 (EDT) > > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by > >mc3-s3.hotmail.com > > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:5

OK. You are asking Informix to do too much work - high CPU with oninit. In 19 checkpoints (1.6 hours if interval is 5 minutes), you have had 2.4 billion buffer reads, 276000 sequential scans and 4.4 million latch waits. At each checkpoint over 13 users wait on average for the checkpoint to complete. Do you know what tables have sequential scans and how many rows are in each table (from sysmaster)? There is a lot of contention for the same resource - latch waits. Why is this so high? There is a lot of activity on your system - average 25 million buffers per minute. How big is your database and how many users do you have? Cacheing looks excellent. This is where I would start. MW > -----Original Message----- > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On > Behalf Of Sushil Shir.... > Sent: Friday, 11 June 2004 6:26 a.m. > To: ids@iiug.org > Subject: RE: Re.slow response [3099] > > > Hi, > > Here is the output of onstat -p, buffers read/write looks good. > Checkpoint > is always 0-2 secs, > and checked couple of other things nowhere I can find the > problem/issue. I > do have perf. manual for reference. > We have fiber disk with cache 512 mb, we are planning to put 2 gb > cache its > under consideration. > O/S looks good except the oninit process which is eating 80-90 % of the > power, this happens whenever we try to open window for more business, and > start getting bigger volume of transactions into the > database(irrespective > of the time of the day) > Backup takes place during night time. > ------------------------------------------------------------------ > -------------------------------------------------------- > Profile > > dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched > 4593473 5000224 2424607883 99.81 2036594 3058089 113185276 98.20 > > isamtot open start read write rewrite delete commit > rollbk > 2418731304 35461283 17699289 2117912282 15758216 1343207 44413 > 1181780 > 5 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 35322.40 4795.57 19 7245 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 525818 1 140500918 0 0 254 7932843 276854 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 437940 834684 402338 1674814 4470596 > ------------------------------------------------------------------ > --------------------------------------------------- > Sushil... > > >From: NormaJean.Sebastian@tellabs.com > >To: forum.subscriber@iiug.org, ids@iiug.org, sushilps@hotmail.com > >Subject: RE: Re.slow response [3097] > >Date: Thu, 10 Jun 2004 12:45:51 -0500 > >MIME-Version: 1.0 > >Received: from tellabs.com ([204.154.129.57]) by mc1-f9.hotmail.com with > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 10:47:27 -0700 > >Received: from ([172.23.207.11])by mx4.tellabs.com with ESMTP > ;Thu, 10 Jun > >2004 12:45:51 -0500 > >Received: from localhost (root@localhost)by mailw01.hq.tellabs.com > >(8.11.1/8.8.6) with ESMTP id i5AHjp302710;Thu, 10 Jun 2004 > 12:45:51 -0500 > >(CDT) > >X-Message-Info: JGTYoYF78jEeFDEaX/cCYoJC6GHZkbHF > >X-OpenMail-Hops: 1 > >Message-Id: <H00016dd15cfcf99.1086889550.mail@MHS> > >Return-Path: normajean.sebastian@tellabs.com > >X-OriginalArrivalTime: 10 Jun 2004 17:47:27.0782 (UTC) > >FILETIME=[FEE7B460:01C44F12] > > > > > >how are you buffer hit ratios, and other standard "perf tuning" things > >(do you have a perf tuning manual to review)? > > > >how about you disk system.... do you have caching at that level... i.e., > >we have EMC disk storage with a GB or so of cache... that's an extra > >performance level to check. > >o/s system running fine to? > > > >is your slow response ALL the time or at certain times... what else is > >happening on the system at those times? (both the DB system and O/S)... > >maybe backup interference? > > > > > > > > > >-----Original Message----- > >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] > >Sent: Thursday, June 10, 2004 11:35 AM > >To: ids@iiug.org; forum.subscriber@iiug.org > >Subject: RE: Re.slow response [3097] > > > > > >Hi, > > > >Basically we are talking about 6 core tables which we think might be the > > > >problem, for these 6 tables we do have detached indexes in its own > >dbspaces > >and fragemented by round-robin which also has its own dbspace. > > > >sushil... > > > > >From: "Russell J. ...." <rclancy@rotech.com> > > >To: ids@iiug.org > > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 > > > > >-0400 (EDT) > > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by > >mc3-s3.hotmail.com > > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 > > >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com > >with > > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 > > >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org > > >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 > >11:30:59 > > >-0400 (EDT) > > >Received: (from nobody@localhost)by ace.iiug.org > >(8.12.10-14/8.12.8/Submit) > > >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) > > >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK > > >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> > > >Apparently-To: forum.subscriber@iiug.org > > >Precedence: bulk > > >Return-Path: nobody@ace.iiug.org > > >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) > > >FILETIME=[E0AF24C0:01C44F02] > > > > > >FWIW, you may want to detach the indexes onto separate disks. If you > >decide > > >to do that and know what your indexed data will look like, you might > >want > > >to > > >instead also fragment by expression to even the I/O over the disks. > > > > > >Russ > > > > > >-----Original Message----- > > >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On > > >Behalf > > >Of JHAYS2@sears.com > > >Sent: Thursday, June 10, 2004 9:07 AM > > >To: ids@iiug.org > > >Subject: Re: Re.slow response [3093] > > > > > >Partition your most active tables over separate disks to spread out > >the=20 > > >I/O. > > > > > >Also, could you furnish more info from your config file, especially > >the=20 > > >area concerning System Config and Shared Memory? > > > > > > > > > > > > > > >"Sushil Shir...." <sushilps@hotmail.com> > > >Sent by: forum.subscriber@iiug.org > > >06/09/2004 08:27 PM > > > > > >=20 > > > To: ids@iiug.org > > > cc:=20 > > > Subject: Re.slow response [3090] > > > > > > > > >Hello Everybody, > > > > > >Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > > > > > >Our system is running fine with acceptable response, but since our > >company > > >= > > > > > > > > >is growing we are getting more transactions/more hits on the database > > >and=20 > > >started having slow response. > > > > > >1) All tabl

Darren_Jaco.... said: > What type of box are you running on? > What is the total physical mem on box? > What type of app is running against it? > Is the app running on the same box? > How many processors do you have? > What does onstat -u look like when th [SNIP] I bet you say that to all the girls.... -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco http://www.catb.org/~esr/faqs/smart-questions.html

Hi Stephanie, Thanks for your email, we have buffers=400000 (might be high), and there are not light scans taking place on the system right now, and OPTCOMIND = 2 and here are the PDQ settings MAX_PDQPRIORITY 100 DS_MAX_QUERIES 6 DS_TOTAL_MEMORY 768 DS_MAX_SCANS 1048576 DATASKIP off -------------------------------------------------------------------------------- ------------------------- Sushil.. >From: "Stephanie_P...." <Stephanie_Peltier@txnp.uscourts.gov> >To: ids@iiug.org >Subject: Fw: Re.slow response [3100] Date: Thu, 10 Jun 2004 14:40:52 >-0400 (EDT) >Received: from mc2-f2.hotmail.com ([65.54.190.9]) by mc2-s10.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 11:53:18 -0700 >Received: from ace.iiug.org ([216.177.38.212]) by mc2-f2.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6713); Thu, 10 Jun 2004 11:52:40 -0700 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id i5AIgMFX027421;Thu, 10 Jun 2004 14:42:35 >-0400 (EDT) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id i5AIeqCe027345;Thu, 10 Jun 2004 14:40:52 -0400 (EDT) >X-Message-Info: jl7Vrt/mfsp6+GzP2GPBAWM6arPQOimu >Message-Id: <200406101840.i5AIeqCe027345@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 10 Jun 2004 18:52:41.0121 (UTC) >FILETIME=[1B6FD910:01C44F1C] > >I was also going to ask this! > >For an OLTP, if buffers are set too high, your system might actually be >running shower because it may be avoiding light scans. Also, if the >OPTCOMPIND is not set to 0, it may not be giving preference to nested-loop >joins, which can slow you down too. Finally (although I'm sure you've >already checked this) what are your PDQ settings? > >Stephanie Peltier >Database Administrator >Texas Northern Probation >214/753-2529 >Pager: 214/439-0594 >----- Forwarded by Stephanie Peltier/TXNP/05/USCOURTS on 06/10/2004 01:33 >PM ----- > >"NormaJean.S...." <NormaJean.Sebastian@tellabs.com> >Sent by: forum.subscriber@iiug.org >06/10/2004 12:50 PM > >To >ids@iiug.org >cc > >Subject >RE: Re.slow response [3098] > > > > > > > >how are you buffer hit ratios, and other standard "perf tuning" things >(do you have a perf tuning manual to review)? > >how about you disk system.... do you have caching at that level... i.e., >we have EMC disk storage with a GB or so of cache... that's an extra >performance level to check. >o/s system running fine to? > >is your slow response ALL the time or at certain times... what else is >happening on the system at those times? (both the DB system and O/S)... >maybe backup interference? > > > > >-----Original Message----- >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] >Sent: Thursday, June 10, 2004 11:35 AM >To: ids@iiug.org; forum.subscriber@iiug.org >Subject: RE: Re.slow response [3097] > > >Hi, > >Basically we are talking about 6 core tables which we think might be the > >problem, for these 6 tables we do have detached indexes in its own >dbspaces >and fragemented by round-robin which also has its own dbspace. > >sushil... > > >From: "Russell J. ...." <rclancy@rotech.com> > >To: ids@iiug.org > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 11:28:37 > > >-0400 (EDT) > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by >mc3-s3.hotmail.com > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 -0700 > >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com >with > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 > >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org > >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 >11:30:59 > >-0400 (EDT) > >Received: (from nobody@localhost)by ace.iiug.org >(8.12.10-14/8.12.8/Submit) > >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) > >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK > >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> > >Apparently-To: forum.subscriber@iiug.org > >Precedence: bulk > >Return-Path: nobody@ace.iiug.org > >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) > >FILETIME=[E0AF24C0:01C44F02] > > > >FWIW, you may want to detach the indexes onto separate disks. If you >decide > >to do that and know what your indexed data will look like, you might >want > >to > >instead also fragment by expression to even the I/O over the disks. > > > >Russ > > > >-----Original Message----- > >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On > >Behalf > >Of JHAYS2@sears.com > >Sent: Thursday, June 10, 2004 9:07 AM > >To: ids@iiug.org > >Subject: Re: Re.slow response [3093] > > > >Partition your most active tables over separate disks to spread out >the=20 > >I/O. > > > >Also, could you furnish more info from your config file, especially >the=20 > >area concerning System Config and Shared Memory? > > > > > > > > > >"Sushil Shir...." <sushilps@hotmail.com> > >Sent by: forum.subscriber@iiug.org > >06/09/2004 08:27 PM > > > >=20 > > To: ids@iiug.org > > cc:=20 > > Subject: Re.slow response [3090] > > > > > >Hello Everybody, > > > >Env: IDS 9.30 UC3,AIX 5.1. 4 cpu, 8gb ram IBM P660 with fiber channel. > > > >Our system is running fine with acceptable response, but since our >company > >= > > > > > >is growing we are getting more transactions/more hits on the database > >and=20 > >started having slow response. > > > >1) All tables are within 2 extents > >2) Indexes/data is in good shape > >3) Good update statistics script > > > >Need suggestion/guidance from you folks as which area we should look >after > >= > > > > > >and what would > >be the problem. > > > >Thanks in advance, > >Sushil... > > > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 >F=5F= > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5 >F=5F= > >=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F > >Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months >FREE! > >= > > > > > >http://join.msn.click-url.com/go/onm00200361ave/direct/01/ > > > > > > > > > > > > > > > > > >_________________________________________________________________ >Looking to buy a house? Get informed with the Home Buying Guide from MSN > >House & Home. http://coldwellbanker.msn.com/ > > > > > > >----------------------------------------- >============================================================ >The information contained in this message may be privileged >and confidential and protected from disclosure. If the >reader of this message is not the intended recipient, or an >employee or agent responsible for delivering this message to >the intended recipient, y

Hi Keith, Yeah we are working on application as well we are considering hardware too (appl and db servers). Sushil... >From: keithfang@comcast.net >To: "Sushil Shir...." <sushilps@hotmail.com> >CC: ids@iiug.org, Subject: RE: Re.slow response [3099] Date: Thu, 10 Jun >2004 20:30:56 +0000 >Received: from rwcrmhc11.comcast.net ([204.127.198.35]) by >mc2-f10.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 >13:30:57 -0700 >Received: from 204.127.197.112 ([204.127.197.112]) by comcast.net >(rwcrmhc11) with SMTP id <20040610203050013009eplfe>; Thu, 10 Jun >2004 20:30:50 +0000 >Received: from [216.131.220.93] by 204.127.197.112;Thu, 10 Jun 2004 >20:30:56 +0000 >X-Message-Info: JGTYoYF78jEA3ZgVNHIpQyQzHZeKv5H6 >Message-Id: ><061020042030.21016.40C8C500000770DA00005218220075074409020E00089B070A05@comcas t.net> >X-Mailer: AT&T Message Center Version 1 (May 18 2004) >X-Authenticated-Sender: a2VpdGhmYW5nQGNvbWNhc3QubmV0 >Return-Path: keithfang@comcast.net >X-OriginalArrivalTime: 10 Jun 2004 20:30:57.0117 (UTC) >FILETIME=[D5B970D0:01C44F29] > >Are you running client-server applications? Can the bottleneck be at the >Application servers or Web servers? If IDS is well tuned and running at >its full speed, and yet you are still CPU bound (CPU usage constantly at >90+%). It maybe time to upgrade the hardware. > >Keith > > > > Hi, > > > > Here is the output of onstat -p, buffers read/write looks good. >Checkpoint > > is always 0-2 secs, > > and checked couple of other things nowhere I can find the problem/issue. >I > > do have perf. manual for reference. > > We have fiber disk with cache 512 mb, we are planning to put 2 gb cache >its > > under consideration. > > O/S looks good except the oninit process which is eating 80-90 % of the > > power, this happens whenever we try to open window for more business, >and > > start getting bigger volume of transactions into the >database(irrespective > > of the time of the day) > > Backup takes place during night time. > > >------------------------------------------------------------------------------- - > > ------------------------------------------ > > Profile > > > > dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched > > 4593473 5000224 2424607883 99.81 2036594 3058089 113185276 98.20 > > > > isamtot open start read write rewrite delete commit > > rollbk > > 2418731304 35461283 17699289 2117912282 15758216 1343207 44413 >1181780 > > 5 > > > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > > 0 0 0 0 0 0 0 > > > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > > 0 0 0 35322.40 4795.57 19 7245 > > > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > > 525818 1 140500918 0 0 254 7932843 276854 > > > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > > 437940 834684 402338 1674814 4470596 > > >------------------------------------------------------------------------------- - > > ------------------------------------- > > Sushil... > > > > >From: NormaJean.Sebastian@tellabs.com > > >To: forum.subscriber@iiug.org, ids@iiug.org, sushilps@hotmail.com > > >Subject: RE: Re.slow response [3097] > > >Date: Thu, 10 Jun 2004 12:45:51 -0500 > > >MIME-Version: 1.0 > > >Received: from tellabs.com ([204.154.129.57]) by mc1-f9.hotmail.com >with > > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 10:47:27 -0700 > > >Received: from ([172.23.207.11])by mx4.tellabs.com with ESMTP ;Thu, 10 >Jun > > >2004 12:45:51 -0500 > > >Received: from localhost (root@localhost)by mailw01.hq.tellabs.com > > >(8.11.1/8.8.6) with ESMTP id i5AHjp302710;Thu, 10 Jun 2004 12:45:51 >-0500 > > >(CDT) > > >X-Message-Info: JGTYoYF78jEeFDEaX/cCYoJC6GHZkbHF > > >X-OpenMail-Hops: 1 > > >Message-Id: <H00016dd15cfcf99.1086889550.mail@MHS> > > >Return-Path: normajean.sebastian@tellabs.com > > >X-OriginalArrivalTime: 10 Jun 2004 17:47:27.0782 (UTC) > > >FILETIME=[FEE7B460:01C44F12] > > > > > > > > >how are you buffer hit ratios, and other standard "perf tuning" things > > >(do you have a perf tuning manual to review)? > > > > > >how about you disk system.... do you have caching at that level... >i.e., > > >we have EMC disk storage with a GB or so of cache... that's an extra > > >performance level to check. > > >o/s system running fine to? > > > > > >is your slow response ALL the time or at certain times... what else is > > >happening on the system at those times? (both the DB system and O/S)... > > >maybe backup interference? > > > > > > > > > > > > > > >-----Original Message----- > > >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] > > >Sent: Thursday, June 10, 2004 11:35 AM > > >To: ids@iiug.org; forum.subscriber@iiug.org > > >Subject: RE: Re.slow response [3097] > > > > > > > > >Hi, > > > > > >Basically we are talking about 6 core tables which we think might be >the > > > > > >problem, for these 6 tables we do have detached indexes in its own > > >dbspaces > > >and fragemented by round-robin which also has its own dbspace. > > > > > >sushil... > > > > > > >From: "Russell J. ...." <rclancy@rotech.com> > > > >To: ids@iiug.org > > > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 >11:28:37 > > > > > > >-0400 (EDT) > > > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by > > >mc3-s3.hotmail.com > > > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 >-0700 > > > >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com > > >with > > > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 > > > >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org > > > >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 > > >11:30:59 > > > >-0400 (EDT) > > > >Received: (from nobody@localhost)by ace.iiug.org > > >(8.12.10-14/8.12.8/Submit) > > > >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) > > > >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK > > > >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> > > > >Apparently-To: forum.subscriber@iiug.org > > > >Precedence: bulk > > > >Return-Path: nobody@ace.iiug.org > > > >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) > > > >FILETIME=[E0AF24C0:01C44F02] > > > > > > > >FWIW, you may want to detach the indexes onto separate disks. If you > > >decide > > > >to do that and know what your indexed data will look like, you might > > >want > > > >to > > > >instead also fragment by expression to even the I/O over the disks. > > > > > > > >Russ > > > > > > > >-----Original Message----- > > > >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On > > > >Behalf > > > >Of JHAYS2@sears.com > > > >Sent: Thursday, June 10, 2004 9:07 AM > > > >To: ids@iiug.org > > > >Subject: Re: Re.slow response [3093] > > > > > > > >Partition your most active tables over separate disks to spread out > > >the=20 > > > >I/O. > > > > > > > >Also, could you furnish more info from your config f

Sushil, Please paste your onconfig file here. Thanks. Ravi. "Sushil Shir...." <sushilps@hotmail.com> wrote: Hi Keith, Yeah we are working on application as well we are considering hardware too (appl and db servers). Sushil... >From: keithfang@comcast.net >To: "Sushil Shir...." >CC: ids@iiug.org, Subject: RE: Re.slow response [3099] Date: Thu, 10 Jun >2004 20:30:56 +0000 >Received: from rwcrmhc11.comcast.net ([204.127.198.35]) by >mc2-f10.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 >13:30:57 -0700 >Received: from 204.127.197.112 ([204.127.197.112]) by comcast.net >(rwcrmhc11) with SMTP id <20040610203050013009eplfe>; Thu, 10 Jun >2004 20:30:50 +0000 >Received: from [216.131.220.93] by 204.127.197.112;Thu, 10 Jun 2004 >20:30:56 +0000 >X-Message-Info: JGTYoYF78jEA3ZgVNHIpQyQzHZeKv5H6 >Message-Id: ><061020042030.21016.40C8C500000770DA00005218220075074409020E00089B070A05@comcas t.net> >X-Mailer: AT&T Message Center Version 1 (May 18 2004) >X-Authenticated-Sender: a2VpdGhmYW5nQGNvbWNhc3QubmV0 >Return-Path: keithfang@comcast.net >X-OriginalArrivalTime: 10 Jun 2004 20:30:57.0117 (UTC) >FILETIME=[D5B970D0:01C44F29] > >Are you running client-server applications? Can the bottleneck be at the >Application servers or Web servers? If IDS is well tuned and running at >its full speed, and yet you are still CPU bound (CPU usage constantly at >90+%). It maybe time to upgrade the hardware. > >Keith > > > > Hi, > > > > Here is the output of onstat -p, buffers read/write looks good. >Checkpoint > > is always 0-2 secs, > > and checked couple of other things nowhere I can find the problem/issue. >I > > do have perf. manual for reference. > > We have fiber disk with cache 512 mb, we are planning to put 2 gb cache >its > > under consideration. > > O/S looks good except the oninit process which is eating 80-90 % of the > > power, this happens whenever we try to open window for more business, >and > > start getting bigger volume of transactions into the >database(irrespective > > of the time of the day) > > Backup takes place during night time. > > >------------------------------------------------------------------------------- - > > ------------------------------------------ > > Profile > > > > dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched > > 4593473 5000224 2424607883 99.81 2036594 3058089 113185276 98.20 > > > > isamtot open start read write rewrite delete commit > > rollbk > > 2418731304 35461283 17699289 2117912282 15758216 1343207 44413 >1181780 > > 5 > > > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > > 0 0 0 0 0 0 0 > > > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > > 0 0 0 35322.40 4795.57 19 7245 > > > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > > 525818 1 140500918 0 0 254 7932843 276854 > > > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > > 437940 834684 402338 1674814 4470596 > > >------------------------------------------------------------------------------- - > > ------------------------------------- > > Sushil... > > > > >From: NormaJean.Sebastian@tellabs.com > > >To: forum.subscriber@iiug.org, ids@iiug.org, sushilps@hotmail.com > > >Subject: RE: Re.slow response [3097] > > >Date: Thu, 10 Jun 2004 12:45:51 -0500 > > >MIME-Version: 1.0 > > >Received: from tellabs.com ([204.154.129.57]) by mc1-f9.hotmail.com >with > > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 10:47:27 -0700 > > >Received: from ([172.23.207.11])by mx4.tellabs.com with ESMTP ;Thu, 10 >Jun > > >2004 12:45:51 -0500 > > >Received: from localhost (root@localhost)by mailw01.hq.tellabs.com > > >(8.11.1/8.8.6) with ESMTP id i5AHjp302710;Thu, 10 Jun 2004 12:45:51 >-0500 > > >(CDT) > > >X-Message-Info: JGTYoYF78jEeFDEaX/cCYoJC6GHZkbHF > > >X-OpenMail-Hops: 1 > > >Message-Id: > > >Return-Path: normajean.sebastian@tellabs.com > > >X-OriginalArrivalTime: 10 Jun 2004 17:47:27.0782 (UTC) > > >FILETIME=[FEE7B460:01C44F12] > > > > > > > > >how are you buffer hit ratios, and other standard "perf tuning" things > > >(do you have a perf tuning manual to review)? > > > > > >how about you disk system.... do you have caching at that level... >i.e., > > >we have EMC disk storage with a GB or so of cache... that's an extra > > >performance level to check. > > >o/s system running fine to? > > > > > >is your slow response ALL the time or at certain times... what else is > > >happening on the system at those times? (both the DB system and O/S)... > > >maybe backup interference? > > > > > > > > > > > > > > >-----Original Message----- > > >From: sushilps@hotmail.com [mailto:sushilps@hotmail.com] > > >Sent: Thursday, June 10, 2004 11:35 AM > > >To: ids@iiug.org; forum.subscriber@iiug.org > > >Subject: RE: Re.slow response [3097] > > > > > > > > >Hi, > > > > > >Basically we are talking about 6 core tables which we think might be >the > > > > > >problem, for these 6 tables we do have detached indexes in its own > > >dbspaces > > >and fragemented by round-robin which also has its own dbspace. > > > > > >sushil... > > > > > > >From: "Russell J. ...." > > > >To: ids@iiug.org > > > >Subject: RE: Re.slow response [3095] Date: Thu, 10 Jun 2004 >11:28:37 > > > > > > >-0400 (EDT) > > > >Received: from mc3-f18.hotmail.com ([64.4.50.154]) by > > >mc3-s3.hotmail.com > > > >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:50 >-0700 > > > >Received: from ace.iiug.org ([216.177.38.212]) by mc3-f18.hotmail.com > > >with > > > >Microsoft SMTPSVC(5.0.2195.6824); Thu, 10 Jun 2004 08:52:04 -0700 > > > >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org > > > >(8.12.10-14/8.12.8) with ESMTP id i5AFUtFX017545;Thu, 10 Jun 2004 > > >11:30:59 > > > >-0400 (EDT) > > > >Received: (from nobody@localhost)by ace.iiug.org > > >(8.12.10-14/8.12.8/Submit) > > > >id i5AFSbGM017455;Thu, 10 Jun 2004 11:28:37 -0400 (EDT) > > > >X-Message-Info: jl7Vrt/mfsoDVNgfMplM1uxeMTbYoYxK > > > >Message-Id: <200406101528.i5AFSbGM017455@ace.iiug.org> > > > >Apparently-To: forum.subscriber@iiug.org > > > >Precedence: bulk > > > >Return-Path: nobody@ace.iiug.org > > > >X-OriginalArrivalTime: 10 Jun 2004 15:52:05.0132 (UTC) > > > >FILETIME=[E0AF24C0:01C44F02] > > > > > > > >FWIW, you may want to detach the indexes onto separate disks. If you > > >decide > > > >to do that and know what your indexed data will look like, you might > > >want > > > >to > > > >instead also fragment by expression to even the I/O over the disks. > > > > > > > >Russ > > > > > > > >-----Original Message----- > > > >From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On > > > >Behalf > > > >Of JHAYS2@sears.com > > > >Sent: Thursday, June 10, 2004 9:07 AM > > > >To: ids@iiug.org > > > >Subject: Re: Re.slow response [3093] > > > > > > > >Partition your most active tables over separate disks to spread out > > >the=20 > > > >I/O