X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BFBC1D1E1F4 for ; Sun, 16 May 2004 14:21:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 79262-07 for ; Sun, 16 May 2004 14:21:06 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id CF45ED1E405 for ; Sun, 16 May 2004 13:56:45 -0300 (ADT) Received: from p0.pureserver.info (unknown [217.160.111.113]) by www.postgresql.com (Postfix) with SMTP id 76361CF6833 for ; Sat, 15 May 2004 07:54:45 -0300 (ADT) Received: (qmail 29578 invoked by uid 703); 15 May 2004 10:52:27 -0000 Date: Sat, 15 May 2004 12:52:27 +0200 From: share-postgres@think42.com To: pgsql-general@postgresql.org Subject: filesystem option tuning Message-ID: <20040515125227.A29574@p15097255.pureserver.info> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.16i X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200405/482 X-Sequence-Number: 60916 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