On Fri, Jan 25, 2019 at 08:14:19AM +0000, Tsunakawa, Takayuki wrote: > Hi Horiguchi-san, Bruce, > > From: Bruce Momjian [mailto:br...@momjian.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 <br...@momjian.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 +