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, ideriha(dot)takeshi(at)jp(dot)fujitsu(dot)com, 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, craig(at)2ndquadrant(dot)com
Subject: Re: Protect syscache from bloating with negative cache entries
Date: 2019-01-28 03:46:50
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

At Fri, 25 Jan 2019 08:14:19 +0000, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote in <0A3221C70F24FB45833433255569204D1FB70EFB(at)G01JPEXMBYT05>
> Hi Horiguchi-san, Bruce,
> From: Bruce Momjian [mailto:bruce(at)momjian(dot)us]
> > I suggest you go with just syscache_prune_min_age, get that into PG 12,
> > and we can then reevaluate what we need. If you want to hard-code a
> > minimum cache size where no pruning will happen, maybe based on the system
> > catalogs or typical load, that is fine.
> Please forgive me if I say something silly (I might have got lost.)
> Are you suggesting to make the cache size limit system-defined and uncontrollable by the user? I think it's necessary for the DBA to be able to control the cache memory amount. Otherwise, if many concurrent connections access many partitions within a not-so-long duration, then the cache eviction can't catch up and ends up in OOM. How about the following questions I asked in my previous mail?

cache_memory_target does the opposit of limiting memory usage. It
keeps some amount of syscahe entries unpruned. It is intended for
sessions on where cache-effective queries runs intermittently.
syscache_prune_min_age also doesn't directly limit the size. It
just eventually prevents infinite memory consumption.

The knobs are not no-brainer at all and don't need tuning in most

> --------------------------------------------------
> This is a pure question. How can we answer these questions from users?
> * What value can I set to cache_memory_target when I can use 10 GB for the caches and max_connections = 100?
> * How much RAM do I need to have for the caches when I set cache_memory_target = 1M?
> The user tends to estimate memory to avoid OOM.
> --------------------------------------------------

You don't have a direct control on syscache memory usage. When
you find a queriy slowed by the default cache expiration, you can
set cache_memory_taret to keep them for intermittent execution of
a query, or you can increase syscache_prune_min_age to allow
cache live for a longer time.


Kyotaro Horiguchi
NTT Open Source Software Center

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2019-01-28 03:53:17 Re: WIP: Avoid creation of the free space map for small tables
Previous Message John Naylor 2019-01-28 03:46:33 Re: WIP: Avoid creation of the free space map for small tables