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 17:02:28
Message-ID: 1164646949.3778.217.camel@silverbirch.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

On Mon, 2006-11-27 at 14:01 +0100, Peter Eisentraut wrote:
> Am Montag, 27. November 2006 12:30 schrieb Simon Riggs:
> > It seems possible to vary both BLCKSZ and XLOGSEGSZ rather than have
> > them set within pg_config_manual. There are a number of use-cases where
> > varying these values will offer increased performance
>
> Such as?

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
Sun think so as well, but that does seem to be rare knowledge, AFAICS.

Increasing BLCKSZ has been claimed to help by
http://archives.postgresql.org/pgsql-performance/2006-05/msg00444.php
http://archives.postgresql.org/pgsql-performance/2005-12/msg00139.php
http://archives.postgresql.org/pgsql-performance/2004-12/msg00271.php
Discussion on that does seem somewhat inconclusive, but that maybe just
that test results are rather thin on the ground because of lack of
ability to test this without recompilation. One commentator says that
the gain isn't worth the pain of having to re-compile to get it, even
though there is measured benefit.
Personally, I've not measured any benefit for OLTP workloads, but there
are many other workloads to try out.

Increasing BLCKSZ would also allow increasing the size of GIST indexes
(IIRC?). It would certainly allow larger TOAST_TARGETs to allow more
data to be held in a single longer tuple than is currently possible,
which would allow many text-based applications to avoid various
overheads.

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Volkan YAZICI 2006-11-27 17:26:03 Re: Storing a dynahash for an entire connection or transaction?
Previous Message Simon Riggs 2006-11-27 16:35:30 Re: Shared pg_xlog directory/partition and warm standby

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2006-11-27 18:51:10 Re: Configuring BLCKSZ and XLOGSEGSZ (in 8.3)
Previous Message Mike Rylander 2006-11-27 16:03:47 Re: Configuring BLCKSZ and XLOGSEGSZ (in 8.3)