DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=LQxRNF58OW4VWSZrb1nhR3bkDoZh2N8dh7vpi5+4YzRlypy2PQLNZcljMdONDfYS4VXvXlXuSGCygZ2qYawR48U6wEGXKskQz6XmXF86Z0SE0VE700W4+bkFF1+1DUny+WOdrsLTy/WhUDiVxp+iKiQsLqDE1oELXND+Q8dYs8g=
Message-ID: <4683A2FF.1090404@enterprisedb.com>
Date: Thu, 28 Jun 2007 13:01:03 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
MIME-Version: 1.0
To: Jean-David Beyer <jeandavid8@verizon.net>
CC: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: update query taking too long
References: <46834124.5010600@gmail.com> <21072.1183009270@sss.pgh.pa.us>
	<46835252.5070901@gmail.com> <468357B2.1040205@archonet.com>
	<46835A16.3050602@gmail.com> <46835D14.6010507@archonet.com>
	<46836EC8.20807@gmail.com> <46839F39.5020402@verizon.net>
In-Reply-To: <46839F39.5020402@verizon.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jean-David Beyer wrote:
> I have not used postgreSQL since I tried it once in about 1998 (when I found
> it unsatisfactory, but much has changed since then), but I am going to try
> it again. What would be a good checkpointing interval? I would guess 26
> seconds is too often. What considerations go into picking a checkpointing
> interval?

Welcome back.

Longer is better when the system is running. But on recovery, longer 
checkpoint interval means a longer wait until the database is up again. 
Longer checkpoint interval also means that more WAL needs to be kept 
around, but that's not usually a concern on normal server hardware with 
plenty of disk space.

> WAL_BUFFERS         The default is 8.

Increasing this can increase the performance of bulk load operations but 
it doesn't make much difference otherwise.

> CHECKPOINT_SEGMENTS The default is 3. This would have been too low for the
>                     O.P. Would it make sense to start with a higher value
>                     or is this a good value and just not appropriate for
>                     the O.P.? Should CHECKPOINT_SEGMENTS be raised until
>                     the checkpointing is about half CHECKPOINT_TIMEOUT,
>                     e.g., 150 seconds while the dbms is running typical
>                     work?
> CHECKPOINT_TIMEOUT  The default is 300 seconds.

You have to decide if you want to use checkpoint_timeout or 
checkpoint_segments as the primary means of controlling your checkpoint 
interval. checkpoint_timeout is easier to understand and tune, so I 
would suggest using that. Depending on how long recovery times you can 
live with, set it to something like 15 minutes - 60 minutes. Then set 
checkpoint_segments to a high value; it's purpose in this scheme is 
basically to just protect you from running out of disk space on the 
filesystem WAL is located in.

Note that unlike on DB2, the size of your transactions isn't limited by 
the amount of transaction log you keep around; this is all about 
performance.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com