On Thu, Jan 6, 2011 at 10:58 AM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> And the risks are rather asymmetric. I don't know of any problem from
>> too large a buffer until it starts crowding out shared_buffers, while
>> under-sizing leads to the rather drastic performance consequences of
>> AdvanceXLInsertBuffer having to wait on the WALWriteLock while holding
>> the WALInsertLock,
>
> Yes, performance testing has bourne that out. Increasing wal_buffers to
> between 1MB and 16MB has benfitted most test cases (DBT2, pgBench, user
> databases) substantially, while an increase has never been shown to be a
> penalty. Increases above 16MB didn't seem to help, which is
> unsurprising given the size of a WAL segment.
>
>> But I wonder if initdb.c, when selecting the default shared_buffers,
>> shouldn't test with wal_buffers = shared_buffers/64 or
>> shared_buffers/128, with a lower limit of 8 blocks, and set that as
>> the default.
>
> We talked about bumping it to 512kB or 1MB for 9.1. Did that get in?
Doesn't look like it, not yet anyway.
> Do I need to write that patch?
>
> It would be nice to have it default to 16MB out of the gate,
Would that be a good default even when the shared_buffer is only 32MB
(the maximum that initdb will ever pick as the default)?
> but there
> we're up against the Linux/FreeBSD SysV memory limits again. When are
> those OSes going to modernize?
I don't think that we should let that limit us.
For one thing, some Linux distributions already do have large defaults
for SHMMAX. SUSE, for, example, defaults to 4GB on 32-bit and much
much larger on 64-bit, and I think they have for years.
For another thing, initdb already does a climb-down on shared_buffers
until it finds something that works. All we would have to do is make
wal_buffers participate in that climb-down.
If I manually set SHMMAX to 32MB, then initdb currently climbs down to
28MB for the shared_buffers on my 32 bit machine. At that point, I
can increase wal_buffers to 896kB before shmget fails, so I think
512kb would be a good default in that situation.
Maybe initdb should test larger values for shared_buffers as well,
rather than starting at only 32MB.
Cheers,
Jeff