RE: Performance Monitoring/Tuning
Vinod Bhansali asked for a tool that generates UPDATE STATISTICS statements following the Performance Guide hints. Art S. Kagel pointed to his dostats.ec in the utils2_ak package from the IIUG Software Repository. Roland Wintgen added that for those without ESQL/C, the ISA package from Intraware contains an SQL script that generates the appropriate UPDATE STATISTICS statements.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
Vinod Bhansali wrote: > > Is there any tool to generate update statistics sql's as per hints given in > Performance Guide? [SNIP] Yes, several, including my own dostats.ec which is in the package utils2_ak available from the IIUG Software Repository (www.iiug.org.software). Art S. Kagel
Vinod Bhansali wrote: > > Is there any tool to generate update statistics sql's as per hints given in > Performance Guide? [SNIP] Yes, several, including my own dostats.ec which is in the package utils2_ak available from the IIUG Software Repository (www.iiug.org.software). Art S. Kagel
"Art S. Kagel" schrieb: > > Vinod Bhansali wrote: > > > > Is there any tool to generate update statistics sql's as per hints given in > > Performance Guide? > [SNIP] > > Yes, several, including my own dostats.ec which is in the package utils2_ak > available from the IIUG Software Repository (www.iiug.org.software). > > Art S. Kagel For all those, who haven't got ESQL/C to compile Art's dostats, there is an alternative. In the ISA-Package, available from Intraware, there is a SQL-script that generates the appropiate UPDATE STATISTICS. -- Roland Wintgen (Systemadministrator) ### # # ### # # # # # EVG Martens GmbH & Co. KG Tel. : +49 2166 550823 # ### # # ### # Trompeterallee 244-246 Fax : +49 2166 550890 # # # # # D-41189 Moenchengladbach e-mail: rw@evg.de ### # ###