Greg Smith <gsmith@gregsmith.com> writes:
> On Mon, 2 Jul 2007, Tom Lane wrote:
>>> # wal_buffers = 1MB
>> Is there really evidence in favor of such a high setting for this,
>> either?
> I noticed consistant improvements in throughput on pgbench results with
> lots of clients going from the default to 256KB, flatlining above that; it
> seemed sufficiently large for any system I've used. I've taken to using
> 1MB anyway nowadays because others suggested that number, and it seemed to
> be well beyond the useful range and thus never likely to throttle
> anything. Is there any downside to it being larger than necessary beyond
> what seems like a trivial amount of additional RAM?
There might be some value in keeping wal_buffers small enough to fit in
L2 cache; not sure.
But pgbench is not really the poster child for large wal_buffers,
because it consists exclusively of short transactions. The gain from
enlarging wal_buffers stops the moment it passes your largest
time-between-commits, since a commit has to flush out whatever's in
there.
There's probably not much point in arguing this now, though; once the
async commit patch is in there we will have to re-measure all the
behavior and develop new recommendations (and, quite possibly, a new
default value). The existence of the walwriter will reduce the useful
size of wal_buffers, but the existence of async commit might increase it.
regards, tom lane
