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