Re: bgwriter, checkpoints, curious (seeing delays)

>>> Tory M Blue <tmblue(at)gmail(dot)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.