filesystem option tuning

[share-postgres]

Hi All,

I have recently started evaluating Postgresql 7.4.2 to replace some *cough*
more proprietary database systems... Thanks to the _excellent_ documentation
(a point I cannot overemphasize) I was up and running in no time, and got a
first test application running on the native C interface.

There is just one point where I found the documentation lacking any
description and practical hints (as opposed to all other topics), namely
that of how to tune a setup for maximum performance regarding the layout of
partitions on hard-disks and their mount options.

I gather that the pg_xlog directory contains the transaction log and would
benefit greatly from being put on a separate partition. I would then mount
that partition with the noatime and forcedirectio options (on Solaris, the
latter to circumvent the OS' buffer cache)? On the other hand the data
partition should not be mounted with direct io, since Postgresql is
documented as relying heavily on the OS' cache?

Then I was wondering whether the fsync option refers only to the wal log (is
that another name for the xlog, or is one a subset of the other?), or also
to data write operations? With forcedirectio for the wal, do I still need
fsync (or O_SYNC...) because otherwise I could corrupt the data?

Are there any other directories that might benefit from being put on a
dedicated disk, and with which mount options? Even without things like
tablespaces there should be some headroom over having everything on one
partition like in the default setup.

What I should add is that reliability is a premium for us, we do not want to
sacrifice integrity for speed, and that we are tuning for a high commit rate
of small, simple transactions...

I would be greatly thankful if somebody could give me some hints or pointers
to further documentation as my search on the web did not show up much.

Regards, Colin

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

sh************@think42.com wrote:

Hi All,

I have recently started evaluating Postgresql 7.4.2 to replace some *cough*
more proprietary database systems... Thanks to the _excellent_ documentation
(a point I cannot overemphasize) I was up and running in no time, and got a
first test application running on the native C interface.
In no official capacity whatsoever, welcome aboard.
There is just one point where I found the documentation lacking any
description and practical hints (as opposed to all other topics), namely
that of how to tune a setup for maximum performance regarding the layout of
partitions on hard-disks and their mount options.
I'm not a Sun user, so I can't give any OS-specific notes, but in general:
- Don't bypass the filesystem, but feel free to tinker with mount
options if you think it will help
- If you can put WAL on separate disk(s), all the better.
- The general opinion seems to be RAID5 is slower than RAID10 unless
you have a lot of disks
- Battery-backed write-cache for your SCSI controller can be a big
performance win
- Tablespaces _should_ be available in the next release of PG, we'll
know for sure soon. That might make life simpler for you if you do want
to spread your database around by hand,
What I should add is that reliability is a premium for us, we do not want to
sacrifice integrity for speed, and that we are tuning for a high commit rate
of small, simple transactions...

Make sure the WAL is on fast disks I'd suggest. At a guess that'll be
your bottleneck.

For more info, your best bet is to check the archives on the
plpgsql-performance list, and then post there. People will probably want
to know more about your database size/number of concurrent
transactions/disk systems etc.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match