Re: [HACKERS] Automatically setting work_mem

On Sat, Apr 22, 2006 at 06:38:53PM +0100, Simon Riggs wrote:
> On Sat, 2006-04-22 at 13:17 -0400, Tom Lane wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > > I still do, for multi-user systems. Releasing unused memory from a large
> > > CREATE INDEX will allow that memory to be swapped out, even if the brk
> > > point can't be changed.
> >
> > Say what? It can get "swapped out" anyway, whether we free() it or not.
>
> Of course it can, but if the memory is not actively used by the sort
> then it will be OK if that happens and fairly likely also. If we
> actively use the memory for the sort it would is less likely to be
> swapped out and a bad thing if it did.
>
> > More to the point, though: I don't believe that the proposed patch is a
> > good idea --- it does not reduce the peak sortmem use, which I think is
> > the critical factor for a multiuser system,
>
> I agree peak memory use is the critical factor. There is only one
> performsort in progress at any one time, though there can be many final
> merges/retrievals in progress concurrently. If the majority of the
> memory used by performsoft is released afterwards then it can be made
> available for subsequent sorts/hashes etc without increasing further the
> peak mem use.
>
> > and what it does do is
> > reduce the locality of access to the sort temp file during the merge
> > phases. That will definitely have some impact; maybe small, but some;
> > and I don't see where the benefit comes in.
>
> That I already accept.

I'd like to add a user perspective: we run dual Opteron servers with
16 Gb of memory and 16 Gb of swap. When we are busy we can have 20 to
thirty substantial queries running at one time. It is very common for us to
have several sorts and also hash joins running concurrently, some for a
minute or two, some for much longer. To avoid running out of swap and
triggering the oom killer we have had to reduce work_mem below what we
prefer.

We could add more swap, but at some point this has diminishing returns.
The proposed patch seems as if it would be helpful in our situation.

-dg

--
David Gould daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.