Re: Protect syscache from bloating with negative cache entries

At Wed, 13 Feb 2019 02:15:42 +0000, "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com> wrote in
<0A3221C70F24FB45833433255569204D1FB97CF1@G01JPEXMBYT05>
> From: Tomas Vondra [mailto:tomas.vondra@2ndquadrant.com]
> > > I didin't consider planning that happen within a function. If
> > > 5min is the default for catalog_cache_prune_min_age, 10% of it
> > > (30s) seems enough and gettieofday() with such intervals wouldn't
> > > affect forground jobs. I'd choose catalog_c_p_m_age/10 rather
> > > than fixed value 30s and 1s as the minimal.
> > >
> > 
> > Actually, I see CatCacheCleanupOldEntries contains this comment:
> > 
> > /*
> >  * Calculate the duration from the time of the last access to the
> >  * "current" time. Since catcacheclock is not advanced within a
> >  * transaction, the entries that are accessed within the current
> >  * transaction won't be pruned.
> >  */
> > 
> > which I think is pretty much what I've been saying ... But the question
> > is whether we need to do something about it.
> 
> Hmm, I'm surprised at v14 patch about this.  I remember that previous patches renewed the cache clock on every
statement,and it is correct.  If the cache clock is only updated at the beginning of a transaction, the following TODO
itemwould not be solved:
 
> 
> https://wiki.postgresql.org/wiki/Todo

Sorry, its just a stale comment. In v15, it is alreday.... ouch!
still left alone. (Actually CatCacheGetStats doesn't perform
pruning.)  I'll remove it in the next version. It is called in
start_xact_command, which is called per statement, provided with
statement timestamp.


> /*
>  * Calculate the duration from the time from the last access to
>  * the "current" time. catcacheclock is updated per-statement
>  * basis and additionaly udpated periodically during a long
>  * running query.
>  */
> TimestampDifference(ct->lastaccess, catcacheclock, &entry_age, &us);


> " Reduce memory use when analyzing many tables in a single command by making catcache and syscache flushable or
bounded."

In v14 and v15, addition to it a timer that fires with the
interval of catalog_cache_prune_min_age/10 - 30s when the
parameter is 5min - updates the catcache clock using
gettimeofday(), which in turn is the source of LRU timestamp.

> Also, Tom mentioned pg_dump in this thread (protect syscache...).  pg_dump runs in a single transaction, touching all
systemcatalogs.  That may result in OOM, and this patch can rescue it.
 

So, all the problem will be addressed in v14.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center