Re: Configuring BLCKSZ and XLOGSEGSZ (in 8.3)

On Mon, 2006-11-27 at 22:08 +0100, Peter Eisentraut wrote:
> Simon Riggs wrote:
> > Increasing XLOGSEGSZ improves performance with write intensive
> > workloads, where WAL is sufficiently active that switching WAL files
> > and fsyncing causes all commits to freeze momentarily.
> > http://blogs.sun.com/jkshah/category/Databases?page=1
>
> He increased the WAL segment size from 16 MB to 256 MB. Without any
> further information about the system configuration, that seems to be
> mostly equivalent to increasing the number of checkpoint segments.

On a busy system you can switch WAL segments every few seconds at 16MB.
Fsync can freeze commits for more than a second, so raising the segment
size reduces the fsync overhead considerably. This doesn't drop away
fully with any of the various wal_fsync_method settings.

256MB is good, 1GB is better. Obviously changes the on-disk footprint
considerably, so some flexibility is needed to accommodate small PC
configs and large performance servers.

It does also have the same effect as changing checkpoint segments, but
we already have variability in that dimension.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com