micha@ozemail.com.au (Micha Petrovic) writes:
: Hi,
: 
: We are using Ingres on a sun (SunOS) box and are looking for some ideas 
: to improve the performance of our Ingres apps. It looks like all our apps 
: are IO bound so some ideas for tuning Ingres and/or UNIX for io 
: performance would be appreciated.
:   
: So far we have tryed an external SCSI cache (64Mb) which seemed to 
: improve the speed by ~ 15 %. 

Where to begin?  

You don't give us a lot to go on, but I'll give some general ideas:

1. Review your queries.  Turn on QEP display and TRACE POINT QE90.
   See if the plans are good.
2. Add secondary indices if indicated; add data to the indices if 
   that makes sense.
3. Make sure you have statistics (run optimizedb).
4. Keep your log file on a dedicated raw device.
5. Get lots of small, fast disc drives
6. Get controllers that can do seek-order optimization and can scale
   up it's throughput as discs are added (these are not as common as
   they should be).
7. Get lots of RAM.

This doesn't even scratch the surface of what you can and should do.
The main point I would take from the above is to check the way Ingres
is working before you start buying hardware.  A hardware solution to 
a tuning problem is poor value for money and won't help much anyway.
Once you KNOW it's a hardware problem, then by all means buy some.

I find that just about every slow I/O bound query is slow and I/O
bound for a totally different reason than the one before.  It is hard
to generalize.

--Roy Hann
jrjones@winternet.com (James R. Jones) wrote:


>1. Users are currently complaining that their queries to the datbase have 
>increased.  What tables can I look at to see were the bottle neck is or 
>should I run sysmod program on the database?
[Before you go much further, get hold of the Ingres FAQ from:
          ftp.adc.com   in /pub/ingres
And be prepared to spend time with the DBA Manual.]

There is no simple answer to this.  Ingres doesn't keep runtime stats
on a per-query basis.  Essentially, each "slow" query must be analyzed
individually.  The most useful tool for this is "set qep", which will
show you the query plan. 

It sometimes happens that locking is a problem; to check for this, the
Ingres "ipm" utility is very useful, and you should be checking the
Ingres error log for "lock escalations", which will degrade
performance very badly.

>2. How often should sysmod be run?
 "Sysmod" should be run occasionally, (in a development shop, weekly
would be nice) but you should understand that it only optimizes the
Ingres system tables.  If you aren't adding/dropping lots of views,
tables, forms, etc., it won't help you.

>3. Should the optimize utility be run on regular bases?
YES!  Do key-only optimization (using the "-zk" flag) on your large or
heavily-used tables.  "optimizedb" produces statistics for the query
optimizer to use -- and it _does_ use them.

>4. Do indexes have to be reorganized periodically and if so what is the 
>best way to do this.
Indices should be BTREE unless there's a compelling reason to use some
other file structure.  The Ingres default is ISAM, which often
degrades over time as overflow chains build up.  Use "modify to merge"
(I think...) to recover space lost through deletions.  Mostly, BTREE
indices take care of themselves.

>5. Are there any ftp sites that have programs which will help be to tune 
>and understand the Ingres database, structures, indexes, etc.  I am 
>looking for DBA tools.
From the FAQ:
    A small number of INGRES tools (including the NAIUA tools) are 
    available by anonymous ftp from: 
      ftp.adc.com (/pub/ingres)
>Any info would be appreciated.  We are running Ingres, not open ingres on 
>a VMS system and it is the latest version.  Ingres last upgraded when vms 
>was upgraded to 6.1.
Good luck!  There's a lot to learn.

Ingres Q & A

To William's Home Page

© William Yuan 2000

Email William