Performance Basics

From majordom@e-math.ams.org  Sat Jan 24 01:21:03 1998

At 11:39 AM 1/23/98 +0100, A.M. De Jong wrote:
>Is there any checklist of things that should be investigated
>(and in what order) when an installation has performance
>problems.
>(For instance: sysmod, optimizedb, modify, etc)
>Is it wise to use II_EMBED_SET possibility for investigating
>output and what will be the best way to use this ?

All the consultant-types like myself are madly typing "That's what you
pay people like us for!"

Seriously, there is no cookbook.  The first thing to do is get better
data.  Is the system always slow?  Sometimes?  When?  Doing any one
specific thing?  Is it ever fast enough?  Is it ever fast enough with
a full user load?  Can slow periods be correlated with any particular
report or process?

If the system is just generally slow, you may have a hardware issue.
Do some system stats and see if the CPU is buried, or if the disks are
saturated.  See if you have enough memory.

If there are certain things that are slow, you have to look at the
database and application.  Yes, II_EMBED_SET can be quite useful for
highlighting problem queries.  Also, decide whether or not the problem
is really a locking problem.  I use some scripts that dig out the
10 longest-running queries from each session for a day (using II_EMBED_SET
output).  It's usually pretty easy to spot frequently recurring queries
that are dogging the system, and similar problems.

Sysmod rarely makes a substantial difference in overall performance,
in my experience.  Optimizedb can make a big difference but you have to
know what columns you need stats on, which in turn means you have to know
what queries are slow and why.  (ie, you need QEP's).  Table modifys
can make a big difference sometimes.  More frequently, you have to
dig into the application and change it, or change the database structure
in some way to better support the application.  And sometimes your
hardware is simply underpowered.


Karl R. Schendel, Jr.
K/B Computer Associates   schendel@kbcomputer.com
Ingres and Unix Expertise

NAIUA Member-At-Large     atlarge@naiua.org




From: Roy Hann 
Subject: Re: IngresII


>> What I will tell you is that it is easy to make Ingres II slow and that the
>> default configuration parameters are just plain goofey.  In fact that goes
>> for most versions of Ingres. I am not exaggerating when I say that 9 out of
>> 10 Ingres installations I look at are horribly mis-configured.
>
>Can you give me some pointers to material on this?


Unfortunately I can't, other than the general comments I will make below.
Obviously there is some information in the manuals, but nowhere near enough.
There is also some information in some of the CA training materials, but
unless you go on the courses you won't be able to get hold of that.  (In the
UK I can recommend the High Performance course (PERFII), which I
occasionally teach.  I don't think that course is offered in North
America--even though Karl would like it to be.)  Third-parties such as DMT
(www.dmt.com) have very detailed and informative training materials too, and
their courses are available in North America.

The topic is too huge to tell you everything you need to know here, but I'll
give you a few pointers. Performance comes down to two major issues: disk
I/O and locking.  Ultimately you have to have a good physical database
design and well designed transactions.  You also have to have a well tuned
server.  The usual dogma is that tuning will get the best performance out of
a good system, but it won't make a bad system good.  That is true, but I
would also say that a really, really badly configured server will make even
a good system do badly, and unfortunately the default configuration that you
get when Ingres installs is really quite bad indeed.  I regularly see
systems with a substantial fraction of a gigabyte of RAM, and Ingres is
using 700K for example!

Look at your cache hit rate (use trace point dm420 to get the buffer manager
statistics).  Obviously you want a high hit-rate.  "High" means REALLY high.
It is hard to get a hit-rate less than about 85%--you should regard that as
a poor hit-rate.  You want something in excess of 97%.  Add more single page
buffers to increase the ratio of HITS to FIX-CALLS.   If GREADS is high, add
more group buffers too.

Look at the lockstat header, and look at the number of lock waits and lock
escalations.  Set MAXLOCKs higher to reduce the rate at which you accumulate
lock waits.  Remember that each wait could be very long because the Ingres
default is to wait forever.

There is WAY more to say about this, but I've already spent 20 minutes of my
finite and rapidly diminishing lifetime on this message, so I'll stop there.
:-)  Hope it helps!

Roy Hann
Rational Commerce Ltd.
Ingres Q & A
Back to William's Home Page
Email William