Re: Reducing System Allocator Thrashing of ExecutorState to Alleviate FDW-related Performance Degradations

On Thu, Feb 16, 2023 at 7:32 PM Andres Freund <andres(at)anarazel(dot)de> wrote:

> What PG version?
>

Hey, Andres. Thanks for the reply.

Given not much changed regarding that allocation context IIRC, I’d think
all recents. It was observed in 13, 14, and 15.

Do you have a way to reproduce this with core code,
> e.g. postgres_fdw/file_fdw?

I’ll have to create one - it was most evident on a TPC-C or sysbench test
using the Postgres, MySQL, SQLite, and Oracle FDWs. It may be reproducible
with pgbench as well.

What is all that memory used for? Is it possible that the real issue are too
> many tiny allocations, due to some allocation growing slowly?

The FDW state management allocations and whatever each FDW needs to
accomplish its goals. Different FDWs do different things.

This seems way too magic to expose to users. How would they ever know how to
> set it? And it will heavily on the specific queries, so a global config
> won't
> work well.

Agreed on the nastiness of exposing it directly. Not that we don’t give
users control of memory anyway, but that one is easier to mess up without
at least putting some custom set bounds on it.

If the issue is a specific FDW needing to make a lot of allocations, I can
> see
> adding an API to tell a memory context that it ought to be ready to
> allocate a
> certain amount of memory efficiently (e.g. by increasing the block size of
> the
> next allocation by more than 2x).

While I’m happy to be wrong, it seems is an inherent problem not really
specific to FDW implementations themselves but the general expectation that
all FDWs are using more of that context than non-FDW cases for similar
types of operations, which wasn’t really a consideration in that allocation
over time.

If we come up with some sort of alternate allocation strategy, I don’t know
how it would be very clean API-wise, but it’s definitely an idea.

--
Jonah H. Harris