RE: Performance Monitoring/Tuning

Available tools to generate the UPDATE STATISTICS commands include: 1) Several tools in the IIUG Software Respository, including Art Kagel's "dostats". 2) Informix's Server Administartor (ISA) product which can be downloaded (for free) from http://www.intraware.com/informix. 3) http://www.objectsoft.com (Under Informix tips). -----Original Message----- From: Vinod Bhansali To: informix-list@iiug.org Sent: 6/15/00 4:46 PM Subject: RE: Performance Monitoring/Tuning Is there any tool to generate update statistics sql's as per hints given in Performance Guide? Vinod Bhansali >From: "Bernstein, Rick" <rbernste@alarismed.com> >Reply-To: Thomas Stainer <tstainer@styria.com> >To: "'Thomas Stainer '" <tstainer@styria.com>, >"'informix-list@iiug.org '" <informix-list@iiug.org> >Subject: RE: Performance Monitoring/Tuning >Date: Thu, 15 Jun 2000 12:25:30 -0700 > >You need to run UPDATE STATISTICS in the manner specified in the >release notes. There are several tools including Informix's ISA 1.20, >which can be used to generate most of the commands for you. > >The LRU_MIN_DIRTY and LRU_MAX_DIRTY parameters directly impact >checkpoint durations. Large numbers can cause most of your writes to be >deferred until checkpoint time. Run "onstat -F". >* LRU Writes are performed based on LRU_MIN/MAX_DIRTY >* Chunk Writes are performed during checkpoints. > >-----Original Message----- >From: Thomas Stainer >To: informix-list@iiug.org >Sent: 6/15/00 11:06 AM >Subject: Re: Performance Monitoring/Tuning > >i rerun UPDATE STATISTICS every night - what about UPDATE STATISTICS >MEDIUM or >HIGH? >initially my checkpoints took 0 - 2 seconds, meanwhile checkpoint >duration is >between 10 and 30 secs. >as far as the query plan is concerned i'm not shure it is really good. > >"Carlson@WHSmith" wrote: > > > Thomas Stainer wrote: > > > > > > As the users of our advertisement system are moaning about the bad > > > performance, i'm trying to find out why the system's perfomance >isn't as > > > good as it was in the beginning. Even after dropping and reimporting >the > > > whole database the performance was only slightly better. > > > > . . . and I'm presuming that you reran the required UPDATE STATISTICS? > > What ONCONFIG changes were made initially?? > > > > > I'm running > > > informix 7.30 UC6 on hp-ux 10.20, the rootdbs is located on the >system's > > > root-hd, the aps-dbspaces are lying in an AutoRaid (Raid5) striped >over > > > 8 discs - therefor fragmentation of the big tables won't better the > > > performance (or am i wrong)?. > > > > Depends, I guess. Is performance bad because the disks are busy or > > because the query plan is bad? > > > > > Does a change of LRU_MAX_DIRTY/LRU_MIN_DIRTY (actually 50/60) >improve > > > performance? > > > > When it comes to writing dirty pages to disk. How long are your > > checkpoints?? > > > > > The server isn't only db-server but also application server, how >many of > > > the (4) CPUs should by affinated? > > > > Didn't know that you could do that on HPUX . . . . > > > > > I somewhere read, the number of LRUs should equal the number of CPUs >of > > > the db-server - is this right? > > > > I believe that may be the 'official' Informix documentation version, >but > > it really depends on the number of BUFFERS that you have. If buffer > > contention exists, then you might need to increase the number of LRUs. > > > > > How can I generally monitor system's performance/throughput? > > > > I use glance and various onstat commands. > > > > > Furthermore the workload of the lun the dbspace are located on often > > > averages some 80 - 90 p.c. - may be this a reason for the bad > > > performance? > > > > > > > Depends on what the performance issues are. I've seen disks at 80-90% > > activity with decent response time. > > > > > thanx for help > > > > > > > If you would, could you please be a bit more specific concerning what > > the 'performance issues' are? > > > > -- > > John Carlson > > Informix DBA > > WHSmith USA > > > > #include std_disclaimer.h /* These are my opinions, not my >company's > > opinion */ > >-- >------------------------------------------------- >Thomas Stainer - tstainer@styria.com >open-it informationsberatungsges.m.b.h. & co kg >Schonaugasse 64 TEL: +43-316-875-3045 >8010 Graz, Austria FAX: +43-316-875-3034 > ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com