[PERFORMANCE] how to set wal_buffers

> ---------- Forwarded message ----------
> From: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
> To: psql performance list <pgsql-performance(at)postgresql(dot)org>
> Date: Wed, 19 Aug 2009 19:25:11 -0500
> Subject: [PERFORMANCE] how to set wal_buffers
> Hi,
>
> Our fine manual says:
> """
> 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.
> """

I don't care for that description for several reasons, but haven't
been able to come up with a good alternative.

One problem is as you note. How is the average user supposed to know
what is the size of the redo that is generated by a typical
transaction?

But other that, I still think it is not good advice. If your typical
transaction runs for 5 minutes and generates 100's of MB of WAL and
there is only one of them at a time, there is certainly no reason to
have several hundred MB of wal_buffers. It will merrily run around
the buffer ring of just a few MB.

On the other extreme, if you have many connections rapidly firing
small transactions, and you hope for the WAL of many of them to all
get flushed down to disk with a single fsync,
then your wal_buffers should be big enough to hold the WAL data of all
those transactions. Running out of WAL space has a nasty effect on
group commits.

The default value of wal_buffers is low because many older systems
have a low default value for the kernel setting shmmax. On any
decent-sized server, I'd just automatically increase wal_buffers to 1
or 2 MB. It might help and lot, and it is unlikely to hurt.

Jeff