Re: Configuring BLCKSZ and XLOGSEGSZ (in 8.3)

On 11/27/06, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> 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.

Also, 16MB WALs are quite a burden for backup systems (that's a lot of
files that just keep coming and coming). [1]

Regards,
Dawid

[1]: It really does the difference, especially if you have a centralized backup.
And as for recovery, we have pg_xlogfile_name_offset(), the size of the
WAL file should not be a problem in HA setups.