Hi >> > 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. >
In current ver8 patch there is a stats view representing age class distribution. https://www.postgresql.org/message-id/20181019.173457.68080786.horiguchi.kyotaro%40lab.ntt.co.jp Does it help DBA with tuning cache_prune_age and/or cache_prune_target? If the amount of cache entries of older age class is large, are people supposed to lower prune_age and not to change cache_prune_target? (I get confusion a little bit.) Regards, Takeshi Ideriha
