Hi All
One basic doubt.
Whether work_mem is allocated for each clients connected to Database or for against the Sort Operations(Queries) in database? Please clarify
Case 1:
work_mem=4m
Total Sessions connected=100
Total=400M
Or Case 2
work_mem=4m
Total Sort operations at DB now=50
Total work_mem=200M
Please clarify at high level whether Case 1 is true or Case 2 is true?
Regards
Sasi
On Friday, 2 April, 2021, 09:04:56 pm IST, Bruce Momjian <bruce@xxxxxxxxxx> wrote:
On Fri, Apr 2, 2021 at 03:25:04PM +0000, Campbell, Lance wrote:
> Thanks for sharing this thread. My suggestion of having a work_mem_stack_size
> is the same concept mentioned in this thread regarding having a work_mem_pool.
> I prefer this later term rather than the one I was using. When the work mem
> pool is exhausted PostgreSQL just uses temp files for work_mem. With current
> statics for temp files and with a new stats on a work mem pool usage a user
> could fine tune memory much more precisely. It would leave the “art of memory
> tuning” behind. The other added benefit is that people would have a better
> understanding of how work_mem is used by naturally having to explain what a
> work_mem_pool is and when it is drawn on. There are probably a lot of
> PostgreSQL instance that would run faster just by having the confidence to
> increase the size of work_mem. I am sure many instances have this value set to
> low.
Uh, did you read the blog before it, referenced in that blog entry:
https://momjian.us/main/blogs/pgblog/2018.html#December_7_2018
Even if we have a pool, it is still complex to configure memory, but it
might help.
--
Bruce Momjian <bruce@xxxxxxxxxx> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
> Thanks for sharing this thread. My suggestion of having a work_mem_stack_size
> is the same concept mentioned in this thread regarding having a work_mem_pool.
> I prefer this later term rather than the one I was using. When the work mem
> pool is exhausted PostgreSQL just uses temp files for work_mem. With current
> statics for temp files and with a new stats on a work mem pool usage a user
> could fine tune memory much more precisely. It would leave the “art of memory
> tuning” behind. The other added benefit is that people would have a better
> understanding of how work_mem is used by naturally having to explain what a
> work_mem_pool is and when it is drawn on. There are probably a lot of
> PostgreSQL instance that would run faster just by having the confidence to
> increase the size of work_mem. I am sure many instances have this value set to
> low.
Uh, did you read the blog before it, referenced in that blog entry:
https://momjian.us/main/blogs/pgblog/2018.html#December_7_2018
Even if we have a pool, it is still complex to configure memory, but it
might help.
--
Bruce Momjian <bruce@xxxxxxxxxx> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
