From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Greg Smith <gsmith@gregsmith.com>
Cc: Andreas Pflug <pgadmin@pse-consulting.de>
Cc: Decibel! <decibel@decibel.org>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-hackers@postgresql.org
Cc: Josh Berkus <josh@agliodbs.com>
Subject: Re: Overhauling GUCS
Date: Thu, 05 Jun 2008 09:38:21 +0300
Message-ID: <484789DD.2050800@enterprisedb.com> (raw)
In-Reply-To: <7473.1212625397@sss.pgh.pa.us>
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>
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
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-hackers@postgresql.org
Cc: heikki@enterprisedb.com, tgl@sss.pgh.pa.us, andrew@dunslane.net, gsmith@gregsmith.com, pgadmin@pse-consulting.de, decibel@decibel.org, peter_e@gmx.net, josh@agliodbs.com
Subject: Re: Overhauling GUCS
In-Reply-To: <484789DD.2050800@enterprisedb.com>
* 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