Re: Redesigning checkpoint_segments

On 03/02/2015 08:05 PM, Josh Berkus wrote:
> On 03/02/2015 05:38 AM, Stephen Frost wrote:
>> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>>> On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>>>> On 02/26/2015 01:32 AM, Josh Berkus wrote:
>>>>> But ... I thought we were going to raise the default for max_wal_size to
>>>>> something much higher, like 1GB? That's what was discussed on this
>>>>> thread.
>>>>
>>>> No conclusion was reached on that. Me and some others were against raising
>>>> the default, while others were for it.
>>>
>>> I guess that's a fair summary of the discussion, but I still think
>>> it's the wrong conclusion. Right now, you can't get reasonable write
>>> performance with PostgreSQL even on tiny databases (a few GB) without
>>> increasing that setting by an order of magnitude. It seems an awful
>>> shame to go to all the work to mitigate the downsides of setting a
>>> large checkpoint_segments and then still ship a tiny default setting.
>>> I've got to believe that the number of people who think 128MB of WAL
>>> is tolerable but 512MB or 1GB is excessive is almost nobody. Disk
>>> sizes these days are measured in TB.
>>
>> +1. I thought the conclusion had actually been in favor of the change,
>> though there had been voices for and against.
>
> That was the impression I had too, which was why I was surprised. The
> last post on the topic was one by Robert Haas, agreeing with me on a
> value of 1GB, and there were zero objections after that.

I didn't make any further posts to that thread because I had already
objected earlier and didn't have anything to add.

Now, if someone's going to go and raise the default, I'm not going to
make a fuss about it, but the fact remains that *all* the defaults in
postgresql.conf.sample are geared towards small systems, and not hogging
all resources. The default max_wal_size of 128 MB is well in line with
e.g. shared_buffers=128MB.

- Heikki