Message-Id: <4B87D16B.2E1C.0042.0@homedecorators.com>
Date: Fri, 26 Feb 2010 13:49:31 -0600
From: "Jorge Montero" <jorge_montero@homedecorators.com>
To: <pgsql-performance@postgresql.org>
Subject: Re: bgwriter, checkpoints, curious (seeing delays)
References: <8a547c841002252212t382cfeb4pde3e668a9d3f2f10@mail.gmail.com>
	<1267166541.2094.52.camel@hp-laptop2.gunduz.org>
	<8a547c841002261052r3bff71f2v6759859abce72710@mail.gmail.com>
In-Reply-To: <8a547c841002261052r3bff71f2v6759859abce72710@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline


>>> Tory M Blue <tmblue@gmail.com> 02/26/10 12:52 PM >>>
>>
>> This is too much. Since you have 300 connections, you will probably =
swap
>> because of this setting, since each connection may use this much
>> work_mem. The rule of the thumb is to set this to a lower general value
>> (say, 1-2 MB), and set it per-query when needed.
>
> I'm slightly confused. Most things I've read, including running
> pg_tune for grins puts this around 100MB, 98MB for pgtune.  1-2MB just
> seems really low to me. And Ignore the 300 connections, thats an upper
> limit, I usually run a max of 40-45 but usually around 20 connections
>per sec.

It has been said in the list before that pg_tune is extremely aggressive =
when it comes to work_mem.

100MB is just a whole lot of memory for something that is dedicated mostly =
to sorting. Some of my relatively heavy duty queries, which end up =
manipulating hundreds of thousands of rows in subqueries, do just fine =
with quite a bit less.

1-2MB is good enough for many families of queries, but it's hard to say =
what the right default should be for you. The right number can be =
estimated by running explain analyze on your most common queries, with =
parameters that are representative to regular use, and see how much memory =
they actually claim to use. In my case, for example, most of my queries do =
just fine with 10 MB, while the reporting queries that accumulate quite a =
bit of deta request up to 60MB.

If your average query needs 100 MB, it'd still mean that 40 connections =
take 4 gigs worth of work memory, which might be better spent caching the =
database.

Now, if your system is so over-specced that wasting a few gigs of RAM =
doesn't impact your performance one bit, then you might not have to worry =
about this at all.