On Thu, Nov 11, 2010 at 10:13 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Robert Haas wrote:
>> 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
>
> Uh, checkpoint_completion_target only controls flushing of buffers
> between checkpoints, not the frequency of checkpoints.
According to the formula in our fine documentation, if you increase
checkpoint_completion_target, the maximum number of WAL files also
increases. This makes sense: the files from the last checkpoint can't
be removed until further along into the next cycle. Therefore, if you
wanted to increase the checkpoint_completion_target while keeping the
maximum amount of WAL on disk the same, you'd need to trigger
checkpoints more frequently.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company