>i have a SELECT COUNT(*) in one of my servers, that is frequently called
>and always will get the same value. Now they want me i copy the DB to memory
>to don't waste performance. But i hate to copy a DB to memory. Is there any
>way the DB to tell for a certain SQL command to keep retrieved data in
>cache, so that i could use a functionality that is already there and so not
>re-invent the wheel ?


Ingres does what you are asking for by default--you can't stop it cacheing
recently used data.  However, you may have to increase the size of your
cache--in this case the number of group buffers--to keep more data in
memory.  For scanning-intensive applications I have recently been allocating
up to 90% of my DMF in the form of group buffers.  (I'd much rather the apps
didn't scan tables, but the code is a given.)  When Ingres installs, it
configures a pathetically small number of group buffers by default (IMO),
though that may be because the way group buffers are re-used changed in a
late version of 6.4 and no one ever got around to changing the installation
procedure.   Depending on your version of Ingres you will either edit
rundbms.opt, or use CBF to change the number (and perhaps size) of group
buffers.

One other thing that you may like to do is change COUNT(*) to COUNT(1),
which can sometimes be much faster and gives the same result.

Roy Hann
BKB Client/Server (UK) Ltd.



>    Before expanding the size of your cache though you really should start
>    monitoring the existing caches performance with trace point dm420. Look
>    specifically at the HITS/FIX ratio. This indicates how often the servers
>    went looking for a page in cache and found it. Numbers over 90% would
>    indicate that your cache is plenty big enough already.


You are close, but not quite completely there yet.  A high cache-hit ratio
will be reported any time you are doing table-scanning (as Peter is), even
if your cache is too small.  The first time you look for a page and don't
find it, you get a cache-miss.  Ingres then does a group read (of let's say
8 pages).  The next 7 reads are guaranteed to be cache-hits, so you will get
a cache-hit rate of 88% which looks quite good, but you've got it for the
wrong reasons.  If you read more pages in a group read, your hit rate looks
even better, but it's still for the wrong reason.  Any time I see a high
cache-hit rate, the first thing I look for is a high ratio of GREADS to
READS.  About a month ago I saw a system that was doing over 100 GREADS for
each READ--the cache hit rate looked great but performance was very bad
indeed.

>    Finally, If this is a part of a performance tuning exercise than I can
>    honestly say thay I have rarely received much gain by expanding the cache
>    to some dramatic level. Generally, there are bigger benefits to be gained
>    by looking elsewhere first.


This is absolutely correct for single-page buffers.  Having even a small
cache gets you most of the benefit of having a cache at all--and that is
true of all caches not just Ingres.  Roughly, you use 20% of the data 80% of
the time, and within that 20% there is another 20% that you use 80% of the
time.  The point of diminishing returns is reached very quickly.  BUT.  But,
as I mentioned in an earlier post, Ingres now re-uses group buffers, and for
table-scanning applications you can achieve very dramatic improvement in
performance by allocating LOTS of very big group buffers.  One app that I
looked at a year ago went from 15 hours down to 1 hour after I allocated
300Mb of group buffers (versus the original 1.6Mb).  It's still a piss-poor
application, but now it goes like blazes and the users are happy.

Roy Hann
BKB Client/Server (UK) Ltd.

Ingres Q & A

Back to William's Home Page

© William Yuan 2000

Email William