From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: 'Tory M Blue' <tmblue@gmail.com>
To: 'Devrim GÜNDÜZ' <devrim@gunduz.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: bgwriter, checkpoints, curious (seeing delays)
Date: Fri, 26 Feb 2010 17:01:22 -0300
Message-ID: <1CD1089012284ACCB9F418745FDF99C9@iptel.com.ar> (raw)
In-Reply-To: <8a547c841002261052r3bff71f2v6759859abce72710@mail.gmail.com>
References: <8a547c841002252212t382cfeb4pde3e668a9d3f2f10@mail.gmail.com>
<1267166541.2094.52.camel@hp-laptop2.gunduz.org>
<8a547c841002261052r3bff71f2v6759859abce72710@mail.gmail.com>
> -----Mensaje original-----
> De: Tory M Blue
>
> 2010/2/25 Devrim GÜNDÜZ <devrim@gunduz.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: fhevia@ip-tel.com.ar, tmblue@gmail.com, devrim@gunduz.org
Subject: Re: bgwriter, checkpoints, curious (seeing delays)
In-Reply-To: <1CD1089012284ACCB9F418745FDF99C9@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox