At Fri, 25 Jan 2019 08:14:19 +0000, "Tsunakawa, Takayuki" <tsunakawa.ta...@jp.fujitsu.com> wrote in <0A3221C70F24FB45833433255569204D1FB70EFB@G01JPEXMBYT05> > 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?
cache_memory_target does the opposit of limiting memory usage. It keeps some amount of syscahe entries unpruned. It is intended for sessions on where cache-effective queries runs intermittently. syscache_prune_min_age also doesn't directly limit the size. It just eventually prevents infinite memory consumption. The knobs are not no-brainer at all and don't need tuning in most cases. > -------------------------------------------------- > 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. > -------------------------------------------------- You don't have a direct control on syscache memory usage. When you find a queriy slowed by the default cache expiration, you can set cache_memory_taret to keep them for intermittent execution of a query, or you can increase syscache_prune_min_age to allow cache live for a longer time. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
