thread-index: AcjG1t9n3ICi4nwcQ1WY8/tWk14nvg==
Content-Class: urn:content-classes:message
Importance: normal
Message-ID: <484789DD.2050800@enterprisedb.com>
Date: Thu, 05 Jun 2008 09:38:21 +0300
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.12 (X11/20080420)
MIME-Version: 1.0
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Andrew Dunstan" <andrew@dunslane.net>,
	"Greg Smith" <gsmith@gregsmith.com>,
	"Andreas Pflug" <pgadmin@pse-consulting.de>,
	"Decibel!" <decibel@decibel.org>, "Peter Eisentraut" <peter_e@gmx.net>,
	<pgsql-hackers@postgresql.org>, "Josh Berkus" <josh@agliodbs.com>
Subject: Re: Overhauling GUCS
References: <48409D1E.3070208@agliodbs.com>
	<200806010032.53337.peter_e@gmx.net>
	<Pine.GSO.4.64.0806020452220.26912@westnet.com>
	<17932.1212415926@sss.pgh.pa.us>
	<20080604131810.GH65238@decibel.org>
	<4846A483.8000001@pse-consulting.de>
	<Pine.GSO.4.64.0806041045520.2258@westnet.com>
	<29525.1212613052@sss.pgh.pa.us>
	<48472E47.7030701@dunslane.net> <7473.1212625397@sss.pgh.pa.us>
In-Reply-To: <7473.1212625397@sss.pgh.pa.us>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> * How much "overhead" disk space are you willing to let Postgres use?
> 
> ... The third one is a bit weird but
> I don't see any other good way to set the checkpoint parameters.

The way I think about the checkpoint settings is:

1. Set checkpoint_timeout to the max. time you're willing to spend in 
recovery in case of crash or power loss.

2. Set checkpoint_segments to a high value. "High" meaning high enough 
that you'll never reach it in practice. The purpose is just to keep you 
from running out of disk space if something weird happens.

The amount of downtime one is willing to accept in case of power loss is 
a good question to ask because it doesn't require any knowledge of how 
PostgreSQL works; it can be answered directly from the application 
requirements. And if the DBA/developer don't know the answer, he needs 
to figure it out, because it's a very important question not only for 
the database but in general.

I believe checkpoint_timeout correlates quite well with the max. time 
required in recovery. If it took 10 minutes to generate X amount of WAL, 
replaying that WAL will need to do at most the same amount of I/O, which 
should take roughly the same amount of time, regardless of whether the 
I/O was sequential or random. If the system wasn't busy doing updates 
during between the checkpoints, it will of course take less.

As with all settings, the tool will need to explain the tradeoff. 
Smaller checkpoint_timeout means more checkpointing which means more I/O 
which means less average TPS and more WAL generated (because of 
full_page_writes).

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