From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | "Campbell, Lance" <lance(at)illinois(dot)edu>, MichaelDBA <MichaelDBA(at)sqlexec(dot)com>, SASIKUMAR Devaraj <sashikumard(at)yahoo(dot)com> |
Cc: | "holger(at)jakobs(dot)com" <holger(at)jakobs(dot)com>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: work_mem |
Date: | 2021-04-02 14:59:16 |
Message-ID: | 339e1ee870881a3efaffc7ea4cddac6bc4d009cf.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Fri, 2021-04-02 at 13:31 +0000, Campbell, Lance wrote:
> It feels like there needs to be work_mem and work_mem_stack_size. When work memory is
> needed a process “pops” a token off of a stack. When it is done processing it “puts”
> the token back on the stack. If the stack is empty then don’t allocate memory just
> write to disk for work_mem.
>
> This does two key things:
>
> 1) It allows for a real world understanding of how much memory is really needed on a
> day to day basis. You can track how often a stack is empty. You can also look at the
> number of temp files to see when work exceeds the work_mem allocation. There is no
> “art” to setting these values. You can use logical analysis to make choices.
>
> 2) This also prevents out of memory issues. You are protecting yourself from extreme loads.
If I get you right, you want another memory limit per session.
I see the point, but then we wouldn't need "work_mem" any more, right?
What is the point of limiting the memory per plan node if we have an
overall limit?
In practice, I have never had trouble with "work_mem". I usually follow
my rule of thumb: max_connections * work_mem + shared_buffers < RAM
While some backend may need more, many will need less. Only bitmaps, hashes
and sorts are memory hungry.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2021-04-02 15:07:36 | Re: work_mem |
Previous Message | Campbell, Lance | 2021-04-02 13:31:20 | Re: work_mem |