Hi, On 2018-03-01 15:19:26 -0500, Robert Haas wrote: > On Thu, Mar 1, 2018 at 3:01 PM, Andres Freund <and...@anarazel.de> wrote: > > On 2018-03-01 14:49:26 -0500, Robert Haas wrote: > >> On Thu, Mar 1, 2018 at 2:29 PM, Andres Freund <and...@anarazel.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