Re: Protect syscache from bloating with negative cache entries

At Wed, 30 Jan 2019 05:06:30 +0000, "Ideriha, Takeshi" <ideriha(dot)takeshi(at)jp(dot)fujitsu(dot)com> wrote in <4E72940DA2BF16479384A86D54D0988A6F4156D4(at)G01JPEXMBKW04>
> >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?

Definitely. At least DBA can see nothing about cache usage.

> 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.)

This feature just removes cache entries that have not accessed
for a certain time.

If older entries occupies the major portion, it means that
syscache is used effectively (in other words most of the entries
are accessed frequently enough.) And in that case I believe
syscache doesn't put pressure to memory usage. If the total
memory usage exceeds expectations in the case, reducing pruning
age may reduce it but not necessarily. Extremely short pruning
age will work in exchange for performance degradation.

If newer entries occupies the major portion, it means that
syscache may not be used effectively. The total amount of memory
usage will be limited by puruning feature so tuning won't be
needed.

In both cases, if pruning causes slowdown of intermittent large
queries, cache_memory_target will alleviate the slowdown.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center