X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 35E77D1B28B for ; Sat, 22 May 2004 01:59:59 -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 71488-05 for ; Sat, 22 May 2004 01:59:38 -0300 (ADT) Received: from p0.pureserver.info (ami.ga [217.160.111.113]) by svr1.postgresql.org (Postfix) with SMTP id 1FC5CD1B176 for ; Sat, 22 May 2004 01:59:20 -0300 (ADT) Received: (qmail 26116 invoked by uid 703); 19 May 2004 07:32:39 -0000 Date: Wed, 19 May 2004 09:32:39 +0200 From: share-postgres@think42.com To: Richard Huxton , pgsql-performance@postgresql.org Subject: Re: filesystem option tuning Message-ID: <20040519093239.A26016@p15097255.pureserver.info> References: <20040515125227.A29574@p15097255.pureserver.info> <40A8F0B6.6060703@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.16i In-Reply-To: <40A8F0B6.6060703@archonet.com>; from dev@archonet.com on Mon, May 17, 2004 at 06:04:54PM +0100 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/223 X-Sequence-Number: 7054 Hi! On Mon, May 17, 2004 at 06:04:54PM +0100, Richard Huxton wrote: > share-postgres@think42.com wrote: > > [...] > > In no official capacity whatsoever, welcome aboard. Thanks ;-) > > 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 Right, raw partitions are too low-level for me these days anyhow... I assume that all postgres partitions can be mounted with noatime? > - If you can put WAL on separate disk(s), all the better. Does that mean only the xlog, or also the clog? As far as I understand, the clog contains some meta-information on the xlog, so presumably it is flushed to disc synchronously together with the xlog? That would mean that they each need a separate disk to prevent one disk having to seek too often...? > - Battery-backed write-cache for your SCSI controller can be a big > performance win I probably won't be able to get such a setup for this project; that's why I am bothering about which disk will be seeking how often. > - 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, Ok, I think tablespaces are not the important thing - at least for this project of ours. > > 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. Here goes ... we are talking about a database cluster with two tables where things are happening, one is a kind of log that is simply "appended" to and will expect to reach a size of several million entries in the time window that is kept, the other is a persistent backing of application data that will mostly see read-modify-writes of single records. Two writers to the history, one writer to the data table. The volume of data is not very high and RAM is enough... If any more information is required feel free to ask - I would really appreciate getting this disk layout sorted out. Thanks, Colin