DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=AYd3ABnHTlZ8xf5qqSdoIxCWv+WdtaOaVAfKofTqo7+wlAMYGUwm9PKExam2kuBsAm
	jthFDDUAQTxKvWOcxC9nqnoKokV2FB6GWDm+IQClDVwweKY1bU3gBbhlRjEaggYRocSg
	s5aMGLgCwMvxdymtMf3Nj8d2XSaPt2aauvWBY=
MIME-Version: 1.0
In-Reply-To: <1267166541.2094.52.camel@hp-laptop2.gunduz.org>
References: <8a547c841002252212t382cfeb4pde3e668a9d3f2f10@mail.gmail.com>
	<1267166541.2094.52.camel@hp-laptop2.gunduz.org>
Date: Thu, 25 Feb 2010 23:01:36 -0800
Message-ID: <8a547c841002252301g49d7f183ue0909929de09d20@mail.gmail.com>
Subject: Re: bgwriter, checkpoints, curious (seeing delays)
From: Tory M Blue <tmblue@gmail.com>
To: =?ISO-8859-1?Q?Devrim_G=DCND=DCZ?= <devrim@gunduz.org>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 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 =3D 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.
>
>> checkpoint_segments =3D 100
>> checkpoint_warning =3D 3600s
>
> What about checkpoint_timeout? Please note that even if
> checkpoint_segments =3D 100, if timeout value is low (say 5 mins),
> PostgreSQL will probably checkpoint every checkpoint_timeout minutes
> (unless PostgreSQL creates $checkpoint_segments xlogs before
> checkpoint_timeout value). Depending on your workload, this may not be
> intended, and it may cause spikes -- which will lead to the issues you
> complain.
>
> I'll stop here, and suggest you read this:
>
> http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
>
> for details about this subject. As noted there, if you are running 8.3+,
> pg_stat_bgwriter will help you to tune checkpoint & bgwriter settings.
>
> -HTH.

Checkpoint_timeout is the default and that looks like 5 mins (300
seconds). And is obviously why I have such a discrepancy between time
reached and requested.

Thank you sir, that's actually the page that I've spent much of my
time on this eve :)  I'll continue to read and check my configuration
settings.

Tory