[Info-ingres] Effective Ingres Caching

[Roy Hann]

Thursday, January 4, 2018, 10:03:28 PM, you wrote:

> I'm looking for some advice on cache sizes in Ingres.

> The following is the output of "top" for my current Ingres installation:

>  
>    PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
>   4601 ingres    20   0 7430084 2.144g  33984 S 431.3  7.8   1985:18 iidbms
>   
> If I use pmap to look at the memory usage of the process, a la
> "pmap -x 4601" it seems Ingres is using  7430084kb of memory, which is...8gb-ish.

> I have a lot more than 8GB of RAM, though.  I've got 32GB, and I'd
> like to use a much larger portion of the available memory for
> logging.  I could pretty easily field 96GB if there was a reason to.

> I currently have the cache guideline for each of the caches set to
> "huge", which is the largest of the guidelines.

[snip]

You have all the caches configured to be huge, which may be
fine, but are you using all those different page sizes?

If you don't have any tables using 64kb pages the 64kb cache will be
sitting there empty. I only question this because it would be unusual
to find a site that uses all the available page sizes. Most sites use
2kb pages and maybe 8kb or 16kb.

Using well-chosen page sizes is good. There is no reason not to use
all of them. It is just that it is hard to work through all the
trade-offs (e.g. large numbers of rows put off-line with a page level
lock on a 64kb page versus large numbers of rows read with a single
DIO). People don't do it. But if they did they could use more DMF
cache effectively.

[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?

It might help, if that was the problem. You should take a look at the
output from trace point dm420 to guide you. Take a look at the summary
stats for each page size. FIX CALLS is the number of times Ingres
looked for a page in cache; HITS is the number of times it found it.
If it doesn't find it that will generally result in a READ or a GREAD
(from the file system). As Karl points out, that doesn't necessarily
mean a read from physical disk, but it is a cache miss.

READs are single page reads. GREADs are group reads (preemptive
read-ahead). GREADs are often used for table scanning. Lots of GREADs
relative to READs means lots of table scanning relative to keyed
look-ups.

You need to know what your system is doing and why. If you don't
expect to be doing a lot of table scanning but it turns out you are,
you may have a problem. (Or you may not. Table scanning can be the
efficient way to execute a query. Intuition is not a good guide.)

One other thing to keep in mind about group reads is the way they
affect your calculated hit rate (HITS/FIX CALLS*100%). If you use 8
page group buffers and you're doing a lot of scanning, only the first
page that triggers the group read will count as a cache miss. The
next 7 will be hits. So you are unlikely ever to see a hit rate lower
than 87.5%. Or to put it differently, you will need to see a hit rate
in the high 90% region before you might feel the difference.

> 2.  Are there negative consequences to setting this value too high?
> 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?

Maybe. But Ingres keeps track of which pages are actually being used
so that it knows which ones to throw out to make room to read a new
page. You would have to be confident you can guess the priority better
than Ingres can measure it. I don't think I could. I'd leave the cache
priority strictly alone in general.

[snip]

> 4.  I realize this is super-general, but what do people generally
> consider "good" cache hit rates?

Values MUCH higher than 87.5%.

Roy Hann
Rational Commerce Ltd.
T +44 20 8691 2089
"Ingres development, tuning and training experts"