Re: How much work_mem to configure...

On Sat, Jul 5, 2008 at 5:24 AM, Jessica Richard <rjessil(at)yahoo(dot)com> wrote:
> How can I tell if my work_mem configuration is enough to support all
> Postgres user activities on the server I am managing?
>
> Where do I find the indication if the number is lower than needed.

You kinda have to do some math with fudge factors involved. As
work_mem gets smaller, sorts spill over to disk and get slower, and
hash_aggregate joins get avoided because they need to fit into memory.

As you increase work_mem, sorts can start happening in memory (or with
less disk writing) and larger and larger sets can have hash_agg joins
performed on them because they can fit in memory.

But there's a dark side to work_mem being too large, and that is that
you can run your machine out of free memory with too many large sorts
happening, and then the machine will slow to a crawl as it swaps out
the very thing you're trying to do in memory.

So, I tend to plan for about 1/4 of memory used for shared_buffers,
and up to 1/4 used for sorts so there's plenty of head room and the OS
to cache files, which is also important for performance. If you plan
on having 20 users accessing the database at once, then you figure
each one might on average run a query with 2 sorts, and that you'll be
using a maximum of 20*2*work_mem for those sorts etc...

If it's set to 8M, then you'd get approximately 320 Meg max used by
all the sorts flying at the same time. You can see why high work_mem
and high max_connections settings together can be dangerous. and why
pooling connections to limit the possibility of such a thing is useful
too.

Generally it's a good idea to keep it in the 4 to 16 meg range on most
machines to prevent serious issues, but if you're going to allow 100s
of connections at once, then you need to look at limiting it based on
how much memory your server has.