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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers