Re: Protect syscache from bloating with negative cache entries

At Mon, 22 Mar 2021 13:12:10 -0400, Bruce Momjian <bruce(at)momjian(dot)us> wrote in
> On Thu, Jan 28, 2021 at 05:16:52PM +0900, Kyotaro Horiguchi wrote:
> > At Thu, 28 Jan 2021 16:50:44 +0900 (JST), Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote in
> > > I was going to write in the doc something like "you can inspect memory
> > > consumption by catalog caches using pg_backend_memory_contexts", but
> > > all the memory used by catalog cache is in CacheMemoryContext. Is it
> > > sensible for each catalog cache to have their own contexts?
> >
> > Something like this.
>
> Is this feature not going to make it into PG 14? It first appeared in
> the January, 2017 commitfest:
>
> https://commitfest.postgresql.org/32/931/

Thank you for looking this. However, I'm afraid that you are looking
to a patch which is not a part of the project in CF, "Protect syscache
<blah>". I'm happy if it is committed.

It is intending not only to show more meaningful information by
pg_get_backend_memory_contexts(), but also to make it easy to
investigate what kind of cache is bloating or something like that.

With the patch, the functions shows individual context information
lines for catcaches.

> postgres=# select pg_get_backend_memory_contexts();
...
> (catcache,"catcache id 78",CacheMemoryContext,2,8192,1,6152,0,2040)
> (catcache,"catcache id 77",CacheMemoryContext,2,8192,1,6152,0,2040)
> (catcache,"catcache id 76",CacheMemoryContext,2,16384,2,7592,3,8792)

Applying catcachecxt_by_name.patch.txt on top of it changes the output
as the following. The names are not familiar to users, but give far
clearer information.

> (catcache,USERMAPPINGUSERSERVER,CacheMemoryContext,2,8192,1,6192,0,2000)
> (catcache,USERMAPPINGOID,CacheMemoryContext,2,8192,1,6192,0,2000)
> (catcache,TYPEOID,CacheMemoryContext,2,16384,2,7632,0,8752)

Applying catcachecxt_by_name_id.patch.xt on top of the _by_name.patch,
the output further changes as the following.

> (catcache,USERMAPPINGUSERSERVER[78],CacheMemoryContext,2,8192,1,6136,0,2056)
> (catcache,USERMAPPINGOID[77],CacheMemoryContext,2,8192,1,6136,0,2056)
> (catcache,TYPEOID[76],CacheMemoryContext,2,16384,2,7592,3,8792)

The number enclosed by brackets is cache id. It is useles for users
but convenient for debugging:p

catcache_individual_mcxt_2.patch.txt: rebased version of per-catcache context.
catcachecxt_by_name.patch.txt: gives a meaningful name to catcache contexts.
catcachecxt_by_name_id.patch.txt: and adds cache id.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center