Martin Foster <martin@ethereal-realms.org> writes:
> Gaetano Mendola wrote:
>> change this values in:
>> shared_buffers = 50000
>> sort_mem = 16084
>>
>> wal_buffers = 1500
This value of wal_buffers is simply ridiculous.
There isn't any reason to set wal_buffers higher than the amount of
WAL log data that will be generated by a single transaction, because
whatever is in the buffers will be flushed at transaction commit.
If you are mainly dealing with heavy concurrency then it's the mean time
between transaction commits that matters, and that's even less than the
average transaction length.
Even if you are mainly interested in the performance of large updating
transactions that are not concurrent with anything else (bulk data load,
perhaps), I'm not sure that I see any value in setting wal_buffers so
high. The data will have to go to disk before commit in any case, and
buffering so much of it just means that you are going to have a serious
spike in disk traffic right before commit. It's almost certainly better
to keep wal_buffers conservatively small and let the data trickle out as
the transaction proceeds. I don't actually think there is anything very
wrong with the default value (8) ... perhaps it is too small, but it's
not two orders of magnitude too small.
In 8.0, the presence of the background writer may make it useful to run
with wal_buffers somewhat higher than before, but I still doubt that
order-of-a-thousand buffers would be useful. The RAM would almost
certainly be better spent on general-purpose disk buffers or kernel
cache.
Note though that this is just informed opinion, as I've never done or
seen any benchmarks that examine the results of changing wal_buffers
while holding other things constant. Has anyone tried it?
regards, tom lane
