Re: Redesigning checkpoint_segments

Heikki,

On Monday, March 2, 2015, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:

> 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.
>

Not to be too much of a pain, but I've run into very few systems where
memory and disk are less than an order of magnitude different in size. I
definitely feel we need to support users tuning their systems for smaller
sizes but I do think our defaults are too small for the majority.

Thanks!

Stephen