Re: Auto-tuning work_mem and maintenance_work_mem

On 2014-02-17 12:23:58 -0500, Robert Haas wrote:
> On Mon, Feb 17, 2014 at 11:19 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2014-02-16 21:26:47 -0500, Robert Haas wrote:
> >> I don't think anyone objected to increasing the defaults for work_mem
> >> and maintenance_work_mem by 4x, and a number of people were in favor,
> >> so I think we should go ahead and do that. If you'd like to do the
> >> honors, by all means!
> >
> > Actually, I object to increasing work_mem by default. In my experience
> > most of the untuned servers are backing some kind of web application and
> > often run with far too many connections. Increasing work_mem for those
> > is dangerous.
>
> I think you may be out-voted.

I realize that, but I didn't want to let the "I don't think anyone
objected" stand :)

> With the proposed defaults, a user with one sort or hash in every
> session, each of which uses the entirety of work_mem, is on the hook
> for 400MB. If you're trying to handle 100 connections on a machine
> that does not have 400MB of working memory available, you are probably
> in for a bad time of it.

Sure, if that's all they do it's fine. But often enough queries aren't
that simple. Lots of the ORMs commonly used for web applications tend to
create lots of JOINs to gather all the data and also use sorting for paging.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services