On Thu, Oct 28, 2010 at 1:13 AM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> I sort of agree with you that the current checkpoint_segments
>> parameter is a bit hard to tune, at least if your goal is to control
>> the amount of disk space that will be used by WAL files. But I'm not
>> sure your proposal is better. Instead of having a complicated formula
>> for predicting how much disk space would get used by a given value for
>> checkpoint_segments, we'd have a complicated formula for the amount of
>> WAL that would force a checkpoint based on max_wal_size.
>
> Yes, but the complicated formula would then be *in our code* instead of
> being inflicted on the user, as it now is.
I don't think so - I think it will just be inflicted on the user in a
different way. We'd still have to document what the formula is,
because people will want to understand how often a checkpoint is going
to get forced.
So here's an example of how this could happen. Someone sets
max_wal_size = 480MB. Then, they hear about the
checkpoint_completion_target parameter, and say, ooh, goody, let me
boost that. So they raise it from 0.5 to 0.9. Now, all of a sudden,
they're getting more frequent checkpoints. Performance may get worse
rather than better. To figure out what value for max_wal_size forces
a checkpoint after the same amount of WAL that forced a checkpoint
before, they need to work backwards from max_wal_size to
checkpoint_segments, and then work forward again to figure out the new
value for the max_wal_size parameter.
Here's the math. max_wal_size = 480MB = 30 segments. With
checkpoint_completion_target = 0.5, that means that
checkpoint_segments is (30 - 1) / (2 + 0.5) = 11 (rounded down). With
checkpoint_completion_target = 0.9, that means they'll need to set
max_wal_size to (2 + 0.9) * 11 + 1 = 33 (rounded up) * 16MB = 528MB.
Whew!
My theory is that most tuning of checkpoint_segments is based on a
worry about recovery time or performance, not disk consumption.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company