Re: Protect syscache from bloating with negative cache entries

At Mon, 25 Feb 2019 15:23:22 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20190225(dot)152322(dot)104148315(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
> I think the two parameters are to be tuned in the following
> steps.
>
> - If the default setting sutisfies you, leave it alone. (as a
> general suggestion)
>
> - If you find your (syscache-sensitive) query are to be executed
> with rather longer intervals, say 10-30 minutes, and it gets
> slower than shorter intervals, consider increase
> catalog_cache_prune_min_age to about the query interval. If you
> don't suffer process-bloat, that's fine.
>
> - If you find the process too much "bloat"s and you (intuirively)
> suspect the cause is system cache, set it to certain shorter
> value, say 1 minutes, and set the catalog_cache_memory_target
> to allowable amount of memory for each process. The memory
> usage will be stable at (un)certain amount above the target.
>
>
> Or, if you want determine the setting previously with rather
> strict limit, and if the monitoring feature were a part of this
> patchset, a user can check how much memory is used for the query.
>
> $ perl -e 'print "set track_catalog_cache_usage_interval = 1000;\n"; for (0..9999) { print "CREATE TABLE foo$_ PARTITION OF foo FOR VALUES WITH (MODULUS 10000, REMAINDER $_);\n"; } print "select sum(size) from pg_stat_syscache";' | psql
>
> sum
> ---------
> 7088523

It's not substantial, but the number is for
catalog_cache_prune_min_age = 300s, I had 12MB when it is
disabled.

perl -e 'print "set catalog_cache_prune_min_age to 0; set track_catalog_cache_usage_interval = 1000;\n"; for (0..9999) { print "CREATE TABLE foo$_ PARTITION OF foo FOR VALUES WITH (MODULUS 10000, REMAINDER $_);\n"; } print "select sum(size) from pg_stat_syscache";' | psql

sum
----------
12642321

> In this case, set catalog_cache_memory_target to 7MB and
> catalog_cache_memory_target to '1min'. Since the target doesn't
> work strictly (checked only at every resizing time), possibly
> you need further tuning.

regards.

-
Kyotaro Horiguchi
NTT Open Source Software Center