Re: Protect syscache from bloating with negative cache entries

Hi,

On 2018-03-01 15:19:26 -0500, Robert Haas wrote:
> On Thu, Mar 1, 2018 at 3:01 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > On 2018-03-01 14:49:26 -0500, Robert Haas wrote:
> >> On Thu, Mar 1, 2018 at 2:29 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> >> > Right. Which might be very painful latency wise. And with poolers it's
> >> > pretty easy to get into situations like that, without the app
> >> > influencing it.
> >>
> >> Really? I'm not sure I believe that. You're talking perhaps a few
> >> milliseconds - maybe less - of additional latency on a connection
> >> that's been idle for many minutes.
> >
> > I've seen latency increases in second+ ranges due to empty cat/sys/rel
> > caches.
>
> How is that even possible unless the system is grossly overloaded?

You just need to have catalog contents out of cache and statements
touching a few relations, functions, etc. Indexscan + heap fetch
latencies do add up quite quickly if done sequentially.

> > I don't think that'd quite address my concern. I just don't think that
> > the granularity (drop all entries older than xxx sec at the next resize)
> > is right. For one I don't want to drop stuff if the cache size isn't a
> > problem for the current memory budget. For another, I'm not convinced
> > that dropping entries from the current "generation" at resize won't end
> > up throwing away too much.
>
> I think that a fixed memory budget for the syscache is an idea that
> was tried many years ago and basically failed, because it's very easy
> to end up with terrible eviction patterns -- e.g. if you are accessing
> 11 relations in round-robin fashion with a 10-relation cache, your
> cache nets you a 0% hit rate but takes a lot more maintenance than
> having no cache at all. The time-based approach lets the cache grow
> with no fixed upper limit without allowing unused entries to stick
> around forever.

I definitely think we want a time based component to this, I just want
to not prune at all if we're below a certain size.

> > If we'd a guc 'syscache_memory_target' and we'd only start pruning if
> > above it, I'd be much happier.
>
> It does seem reasonable to skip pruning altogether if the cache is
> below some threshold size.

Cool. There might be some issues making that check performant enough,
but I don't have a good intuition on it.

Greetings,

Andres Freund