Re: Protect syscache from bloating with negative cache entries

At Wed, 13 Feb 2019 02:15:42 +0000, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote in <0A3221C70F24FB45833433255569204D1FB97CF1(at)G01JPEXMBYT05>
> From: Tomas Vondra [mailto:tomas(dot)vondra(at)2ndquadrant(dot)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 item would 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 system catalogs. 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