Re: Wrong docs on wal_buffers?

On Wed, Jan 5, 2011 at 12:43 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> All,
>
> One of my coworkers just pointed this out:
>
> "The amount of memory used in shared memory for WAL data. The default is
> 64 kilobytes (64kB). The setting need only be large enough to hold the
> amount of WAL data generated by one typical transaction, since the data
> is written out to disk at every transaction commit. This parameter can
> only be set at server start."
> http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html
>
> That's quite incorrect.  The wal_buffers are shared by all concurrent
> transactions, so it needs to be sized appropriately for all
> *simultaneous* uncommitted transactions, otherwise you'll get
> unnecessary flushing.

I'd thought the same thing in the past. But on further thinking about
it, I had decided otherwise.

On a highly concurrent system, transaction commits are constantly and
unavoidably writing and flushing other transactions' WAL.

If the transactions are well spread out, each of N concurrent
homogeneous transactions only has 1/N of its total WAL in shared
buffers at any one time, so the total does come out to about 1/N * N =
1 typical transaction size. Throw in stochastic departures from
uniform distribution, and it would be somewhat higher, but not N.

Only if all the transactions move through the system in lock-step,
would need N times the typical size for one transaction. pgbench can
create this condition, but I don't know how likely it is for
real-world work flows to do so. Maybe it is common there as well?

But my bigger objection to the original wording is that it is very
hard to know how much WAL a typical transaction generates, especially
under full_page_writes.

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,

>
> Certainly performance testing data posted on this list and -hackers.
> bears that out.  My suggestion instead:
>
> "The amount of shared memory dedicated to buffering writes to the WAL.
> The default is 64 kilobytes (64kB), which is low for a busy production
> server.  Users who have high write concurrency, or transactions which
> commit individual large data writes, will want to increase it to between
> 1MB and 16MB. This parameter can only be set at server start."

I like this wording.

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.

Cheers,

Jeff