Re: bgwriter, checkpoints, curious (seeing delays)

От: Fernando Hevia
Тема: Re: bgwriter, checkpoints, curious (seeing delays)
Дата: ,
Msg-id: 1CD1089012284ACCB9F418745FDF99C9@iptel.com.ar
(см: обсуждение, исходный текст)
Ответ на: Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue)
Список: pgsql-performance

Скрыть дерево обсуждения

bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
 Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
  Re: bgwriter, checkpoints, curious (seeing delays)  (Jochen Erwied, )
 Re: bgwriter, checkpoints, curious (seeing delays)  (Devrim GÜNDÜZ, )
  Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
   Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
    Re: bgwriter, checkpoints, curious (seeing delays)  (Greg Smith, )
   Re: bgwriter, checkpoints, curious (seeing delays)  (Devrim GÜNDÜZ, )
    Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
  Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
   Re: bgwriter, checkpoints, curious (seeing delays)  ("Jorge Montero", )
    Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
     Re: bgwriter, checkpoints, curious (seeing delays)  (Robert Haas, )
   Re: bgwriter, checkpoints, curious (seeing delays)  ("Fernando Hevia", )
 Re: bgwriter, checkpoints, curious (seeing delays)  ("Kevin Grittner", )
  Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
   Re: bgwriter, checkpoints, curious (seeing delays)  (Ben Chobot, )


> -----Mensaje original-----
> De: Tory M Blue
>
> 2010/2/25 Devrim GÜNDÜZ <>:
> > 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.



В списке pgsql-performance по дате сообщения:

От: Bruce Momjian
Дата:
Сообщение: Re: SSD + RAID
От: Greg Smith
Дата:
Сообщение: Re: SSD + RAID