R: Proposal: Shared Work Mem Area
Thanks Stephen.
https://commitfest.postgresql.org/42/3867/ is not exacly what I proposed as new feature to developers.
If I'm not wrong, almost all main memory areas have a fixed size:
shared_buffers
effective_cache_size
wal_buffers
Instead, work_mem is per-process dynamically allocated up to defined size limit.
What I suggested is to replace work_mem from per-process allocation to global and fixed size allocation (see pga_aggregate_target on Oracle) and shared to worker processes.
Let's assume the new parameter name is worker_mem_area and this was set to 8GB: with my proposal method each worker process do not use it's own dedicated work_mem but the shared one.
In this way each worker is also able to peek free pages from the worker_mem_area if needed.
Regards,
Marco
________________________________
Da: Stephen Frost
Inviato: Giovedì, 06 Aprile, 2023 14:43
A: Marco Fortina
Cc: pgsql-general@postgresql.org
Oggetto: Re: Proposal: Shared Work Mem Area
Greetings,
* Marco Fortina (marco_fortina@hotmail.it) wrote:
I would like to propose to developers a new feature to replace the private management of the work mem with a management through shared memory: allocated at the start of PostgreSQL this area should be shared to all worker processes as for example Oracle Database PGA do.
This would allow an optimal use of this memory area by limiting only its global maximum limit and not a configuration/allocation at the process level.
What do you think about my proposal?
There's ongoing work to provide a way to have a global maximum limit
which doesn't involve entirely reworking how work_mem works today. The
commitfest entry for that work is here:
https://commitfest.postgresql.org/42/3867/
If you're interested in that, getting additional reviews and comments on
the work would be helpful in moving it forward.
Thanks,
Stephen
Greetings,
Please don't top-post on the PG mailing lists, it makes it harder to
follow the discussion.
* Marco Fortina (marco_fortina@hotmail.it) wrote:
https://commitfest.postgresql.org/42/3867/ is not exacly what I proposed as new feature to developers.
I understood what you were proposing.
If I'm not wrong, almost all main memory areas have a fixed size:
shared_buffers
effective_cache_size
wal_buffersInstead, work_mem is per-process dynamically allocated up to defined size limit.
That's not how work_mem works actually. It's a per-node amount and it's
not a per-process overall limit, nor is it really a hard limit though
some nodes will do their best to respect the amount configured.
What I suggested is to replace work_mem from per-process allocation to global and fixed size allocation (see pga_aggregate_target on Oracle) and shared to worker processes.
I understood the suggestion and it's a lot of work for an unclear gain.
You noted that having it be pulled from a single area would allow
administrators to configure an overall memory usage limit- but that's
not the only way to do that and there's an existing effort to do exactly
that already underway that's a lot simpler than what you're proposing.
While there might be other advantages to having a shared memory segment
be used for work_mem, you've not outlined any.
Let's assume the new parameter name is worker_mem_area and this was set to 8GB: with my proposal method each worker process do not use it's own dedicated work_mem but the shared one.
I understand your suggestion, but making such a large change just to
make it isn't sensible, there should be reasoning behind why that's
better than what we're doing already or proposing to do.
In this way each worker is also able to peek free pages from the worker_mem_area if needed.
This can be done with the existing approach and doesn't require a shared
memory segment for work_mem. We are pretty far from having an actual
acceptance system for queries though but I do agree that would be a
useful thing to work towards. I don't know that it requires work_mem
being in shared memory though.
Thanks,
Stephen