On Thu, Nov 11, 2010 at 10:13 PM, Bruce Momjian <br...@momjian.us> wrote: > Robert Haas wrote: >> On Thu, Oct 28, 2010 at 1:13 AM, Josh Berkus <j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
