Re: Redesigning checkpoint_segments

Heikki,

Thanks for getting back to this! I really look forward to simplifying
WAL tuning for users.

>>> 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.
>
> Sure, I'm all ears.

My suggestion:

max_wal_size
min_wal_size

... these would be very easy to read & understand for users: "Set
max_wal_size based on the amount of space you have available for the
transaction log, or about 10% of the space available for your database
if you don't have a specific allocation for the log. If your database
involves large batch imports, you may want to increase min_wal_size to
be at least the size of your largest batch."

Suggested defaults:

max_wal_size: 256MB
min_wal_size: 64MB

Please remind me because I'm having trouble finding this in the
archives: how does wal_keep_segments interact with the new settings?

>> 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?
>
> It kicks in earlier, so that the checkpoint *completes* just when
> checkpoint_wal_size of WAL is used up. So the real disk usage is
> checkpoint_wal_size.

Awesome. This makes me very happy.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com