Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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

pgsql-hackers by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group