From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: which is more important? freq of checkpoints or the duration of them?
Date: Mon, 3 Mar 2008 12:06:29 -0500 (EST)
Message-ID: <Pine.GSO.4.64.0803031140420.10152@westnet.com> (raw)
In-Reply-To: <60y78zajka.fsf@dba2.int.libertyrms.com>
References: <200803030925.02931.doug@hunley.homeip.net>
<60y78zajka.fsf@dba2.int.libertyrms.com>
On Mon, 3 Mar 2008, Chris Browne wrote:
> Now, if you have reasonable settings (I'm not sure how well its tuning
> is documented :-(), checkpoint "flushes" should be able to be short,
> however infrequent they may be. In effect, the "oops, the database got
> blocked by checkpoint flushing" issue should now be gone...
Ah, if only it were true. The background writer can be made to work
fairly well in circa early 8.1 setups where the shared_buffers cache is
small. But on more current systems where there's a lot of memory
involved, you can't get a tuning aggressive enough to make checkpoint
spikes small without wasting a bunch of I/O writing buffers that will just
get dirty again before the checkpoint. Since the kinds of systems that
have nasty checkpoint spikes are also I/O bound in general, there is no
good way to resolve that struggle using the code in 8.1 and 8.2.
The checkpoint_completion_target tunable and related code in 8.3 is the
first approach to this issue that has a good foundation even with larger
buffer caches. You can band-aid some cases well enough to improve things
with the background writer in earlier versions, but it's certainly not
guaranteed that it's possible even if you spend lots of time fiddling with
the settings.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
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-performance@postgresql.org
Cc: gsmith@gregsmith.com
Subject: Re: which is more important? freq of checkpoints or the duration of them?
In-Reply-To: <Pine.GSO.4.64.0803031140420.10152@westnet.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