Re: Protect syscache from bloating with negative cache entries

From: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
To: tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com
Cc: bruce(at)momjian(dot)us, GavinFlower(at)archidevsys(dot)co(dot)nz, robertmhaas(at)gmail(dot)com, tgl(at)sss(dot)pgh(dot)pa(dot)us, pgsql-hackers(at)lists(dot)postgresql(dot)org, alvherre(at)alvh(dot)no-ip(dot)org, andres(at)anarazel(dot)de, michael(dot)paquier(at)gmail(dot)com, david(at)pgmasters(dot)net, Jim(dot)Nasby(at)bluetreble(dot)com, ideriha(dot)takeshi(at)jp(dot)fujitsu(dot)com, craig(at)2ndquadrant(dot)com
Subject: Re: Protect syscache from bloating with negative cache entries
Date: 2019-02-06 05:43:34
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

At Tue, 5 Feb 2019 02:40:35 +0000, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote in <0A3221C70F24FB45833433255569204D1FB93A16(at)G01JPEXMBYT05>
> From: bruce(at)momjian(dot)us [mailto:bruce(at)momjian(dot)us]
> > On Mon, Feb 4, 2019 at 08:23:39AM +0000, Tsunakawa, Takayuki wrote:
> > > Horiguchi-san, Bruce, all, So, why don't we make
> > > syscache_memory_target the upper limit on the total size of all
> > > catcaches, and rethink the past LRU management?
> >
> > I was going to say that our experience with LRU has been that the
> > overhead is not worth the value, but that was in shared resource cases,
> > which this is not.
> That's good news! Then, let's proceed with the approach involving LRU, Horiguchi-san, Ideriha-san.

If you mean accessed-time-ordered list of entries by "LRU", I
still object to involve it since it is too complex in searching
code paths. Invalidation would make things more complex. The
current patch sorts entries by ct->lastaccess and discards
entries not accessed for more than threshold, only at doubling
cache capacity. It is already a kind of LRU in behavior.

This patch intends not to let caches bloat by unnecessary
entries, which is negative ones at first, then less-accessed ones
currently. If you mean by "LRU" something to put a hard limit on
the number or size of a catcache or all caches, it would be
doable by adding sort phase before pruning, like
CatCacheCleanOldEntriesByNum() in the attached as a PoC (first
attched) as food for discussion.

With the second attached script, we can observe what is happening
from another session by the following query.

select relname, size, ntuples, ageclass from pg_stat_syscache where relname =' pg_statistic'::regclass;

> pg_statistic | 1041024 | 7109 | {{1,1109},{3,0},{30,0},{60,0},{90,6000},{0,0

On the other hand, differently from the original pruning, this
happens irrelevantly to hash resize so it will causes another
observable intermittent slowdown than rehashing.

The two should have the same extent of impact on performance when
disabled. I'll take numbers briefly using pgbench.


Kyotaro Horiguchi
NTT Open Source Software Center

Attachment Content-Type Size
v9-0001-Remove-entries-that-haven-t-been-used-for-a-certain-.patch text/x-patch 15.2 KB
v9-0002-Syscache-usage-tracking-feature.patch text/x-patch 37.7 KB
v9-0003-PoC-add-prune-by-number-of-entries-feature.patch text/x-patch 6.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2019-02-06 06:17:59 Re: Protect syscache from bloating with negative cache entries
Previous Message Ideriha, Takeshi 2019-02-06 05:32:38 RE: Cache relation sizes?