Re: bgwriter, checkpoints, curious (seeing delays)

["Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>]

> -----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.