Re: Protect syscache from bloating with negative cache entries

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?

>> Anyway, I don't mind making the exact timeout a GUC (with 0 disabling
>> the feature altogether) if that addresses your concern, but in general
>> I think that it's reasonable to accept that a connection that's been
>> idle for a long time may have a little bit more latency than usual
>> when you start using it again.
>
> 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.

> 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company