Re: Configuring BLCKSZ and XLOGSEGSZ (in 8.3)

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.

> Increasing BLCKSZ has been claimed to help by
[snip]
> 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.

I don't doubt that there may be a positive effect from increasing the
block size. But we haven't seen any analysis of why that might be. If
it's just to use the disk system bandwith better, maybe we should
combine page writes instead or something.

> 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.

Have there ever been demands for reconfiguring the TOASTing behavior?
The TOAST system seems to think that values larger than about 2 kB will
rarely or never be used in computations, only for retrieval. What was
the reason for choosing this particular limit? It seems to me that the
maximum size of useful lookup keys is mostly influenced by human
intelligence, not by the available computing hardware, so 2 kB seems to
be just fine.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/