Re: Configuring BLCKSZ and XLOGSEGSZ (in 8.3)

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Peter Eisentraut" <peter_e(at)gmx(dot)net>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Configuring BLCKSZ and XLOGSEGSZ (in 8.3)
Date: 2006-11-27 22:52:15
Message-ID: 1164667935.3778.331.camel@silverbirch.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joshua D. Drake 2006-11-27 22:54:50 Re: doc patch for savepoints
Previous Message Tom Lane 2006-11-27 22:48:47 Re: doc patch for savepoints

Browse pgsql-patches by date

  From Date Subject
Next Message Joshua D. Drake 2006-11-27 22:54:50 Re: doc patch for savepoints
Previous Message Tom Lane 2006-11-27 22:48:47 Re: doc patch for savepoints