From: Scott Marlowe <scott.marlowe@gmail.com>
To: Scott Carey <scott@richrelevance.com>
Cc: Andrus <kobruleht2@hot.ee>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Increasing GROUP BY CHAR columns speed
Date: Fri, 28 Nov 2008 13:34:29 -0700
Message-ID: <dcc563d10811281234gd5cdbc7v4b7c708dcf3babd8@mail.gmail.com> (raw)
In-Reply-To: <BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9CE@EXVMBX018-1.exch018.msoutlookonline.net>
References: <ggp1au$1iq2$1@news.hub.org>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9C9@EXVMBX018-1.exch018.msoutlookonline.net>
<3E5CE5640FFD4172956D223988D8D578@andrusnotebook>
<dcc563d10811281024l4cc3a063r4f17ba8337b1ee36@mail.gmail.com>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9CE@EXVMBX018-1.exch018.msoutlookonline.net>
On Fri, Nov 28, 2008 at 12:51 PM, Scott Carey <scott@richrelevance.com> wrote:
> I'm not sure that postgres allocates the whole work_mem each time, and in any event, the OS won't try and allocate to RAM until the page is actually used. My experience is that oversized work_mem doesn't hurt until it is actually used. Else, my configuration with 1000 concurrent connections and work_mem = 1GB would have blown up. I don't have that much RAM + SWAP * overcommit. Of the 1000 connections, only 8 run queries that would ever need more than 2 or 3 MB of space to execute. Of course, one has to be certain what the client connections do for it to be very over sized, so I would not recommend the above in general.
That's kinda like saying I have a revolver with 100 chambers and only
one bullet, and it seems perfectly safe to put it to my head and keep
pulling the trigger.
Of course pg doesn't allocate the whole amount every time. It
allocates what it needs, up to the max you allow. by setting it to 1G
it's quite possible that eventually enough queries will be running
that need a fair bit of work_mem and suddenly bang, your server is in
a swap storm and goes non-responsive.
It's far better to set it to something reasonable, like 4 or 8 Meg,
then for the individual queries that need more set it higher at run
time.
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-performance@postgresql.org
Cc: scott.marlowe@gmail.com, scott@richrelevance.com, kobruleht2@hot.ee
Subject: Re: Increasing GROUP BY CHAR columns speed
In-Reply-To: <dcc563d10811281234gd5cdbc7v4b7c708dcf3babd8@mail.gmail.com>
* 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