Re: bgwriter, checkpoints, curious (seeing delays)

> -----Mensaje original-----
> De: Tory M Blue
>
> 2010/2/25 Devrim GÜNDÜZ <devrim(at)gunduz(dot)org>:
> > On Thu, 2010-02-25 at 22:12 -0800, Tory M Blue wrote:
> >> shared_buffers = 1500MB
> >
> > Some people tend to increase this to 2.2GB(32-bit) or 4-6
> GB (64 bit),
> > if needed. Please note that more shared_buffers will lead to more
> > pressure on bgwriter, but it also has lots of benefits, too.
> >
> >> work_mem = 100MB
> >
> > 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.
>

If you have maximum 45 users running simultaneously a rather complex query
that requires... say 3 sorts, thats 45 x 100MB x 3 = 13.5 GB of RAM used up
because of this particular work_mem setting. Doesn't mean it will happen
just that your settings make this scenario possible.

So, to avoid this scenario, the suggestion is to keep work_mem low and
adjust it per query if required. I find 1-2 MB too low for my particular
requirements so I have it in 8 MB. Anyway, due to your server having a lot
of RAM your setting might make sense. But all that memory would probably be
better used if it was available for caching.

>
> Also is there a way to log if there are any deadlocks
> happening (I'm not seeing them in logs) deadlock_timeout = 5s
>

In postgresql.conf:
log_lock_waits = on # log lock waits >= deadlock_timeout

Regards,
Fernando.