Re: Configuring BLCKSZ and XLOGSEGSZ (in 8.3)

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