On Thu, Jan 6, 2011 at 10:58 AM, Josh Berkus <josh@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
