RE: Protect syscache from bloating with negative cache entries

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


>> > 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 cases.
>> --------------------------------------------------
>> 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.

In current ver8 patch there is a stats view representing age class distribution.
Does it help DBA with tuning cache_prune_age and/or cache_prune_target?
If the amount of cache entries of older age class is large, are people supposed to lower prune_age and
not to change cache_prune_target?
(I get confusion a little bit.)

Takeshi Ideriha

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2019-01-30 05:16:55 Re: [HACKERS] REINDEX CONCURRENTLY 2.0
Previous Message Merlin Moncure 2019-01-30 04:56:12 Re: Early WIP/PoC for inlining CTEs