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>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>, "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>, "robertmhaas(at)gmail(dot)com" <robertmhaas(at)gmail(dot)com>, "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>, "tgl(at)sss(dot)pgh(dot)pa(dot)us" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: RE: Protect syscache from bloating with negative cache entries
Date: 2018-09-12 05:16:52
Message-ID: 4E72940DA2BF16479384A86D54D0988A6F197012@G01JPEXMBKW04
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


>Subject: Re: Protect syscache from bloating with negative cache entries
>Hello. The previous v4 patchset was just broken.

>Somehow the 0004 was merged into the 0003 and applying 0004 results in failure. I
>removed 0004 part from the 0003 and rebased and repost it.

I have some questions about syscache and relcache pruning
though they may be discussed at upper thread or out of point.

Can I confirm about catcache pruning?
syscache_memory_target is the max figure per CatCache.
(Any CatCache has the same max value.)
So the total max size of catalog caches is estimated around or
slightly more than # of SysCache array times syscache_memory_target.

If correct, I'm thinking writing down the above estimation to the document
would help db administrators with estimation of memory usage.
Current description might lead misunderstanding that syscache_memory_target
is the total size of catalog cache in my impression.

Related to the above I just thought changing sysycache_memory_target per CatCache
would make memory usage more efficient.
Though I haven't checked if there's a case that each system catalog cache memory usage varies largely,
pg_class cache might need more memory than others and others might need less.
But it would be difficult for users to check each CatCache memory usage and tune it
because right now postgresql hasn't provided a handy way to check them.
Another option is that users only specify the total memory target size and postgres
dynamically change each CatCache memory target size according to a certain metric.
(, which still seems difficult and expensive to develop per benefit)
What do you think about this?

+ /*
+ * Set up pruning.
+ *
+ * We have two knobs to control pruning and a hash can share them of
+ * syscache.
+ *
+ */
+ if (flags & HASH_PRUNABLE)
+ {
+ hctl->prunable = true;
+ hctl->prune_cb = info->prune_cb;
+ if (info->memory_target)
+ hctl->memory_target = info->memory_target;
+ else
+ hctl->memory_target = &cache_memory_target;
+ if (info->prune_min_age)
+ hctl->prune_min_age = info->prune_min_age;
+ else
+ hctl->prune_min_age = &cache_prune_min_age;
+ }
+ else
+ hctl->prunable = false;

As you commented here, guc variable syscache_memory_target and
syscache_prune_min_age are used for both syscache and relcache (HTAB), right?

Do syscache and relcache have the similar amount of memory usage?
If not, I'm thinking that introducing separate guc variable would be fine.
So as syscache_prune_min_age.

Takeshi Ideriha
Fujitsu Limited

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2018-09-12 05:23:57 Re: executor relation handling
Previous Message David Rowley 2018-09-12 05:14:41 Re: [HACKERS] Secondary index access optimizations