On 08/23/2013 02:08 PM, Heikki Linnakangas wrote: > Here's a bigger patch, which does more. It is based on the ideas in the > post I started this thread with, with feedback incorporated from the > long discussion. With this patch, WAL disk space usage is controlled by > two GUCs: > > min_recycle_wal_size > checkpoint_wal_size > <snip>
> These settings are fairly intuitive for a DBA to tune. You begin by > figuring out how much disk space you can afford to spend on WAL, and set > checkpoint_wal_size to that (with some safety margin, of course). Then > you set checkpoint_timeout based on how long you're willing to wait for > recovery to finish. Finally, if you have infrequent batch jobs that need > a lot more WAL than the system otherwise needs, you can set > min_recycle_wal_size to keep enough WAL preallocated for the spikes. We'll want to rename them to make it even *more* intuitive. But ... do I understand things correctly that checkpoint wouldn't "kick in" until you hit checkpoint_wal_size? If that's the case, isn't real disk space usage around 2X checkpoint_wal_size if spread checkpoint is set to 0.9? Or does checkpoint kick in sometime earlier? > except that it's more > intuitive to set it in terms of "MB of WAL space required", instead of > "# of segments between checkpoints". Yes, it certainly is. We'll need to caution people that fractions of 16MB will be ignored. > Does that make sense? I'd love to hear feedback on how people setting up > production databases would like to tune these things. The reason for the > auto-tuning between the min and max is to be able to set reasonable > defaults e.g for embedded systems that don't have a DBA to do tuning. > Currently, it's very difficult to come up with a reasonable default > value for checkpoint_segments which would work well for a wide range of > systems. The PostgreSQL default of 3 is way way too low for most > systems. On the other hand, if you set it to, say, 20, that's a lot of > wasted space for a small database that's not updated much. With this > patch, you can set "max_wal_size=1GB" and if the database ends up > actually only needing 100 MB of WAL, it will only use that much and not > waste 900 MB for useless preallocated WAL files. This sounds good, aside from the potential 2X issue I mention above. Mind you, what admins really want is a hard limit on WAL size, so that they can create a partition and not worry about PG running out of WAL space. But ... > Making it a hard limit is a much bigger task than I'm willing to tackle > right now. ... agreed. And this approach could be built on for a hard limit later on. As a note, pgBench would be a terrible test for this patch; we really need something which creates uneven traffic. I'll see if I can devise something. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
