From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Campbell, Lance <lance@illinois.edu>
To: MichaelDBA <MichaelDBA@sqlexec.com>
To: SASIKUMAR Devaraj <sashikumard@yahoo.com>
Cc: holger@jakobs.com <holger@jakobs.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: work_mem
Date: Fri, 02 Apr 2021 16:59:16 +0200
Message-ID: <339e1ee870881a3efaffc7ea4cddac6bc4d009cf.camel@cybertec.at> (raw)
In-Reply-To: <SN6PR11MB3326EADE632BA4EBE1FA28DCDE7A9@SN6PR11MB3326.namprd11.prod.outlook.com>
References: <A2AA6D08-9506-4B41-9738-4D5FF8E2E011@illinois.edu>
<04D84259-1B75-4648-949F-78677916194E@jakobs.com>
<66290583.2329958.1617367063840@mail.yahoo.com>
<c1e02a03-71cd-4071-e000-967403bcab9d@sqlexec.com>
<1654783699.2333997.1617367552583@mail.yahoo.com>
<592e5895-8dbd-1a4f-a87c-af72ac8eea02@sqlexec.com>
<SN6PR11MB3326EADE632BA4EBE1FA28DCDE7A9@SN6PR11MB3326.namprd11.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, lance@illinois.edu, MichaelDBA@sqlexec.com, sashikumard@yahoo.com, holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: work_mem
In-Reply-To: <339e1ee870881a3efaffc7ea4cddac6bc4d009cf.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox