Re: Separate memory contexts for relcache and catcache - Mailing list pgsql-hackers

On Tue, Nov 12, 2024 at 2:57 AM Jeff Davis <pgsql@j-davis.com> wrote:
>
> On Mon, 2024-11-11 at 17:05 +0530, Ashutosh Bapat wrote:
> > It will be good
> > to move ahead with the ones we all agree for now. Looking at all the
> > emails, those will be CatCacheContext,
> > RelCacheContext, PlanCacheContext, TypCacheContext.
>
> I'm not sure we have consensus on all of those yet. Andres's concern,
> IIUC, is that the additional memory contexts will cause additional
> fragmentation.
>
> I believe we have a rough consensus that CatCacheContext and
> RelCacheContext are wanted, but we're trying to find ways to mitigate
> the fragmentation.

The totals (free_bytes, total_bytes, used_bytes) of memory contexts
separated from CacheMemoryContext and those without separate are
(35968, 540672, 504704) vs (75448,524288,448840). There's about 20K
increased in used_bytes and total_bytes. And we guess/know that that
increase is because of fragmentation. Am I right? But I don't find any
reference to what load Andres ran which resulted in this state [1]. So
can not make a judgement of whether that increase represents a typical
case or not.

I experimented with the plan cache context. I created 1000 tables
using Melih's [2] queries. But moved them into a single partitioned
table.
With no prepared statement
#SELECT name, count(*), pg_size_pretty(sum(total_bytes)) as
total_bytes, sum(total_nblocks) as total_nblocks,
pg_size_pretty(sum(free_bytes)) free_by
tes, sum(free_chunks) as free_chunks, pg_size_pretty(sum(used_bytes))
used_bytes from pg_get_backend_memory_contexts() where name like
'CachedPlan%' or name = 'PlanCacheContext' group by name;
       name       | count | total_bytes | total_nblocks | free_bytes |
free_chunks | used_bytes
------------------+-------+-------------+---------------+------------+-------------+------------
 PlanCacheContext |     1 | 8192 bytes  |             1 | 7952 bytes |
          0 | 240 bytes
(1 row)

With 10 prepared statement each selecting from the partitioned table
#SELECT format('prepare all_tables_%s as SELECT count(*) FROM test',
g.i) from generate_series(1, 10) g(i); \gexec

#SELECT name, count(*), pg_size_pretty(sum(total_bytes)) as
total_bytes, sum(total_nblocks) as total_nblocks,
pg_size_pretty(sum(free_bytes)) free_bytes, sum(free_chunks) as
free_chunks, pg_size_pretty(sum(used_bytes)) used_bytes from
pg_get_backend_memory_contexts() where name like 'CachedPlan%' or name
= 'PlanCacheContext' group by name;
       name       | count | total_bytes | total_nblocks | free_bytes |
free_chunks | used_bytes
------------------+-------+-------------+---------------+------------+-------------+------------
 CachedPlanQuery  |    10 | 40 kB       |            30 | 17 kB      |
          0 | 23 kB
 CachedPlanSource |    10 | 20 kB       |            20 | 3920 bytes |
          0 | 16 kB
 PlanCacheContext |     1 | 8192 bytes  |             1 | 7952 bytes |
          0 | 240 bytes
(3 rows)

After executing all those 10 statements
#SELECT format('execute all_tables_%s', g.i) from generate_series(1,
10) g(i); \gexec

#SELECT name, count(*), pg_size_pretty(sum(total_bytes)) as
total_bytes, sum(total_nblocks) as total_nblocks,
pg_size_pretty(sum(free_bytes)) free_bytes, sum(free_chunks) as
free_chunks, pg_size_pretty(sum(used_bytes)) used_bytes from
pg_get_backend_memory_contexts() where name like 'CachedPlan%' or name
= 'PlanCacheContext' group by name;
       name       | count | total_bytes | total_nblocks | free_bytes |
free_chunks | used_bytes
------------------+-------+-------------+---------------+------------+-------------+------------
 CachedPlan       |    10 | 20 MB       |           124 | 9388 kB    |
         28 | 11 MB
 CachedPlanQuery  |    10 | 40 kB       |            30 | 17 kB      |
          0 | 23 kB
 CachedPlanSource |    10 | 20 kB       |            20 | 3920 bytes |
          0 | 16 kB
 PlanCacheContext |     1 | 8192 bytes  |             1 | 7952 bytes |
          0 | 240 bytes
(4 rows)

PlanCacheContext is never used for actual planned statements. In fact
I am not sure whether those 8K bytes it's consuming are real or just
context overhead. The real memory is used from CachedPlan* contexts
which are created and destroyed for each prepared statement.

The only use of the shell context is to be able to query memory
context statistics of cached plans, in case we change the names of
contexts for individual planned queries in future.
SELECT name, count(*), pg_size_pretty(sum(total_bytes)) as
total_bytes, sum(total_nblocks) as total_nblocks,
pg_size_pretty(sum(free_bytes)) free_bytes, sum(free_chunks) as
free_chunks, pg_size_pretty(sum(used_bytes)) used_bytes from
pg_get_backend_memory_contexts() where path @> (select path from
pg_get_backend_memory_contexts() where name = 'PlanCacheContext')
group by name;

So separating PlanCacheContext seems to have little use.

[1] https://www.postgresql.org/message-id/dywwv6v6vq3wfqyebypspq7kuez44tnycbvqjspgsqypuunbzn@mzixkn6g47y2
[2] https://www.postgresql.org/message-id/CAGPVpCTJWEQLt2eOSDGTDtRbQPUQ9b9JtZWro9osJubTyWAEMA@mail.gmail.com

--
Best Wishes,
Ashutosh Bapat