Re: BUG #15923: Prepared statements take way too much memory.

Hi,

On 2019-07-25 18:13:33 -0400, Tom Lane wrote:
> FWIW, I've thought for some time that we should invent a memory context
> allocator that's meant for data that doesn't get realloc'd (much) after
> first allocation, with lower overhead than aset.c has.

Same.

Although I've wondered whether we'd actually want to tie that to a
separate memory context, but instead add it as an option to set on a
context. It seems like that could potentially reduce overhead, by
avoiding the need to have multiple contexts around. E.g. something like
MemoryContextUseBulkAlloc(). For aset.c that could e.g. switch the
context's callbacks to ones where AllocSetAlloc() is much simpler (one
strategy for large allocations, for the rest don't manage a freelist,
and don't round up allocation sizes). Not sure what's better.

FWIW, here's a few steps towards tracing aset.c events:

perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'AllocSetAlloc context->name:string size:u64'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'AllocSetFree:3 context->name:string chunk->size:u64'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'AllocSetFree:18 context->name:string chunk->size:u64'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'AllocSetRealloc:9 context->name:string oldsize=chunk->size:u64 size:u64'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'AllocSetReset context->name:string'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'AllocSetDelete context->name:string'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'AllocSetContextCreateInternal name:string'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'raw_parser'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'raw_parser%return'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'pg_analyze_and_rewrite'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'pg_analyze_and_rewrite%return'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'planner%return'
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --add 'planner'

which then can be used with perf record to trace a backend's
allocations:

perf record $(for e in AllocSetContextCreateInternal AllocSetDelete AllocSetReset AllocSetAlloc AllocSetFree AllocSetRealloc AllocSetFree raw_parser raw_parser__return pg_analyze_and_rewrite pg_analyze_and_rewrite__return planner planner__return ; do echo "-e probe_postgres:$e";done) -p 26963
<execute query>
ctrl-c

perf script

postgres 26963 [000] 58737.065318: probe_postgres:AllocSetContextCreateInternal: (55eba8540c23) name_string="TopTransactionContext"
postgres 26963 [000] 58737.065331: probe_postgres:AllocSetAlloc: (55eba853f906) name="TopMemoryContext" size_u64=480
postgres 26963 [000] 58737.065345: probe_postgres:raw_parser: (55eba819f68c)
postgres 26963 [000] 58737.065355: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=160
postgres 26963 [000] 58737.065362: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=944
postgres 26963 [000] 58737.065367: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=64
...
postgres 26963 [000] 58737.067714: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=32
postgres 26963 [000] 58737.067721: probe_postgres:raw_parser__return: (55eba819f68c <- 55eba83f2254)
postgres 26963 [000] 58737.067734: probe_postgres:AllocSetAlloc: (55eba853f906) name="TopTransactionContext" size_u64=24
postgres 26963 [000] 58737.067740: probe_postgres:AllocSetAlloc: (55eba853f906) name="TopTransactionContext" size_u64=104
postgres 26963 [000] 58737.067746: probe_postgres:pg_analyze_and_rewrite: (55eba83f232b)
postgres 26963 [000] 58737.067753: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=208
postgres 26963 [000] 58737.067759: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=216
...
postgres 26963 [000] 58737.074248: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=32
postgres 26963 [000] 58737.074260: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=32
postgres 26963 [000] 58737.074266: probe_postgres:pg_analyze_and_rewrite__return: (55eba83f232b <- 55eba83f2ae9)
postgres 26963 [000] 58737.074273: probe_postgres:planner: (55eba8326c51)
postgres 26963 [000] 58737.074280: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=128
postgres 26963 [000] 58737.074305: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=536
postgres 26963 [000] 58737.074316: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=48
...
postgres 26963 [000] 58737.076820: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=16
postgres 26963 [000] 58737.076827: probe_postgres:AllocSetRealloc: (55eba85403f6) name="MessageContext" oldsize=32 size_u64=24
postgres 26963 [000] 58737.076843: probe_postgres:AllocSetContextCreateInternal: (55eba8540c23) name_string="inline_function"
postgres 26963 [000] 58737.076854: probe_postgres:AllocSetAlloc: (55eba853f906) name="inline_function" size_u64=179
postgres 26963 [000] 58737.076861: probe_postgres:AllocSetAlloc: (55eba853f906) name="inline_function" size_u64=48
postgres 26963 [000] 58737.076869: probe_postgres:AllocSetAlloc: (55eba853f906) name="inline_function" size_u64=40
postgres 26963 [000] 58737.076876: probe_postgres:AllocSetAlloc: (55eba853f906) name="inline_function" size_u64=16
postgres 26963 [000] 58737.076883: probe_postgres:AllocSetAlloc: (55eba853f906) name="inline_function" size_u64=8
postgres 26963 [000] 58737.076891: probe_postgres:raw_parser: (55eba819f68c)
postgres 26963 [000] 58737.076903: probe_postgres:AllocSetAlloc: (55eba853f906) name="inline_function" size_u64=160
postgres 26963 [000] 58737.076910: probe_postgres:AllocSetAlloc: (55eba853f906) name="inline_function" size_u64=180
...
postgres 26963 [000] 58737.091725: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=128
postgres 26963 [000] 58737.091733: probe_postgres:planner__return: (55eba8326c51 <- 55eba83f24ba)
postgres 26963 [000] 58737.091740: probe_postgres:AllocSetAlloc: (55eba853f906) name="MessageContext" size_u64=32
postgres 26963 [000] 58737.091749: probe_postgres:AllocSetFree: (55eba853f4fc) name="TopTransactionContext" size=128
...

the query here was just \dt+.

it's possible that you'd need to change the offsets slightly, depending
on compiler options. One can see tracable lines with:
perf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --line AllocSetRealloc
and the available variables with:
erf probe -x /home/andres/build/postgres/dev-assert/vpath/src/backend/postgres --vars AllocSetRealloc:9

Shouldn't be hard to parse that into something giving actual stats for
each memory context, and each query phase.

> Such an allocator would be ideal for plancache.c, and perhaps other
> use-cases such as plpgsql function parsetrees.

Probably also for raw parsing. Bet we'd save more than we'd loose by not
freeing during it.

> IMV this would have these properties:
>
> * Doesn't support retail pfree; to recover space you must destroy the
> whole context. We could just make pfree a no-op. With the details
> sketched below, repalloc would have to throw an error (because it would
> not know the size of the old chunk), but I think that's OK for the
> intended purpose.

> * Minimum chunk header overhead, ie only the context pointer required by
> the mmgr.c infrastructure. In particular, don't store the chunk size.

I'm not sure it's worth eliding the size. Will make it hard to adapt the
new type of context to new code - it's pretty hard to guarantee that
there's not one random utility function in some rarely executed path
that does need the size.

> (Possibly, instead of freelists, we'd trouble to track
> end-of-block space in more than one active block, so that we avoid
> wasting such space when a large allocation doesn't quite fit.)

Hm, doesn't aset.c already solve that by not putting the oversized block
at the head of the block list?

> A totally different idea is to make a variant version of copyObject
> that is intended to produce a compact form of a node tree, and does
> not create a separate palloc allocation for each node but just packs
> them as tightly as it can in larger palloc chunks. This could outperform
> the no-pfree-context idea because it wouldn't need even context-pointer
> overhead for each node.

If done right, it'd probably also yield considerably higher spatial
locality in the memory layout.

> The stumbling block here is that nobody is gonna tolerate maintaining
> two versions of copyfuncs.c, so you'd have to find a way for a single
> set of copy functions to support this output format as well as the
> traditional one. (Alternatively, maybe we could learn to autogenerate
> the copy functions from annotated struct definitions; people have
> muttered about that for years but not done anything.)

Yea, I think we'd have to autogenerate them to go for that. I'm kinda
hoping that maybe John Naylor would take a look, after he made all our
lives quite a bit easier with the other catalog script work...

In the 'pipe dream' version of this, that script would be able to
generate code to adjust pointers after a memcpy... That'd make
copyObject() of such flattened trees much much cheaper.

Greetings,

Andres Freund