[Info-ingres] 4k Cache

[Roy Hann]

Steve wrote:

> On Thursday, 9 July 2020 18:49:06 UTC+10, Roy Hann  wrote:

[snip]

>> Being private was claimed to give a
>> performance benefit. (Personally I never saw a system where that would
>> have been anywhere near the top of the list of performance drags.
>
> Great, thanks Roy. 
>
> Be interested to know what your list of top performance drags are.
>
> Steve

Not knowing your job mix (transaction processing versus
reporting/batch/analytics) it's hard to be very specific. That said,
just picking on a couple of the usual suspects...

Way out in front: row-at-a-time processing, followed by its
close relative: "key hopping" (having to navigate from table to table
to table because someone thought using row IDs and not migrating keys
were both good ideas). Monotonic (sequential) key values in B-trees
are deadly too if you're using locking rather than MVCC. 

There's not much you can do about any of those once they're baked in.
Unfortunately that is the nature of performance. It has to be designed
in. It can't be bolted on afterwards. There are a few knobs you can
twiddle to make a things a bit better though.

Enlarge your DMF caches to the extent possible. Historically Ingres was
(IMO) too timid about grabbing all the memory on a machine. In your
specific case I am a little uneasy about using the 4k cache. For
various reasons it is an unlikely win. I'd be looking at 8k or 16k
usually, being mindful of lock-contention problems (see above). 

More and bigger group buffers can give a spectacular boost to some
processing, at the price of erratic performance--when the group
buffers work well they work very well, and when they don't they don't
work at all. Since users hate erratic more than slow you might find
you have to dial them back down again.

Generate appropriate statistics so the optimizer can avoid bad
decisions. Running optimizedb -zk is harmless but usually just confirms
assumptions the optimizer would have used anyway. You want stats on
non-key columns used in constant restrictions (e.g. WHERE foo=123), and
surprisingly often it will be the most mundane-looking of these columns
that turn out to make the biggest difference.

If transaction logging is your bottleneck it will affect everything.
Using logstat -statisics will tell you. 

This topic is way too big for a newsgroup post.  :-)

Roy