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

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