Proposal: Shared Work Mem Area
Hello there! 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? Cheers, Marco
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-gene...@postgresql.org Oggetto: Re: Proposal: Shared Work Mem Area Greetings, * Marco Fortina (marco_fort...@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
Re: 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-gene...@postgresql.org Oggetto: Re: Proposal: Shared Work Mem Area Greetings, * Marco Fortina (marco_fort...@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
