pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhich is more important? freq of checkpoints or the duration of them?
5+ messages / 4 participants
[nested] [flat]
* which is more important? freq of checkpoints or the duration of them?
@ 2008-03-03 14:25 Douglas J Hunley <doug@hunley.homeip.net>
0 siblings, 3 replies; 5+ messages in thread
From: Douglas J Hunley @ 2008-03-03 14:25 UTC (permalink / raw)
To: pgsql-performance
Subject about says it all. Should I be more concerned about checkpoints
happening 'frequently' or lasting 'longer'? In other words, is it ok to
checkpoint say, every 5 minutes, if it only last a second or three or better
to have checkpoints every 10 minutes that last half a minute? Stupid examples
probably, but you get my point I hope :)
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: which is more important? freq of checkpoints or the duration of them?
@ 2008-03-03 15:16 Scott Marlowe <scott.marlowe@gmail.com>
parent: Douglas J Hunley <doug@hunley.homeip.net>
2 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2008-03-03 15:16 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
On Mon, Mar 3, 2008 at 8:25 AM, Douglas J Hunley <doug@hunley.homeip.net> wrote:
> Subject about says it all. Should I be more concerned about checkpoints
> happening 'frequently' or lasting 'longer'? In other words, is it ok to
> checkpoint say, every 5 minutes, if it only last a second or three or better
> to have checkpoints every 10 minutes that last half a minute? Stupid examples
> probably, but you get my point I hope :)
The answer is, of course, it depends.
If you do a lot of batch processing where you move a lot of data in a
stream into the database, then less, but larger checkpoints are
probably a win.
Or is this a transactional system that has to run transactions in
under x seconds? Then more, smaller checkpoints might make sense.
And then, you might be better off using the bgwriter. If tuned
properly, it will keep ahead of your checkpoints just enough that they
never have to happen. Comes with a price, some small % of performance
loss peak, in exchange for a smoother behaviour.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: which is more important? freq of checkpoints or the duration of them?
@ 2008-03-03 15:55 Chris Browne <cbbrowne@acm.org>
parent: Douglas J Hunley <doug@hunley.homeip.net>
2 siblings, 1 reply; 5+ messages in thread
From: Chris Browne @ 2008-03-03 15:55 UTC (permalink / raw)
To: pgsql-performance
doug@hunley.homeip.net (Douglas J Hunley) writes:
> Subject about says it all. Should I be more concerned about checkpoints
> happening 'frequently' or lasting 'longer'? In other words, is it ok to
> checkpoint say, every 5 minutes, if it only last a second or three or better
> to have checkpoints every 10 minutes that last half a minute? Stupid examples
> probably, but you get my point I hope :)
Well, with the (new-in-8.1) background writer, you should be able to
have whatever combination might appear attractive, as the point of the
background writer is to push out dirty pages.
Pre-8.1, your choice would be either to:
a) Flush frequently, and so have the checkpoints be of short duration, or
b) Flush infrequently, so that the checkpoint flushes would have a long
duration.
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...
The issue that then remains is whether to checkpoint often, in which
case crash recovery will tend to be be quicker, or whether to
checkpoint seldom, in which case crash recovery will have fewer
checkpoints to choose from, and hence will run somewhat longer.
If your systems don't crash much, and recovery time isn't a big deal,
then this probably doesn't much matter...
--
(reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc"))
http://linuxfinances.info/info/sap.html
"I don't plan to maintain it, just to install it." -- Richard M. Stallman
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: which is more important? freq of checkpoints or the duration of them?
@ 2008-03-03 16:40 Greg Smith <gsmith@gregsmith.com>
parent: Douglas J Hunley <doug@hunley.homeip.net>
2 siblings, 0 replies; 5+ messages in thread
From: Greg Smith @ 2008-03-03 16:40 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
On Mon, 3 Mar 2008, Douglas J Hunley wrote:
> In other words, is it ok to checkpoint say, every 5 minutes, if it only
> last a second or three or better to have checkpoints every 10 minutes
> that last half a minute?
When checkpoints do too much work at once they will block clients for a
significant period of time near the end--anywhere from 2 to 8 seconds
isn't unusual. Every client on the system will just hang, then they all
start responding again in a batch when the checkpoint is finished.
With that as the problematic case, if you can keep the duration of the
checkpoint processing minimal by having them happen more frequently, then
that's the better approach. You can't push that interval too small though
or your system will be continuously checkpointing.
In cases where checkpoints hurt no matter how often you do them, there it
makes sense to have them as infrequently as possible so at least you
minimize the number of times that the disruption happens.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: which is more important? freq of checkpoints or the duration of them?
@ 2008-03-03 17:06 Greg Smith <gsmith@gregsmith.com>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 5+ messages in thread
From: Greg Smith @ 2008-03-03 17:06 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-03-03 17:06 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-03 14:25 which is more important? freq of checkpoints or the duration of them? Douglas J Hunley <doug@hunley.homeip.net>
2008-03-03 15:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-03 15:55 ` Chris Browne <cbbrowne@acm.org>
2008-03-03 17:06 ` Greg Smith <gsmith@gregsmith.com>
2008-03-03 16:40 ` Greg Smith <gsmith@gregsmith.com>
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