Re: Protect syscache from bloating with negative cache entries

From: Andres Freund <andres(at)anarazel(dot)de>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, David Steele <david(at)pgmasters(dot)net>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Craig Ringer <craig(at)2ndquadrant(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Protect syscache from bloating with negative cache entries
Date: 2018-03-01 20:26:30
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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.


Andres Freund

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2018-03-01 20:29:22 Re: In reference to gsoc
Previous Message Robert Haas 2018-03-01 20:19:26 Re: Protect syscache from bloating with negative cache entries