Re: Redesigning checkpoint_segments - Mailing list pgsql-hackers

From Josh Berkus
Subject Re: Redesigning checkpoint_segments
Date
Msg-id 54A4A1D8.1030805@agliodbs.com
Whole thread Raw
In response to Redesigning checkpoint_segments  (Heikki Linnakangas <hlinnakangas@vmware.com>)
Responses Re: Redesigning checkpoint_segments
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Ian Barwick
Date:
Subject: Re: psql tab completion: fix COMMENT ON ... IS IS IS
Next
From: Amit Kapila
Date:
Subject: Re: Compression of full-page-writes