Re: Protect syscache from bloating with negative cache entries

On Fri, Jan 25, 2019 at 08:14:19AM +0000, Tsunakawa, Takayuki wrote:
> Hi Horiguchi-san, Bruce,
>
> From: Bruce Momjian [mailto:bruce(at)momjian(dot)us]
> > I suggest you go with just syscache_prune_min_age, get that into
> > PG 12, and we can then reevaluate what we need. If you want to
> > hard-code a minimum cache size where no pruning will happen, maybe
> > based on the system catalogs or typical load, that is fine.
>
> Please forgive me if I say something silly (I might have got lost.)
>
> Are you suggesting to make the cache size limit system-defined and
> uncontrollable by the user? I think it's necessary for the DBA to
> be able to control the cache memory amount. Otherwise, if many
> concurrent connections access many partitions within a not-so-long
> duration, then the cache eviction can't catch up and ends up in OOM.
> How about the following questions I asked in my previous mail?
>
> ----------------------------------------------------------------------
> This is a pure question. How can we answer these questions from
> users?
>
> * What value can I set to cache_memory_target when I can use 10 GB for
> * the caches and max_connections = 100? How much RAM do I need to
> * have for the caches when I set cache_memory_target = 1M?
>
> The user tends to estimate memory to avoid OOM.

Well, let's walk through this. Suppose the default for
syscache_prune_min_age is 10 minutes, and that we prune all cache
entries unreferenced in the past 10 minutes, or we only prune every 10
minutes if the cache size is larger than some fixed size like 100.

So, when would you change syscache_prune_min_age? If you reference many
objects and then don't reference them at all for minutes, you might want
to lower syscache_prune_min_age to maybe 1 minute. Why would you want
to change the behavior of removing all unreferenced cache items, at
least when there are more than 100? (You called this
syscache_memory_target.)

My point is I can see someone wanting to change syscache_prune_min_age,
but I can't see someone wanting to change syscache_memory_target. Who
would want to keep 5k cache entries that have not been accessed in X
minutes? If we had some global resource manager that would allow you to
control work_mem, maintenance_work_mem, cache size, and set global
limits on their sizes, I can see where maybe it might make sense, but
right now the memory usage of a backend is so fluid that setting some
limit on its size for unreferenced entries just doesn't make sense.

One of my big points is that syscache_memory_target doesn't even
guarantee that the cache will be this size or lower, it only controls
whether the cleanup happens at syscache_prune_min_age intervals.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +