Re: Protect syscache from bloating with negative cache entries - Mailing list pgsql-hackers

On Wed, Jan 23, 2019 at 05:35:02PM +0900, Kyotaro HORIGUCHI wrote:
> At Mon, 21 Jan 2019 17:22:55 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> wrote
in<20190121.172255.226467552.horiguchi.kyotaro@lab.ntt.co.jp>
 
> > An option is an additional PGPROC member and interface functions.
> > 
> > struct PGPROC
> > {
> >  ...
> >  int syscahe_usage_track_interval; /* track interval, 0 to disable */
> > 
> > =# select syscahce_usage_track_add(<pid>, <intvl>[, <repetition>]);
> > =# select syscahce_usage_track_remove(2134);
> > 
> > 
> > Or, just provide an one-shot triggering function.
> > 
> > =# select syscahce_take_usage_track(<pid>);
> > 
> > This can use both a similar PGPROC variable or SendProcSignal()
> > but the former doesn't fire while idle time unless using timer.
> 
> The attached is revised version of this patchset, where the third
> patch is the remote setting feature. It uses static shared memory.
> 
> =# select pg_backend_catcache_stats(<pid>, <millis>);
> 
> Activates or changes catcache stats feature on the backend with
> PID. (The name should be changed to .._syscache_stats, though.)
> It is far smaller than the remote-GUC feature. (It contains a
> part that should be in the previous patch. I will fix it later.)

I have a few questions to make sure we have not made the API too
complex.  First, for syscache_prune_min_age, that is the minimum age
that we prune, and entries could last twice that long.  Is there any
value to doing the scan at 50% of the age so that the
syscache_prune_min_age is the max age?  For example, if our age cutoff
is 10 minutes, we could scan every 5 minutes so 10 minutes would be the
maximum age kept.

Second, when would you use syscache_memory_target != 0?  If you had
syscache_prune_min_age really fast, e.g. 10 seconds?  What is the
use-case for this?  You have a query that touches 10k objects, and then
the connection stays active but doesn't touch many of those 10k objects,
and you want it cleaned up in seconds instead of minutes?  (I can't see
why you would not clean up all unreferenced objects after _minutes_ of
disuse, but removing them after seconds of disuse seems undesirable.)
What are the odds you would retain the entires you want with a fast
target?

What is the value of being able to change a specific backend's stat
interval?  I don't remember any other setting having this ability.

-- 
  Bruce Momjian  <bruce@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 +