From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: "'Tory M Blue'" <tmblue@gmail.com>,
	=?Windows-1252?Q?'Devrim_G=DCND=DCZ'?= <devrim@gunduz.org>
Cc: <pgsql-performance@postgresql.org>
References: <8a547c841002252212t382cfeb4pde3e668a9d3f2f10@mail.gmail.com>
	<1267166541.2094.52.camel@hp-laptop2.gunduz.org>
	<8a547c841002261052r3bff71f2v6759859abce72710@mail.gmail.com>
Subject: Re: bgwriter, checkpoints, curious (seeing delays)
Date: Fri, 26 Feb 2010 17:01:22 -0300
Organization: IP-TEL
Message-ID: <1CD1089012284ACCB9F418745FDF99C9@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <8a547c841002261052r3bff71f2v6759859abce72710@mail.gmail.com>
Thread-Index: Acq3FOFDt3iz0+kiQM++LQ0mFsV5ggAByNNQ

=20

> -----Mensaje original-----
> De: Tory M Blue
>=20
> 2010/2/25 Devrim G=DCND=DCZ <devrim@gunduz.org>:
> > On Thu, 2010-02-25 at 22:12 -0800, Tory M Blue wrote:
> >> shared_buffers =3D 1500MB
> >
> > Some people tend to increase this to 2.2GB(32-bit) or 4-6=20
> GB (64 bit),=20
> > if needed. Please note that more shared_buffers will lead to more=20
> > pressure on bgwriter, but it also has lots of benefits, too.
> >
> >> work_mem =3D 100MB
> >
> > This is too much. Since you have 300 connections, you will probably=20
> > swap because of this setting, since each connection may use=20
> this much=20
> > work_mem. The rule of the thumb is to set this to a lower general=20
> > value (say, 1-2 MB), and set it per-query when needed.
>=20
> I'm slightly confused. Most things I've read, including=20
> running pg_tune for grins puts this around 100MB, 98MB for=20
> pgtune.  1-2MB just seems really low to me. And Ignore the=20
> 300 connections, thats an upper limit, I usually run a max of=20
> 40-45 but usually around 20 connections per sec.
>=20

If you have maximum 45 users running simultaneously a rather complex =
query
that requires... say 3 sorts, thats 45 x 100MB x 3 =3D 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.

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

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


Regards,
Fernando.