[Info-ingres] Effective Ingres Caching

[Karl and Betty Schendel]

> On Jan 4, 2018, at 5:03 PM, Michel Forget <mvf at cyberren.com> wrote:
> 
> Hi,
> 
> I'm looking for some advice on cache sizes in Ingres.
> [snip]
> So, some specific questions I am hoping to find answers to:
> 
> 1.  Is increasing dmf_cache_size the correct approach toward the goal of consuming more memory and increasing overall read performance by not having to hit the physical disk?

Yes, up to a point.  dmf_cache_size only affects the single-page area, and one may
also want to add group buffers; dmf_group_size to make them larger and dmf_group_count
to add more of them.  The "right" group size depends on usage patterns, but in general
I'd try to adjust it so that groups work out to anywhere between 64k and 512k.  I would
generally not go much larger because the penalty for tossing a group becomes very high,
and group management is not coded with large (100's of pages) groups in mind.

> 2.  Are there negative consequences to setting this value too high?

Absolutely.  The cache algorithms and code were written with cache size of a
few-thousand to few-tens-of-thousand pages in mind.  It was never optimized for many
tens-of-thousands and up.  (The code is better at large caches than it used to be,
back in the version 2.x and earlier days, but it's still not good at huge numbers of pages.)

Part of the reason for this is that Ingres takes advantage of another cache which you
aren't seeing directly, and that's the filesystem cache.  Ingres doesn't use raw or
direct I/O, unless you tell it to.  My rough rule of thumb is to set the cache size so that
it's in the few-tens-of-thousands of pages range, and a few thousand groups,
and let the OS filesystem cache do the rest.  The filesystem cache is generally better
designed for using up many gigabytes of memory.

(This isn't conventional DBMS wisdom, but conventional DBMS wisdom also assumes
either raw/direct I/O, or massive tablespaces where the underlying table structure is
hidden from the OS filesystem.  Ingres never did it that way, and it can use the filesystem
cache a lot more effectively because of it.)


> 3.  I've noticed that the output of "help table sometable;" shows a field called "cac! he priority".  If I know which of my tables I consider to be most cache-worthy, can I use this setting to purposely bias the cache in favor of the tables I care about?  If so, how can I do that?  I've never seen anything that indicates how I could set this. 

modify tablename to priority=N
or modify tablename to <structure> with priority=N
N can be 1 to 8 (low to high).

You have to be pretty careful with this.  I personally don't think the implementation of this
feature was as good as it should have been.  I'd use it sparingly and on smallish tables.  Also, I
suspect that the only useful value is 8;  I don't think you'll get much good out of setting a table's
cache priority to (say) 2 or 3.

> 4.  I realize this is super-general, but what do people generally consider "good" cache hit rates?  I mean, if I keep throwing more memory at the cache can I reasonably expect to drive cache hit rates up to values approaching 100% (ie: if the cache were larger than the actual database itself)?

Again, very very rough rule of thumb, but above 90% is usually more or less OK, while below
90% needs some thought.  Below say 60% needs some real 'splainin;  it might in fact be
the best one can do given some usage patterns, so it's not an automatic "make the cache
bigger".  Also, remember that hit rates aren't everything, a 90% hit rate is no good if the
cache has become so bloated that every reclaim/replace takes milliseconds of CPU time.

By the way, I don't think you'll ever hit 100% because of the way it handles core catalogs.
I haven't experimented, and I could be wrong, but I'd be surprised to see a pure 100%
cache hit rate except under artificial circumstances such as no DDL, read-mostly load,
and either no session connects or all databases (including iidbdb) pinned open for the duration.

Karl