Re: Best OS for Postgres 8.2

On Tue, 8 May 2007, ~]~N彦 Ian Li wrote:

> In #postgresql on freenode, somebody ever mentioned that ZFS from Solaris 
> helps a lot to the performance of pgsql, so dose anyone have information 
> about that?

the filesystem you use will affect the performance of postgres 
significantly. I've heard a lot of claims for ZFS, unfortunantly many of 
them from people who have prooven that they didn't know what they were 
talking about by the end of their first or second e-mails.

much of the hype for ZFS is it's volume management capabilities and admin 
tools. Linux has most (if not all) of the volume management capabilities, 
it just seperates them from the filesystems so that any filesystem can use 
them, and as a result you use one tool to setup your RAID, one to setup 
snapshots, and a third to format your filesystems where ZFS does this in 
one userspace tool.

once you seperate the volume management piece out, the actual performance 
question is a lot harder to answer. there are a lot of people who say that 
it's far faster then the alternate filesystems on Solaris, but I haven't 
seen any good comparisons between it and Linux filesystems.

On Linux you have the choice of several filesystems, and the perfomance 
will vary wildly depending on your workload. I personally tend to favor 
ext2 (for small filesystems where the application is ensuring data 
integrity) or XFS (for large filesystems)

I personally don't trust reiserfs, jfs seems to be a tools for 
transitioning from AIX more then anything else, and ext3 seems to have all 
the scaling issues of ext2 plus the overhead (and bottleneck) of 
journaling.

one issue with journaling filesystems, if you journal the data as well as 
the metadata you end up with a very reliable setup, however it means that 
all your data needs to be written twice, oncce to the journal, and once to 
the final location. the write to the journal can be slightly faster then a 
normal write to the final location (the journal is a sequential write to 
an existing file), however the need to write twice can effectivly cut your 
disk I/O bandwidth in half when doing heavy writes. worse, when you end up 
writing mor ethen will fit in the journal (128M is the max for ext3) the 
entire system then needs to stall while the journal gets cleared to make 
space for the additional writes.

if you don't journal your data then you avoid the problems above, but in a 
crash you may find that you lost data, even though the filesystem is 
'intact' according to fsck.

David Lang

> Steve Atkins wrote:
>>
>>  On May 7, 2007, at 2:55 PM, David Levy wrote:
>> 
>> >  Hi,
>> > 
>> >  I am about to order a new server for my Postgres cluster. I will
>> >  probably get a Dual Xeon Quad Core instead of my current Dual Xeon.
>> >  Which OS would you recommend to optimize Postgres behaviour (i/o
>> >  access, multithreading, etc) ?
>> > 
>> >  I am hesitating between Fedora Core 6, CentOS and Debian. Can anyone
>> >  help with this ?
>>
>>  Well, all three you mention are much the same, just with a different
>>  badge on the box, as far as performance is concerned. They're all
>>  going to be a moderately recent Linux kernel, with your choice
>>  of filesystems, so any choice between them is going to be driven
>>  more by available staff and support or personal preference.
>>
>>  I'd probably go CentOS 5 over Fedora  just because Fedora doesn't
>>  get supported for very long - more of an issue with a dedicated
>>  database box with a long lifespan than your typical desktop or
>>  interchangeable webserver.
>>
>>  I might also look at Solaris 10, though. I've yet to play with it much,
>>  but it
>>  seems nice, and I suspect it might manage 8 cores better than current
>>  Linux setups.
>>
>>  Cheers,
>>    Steve
>>
>>
>>
>>  ---------------------------(end of broadcast)---------------------------
>>  TIP 5: don't forget to increase your free space map settings
>> 
>
> Regards
>
> Ian
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>              http://www.postgresql.org/docs/faq
>
>From pgsql-performance-owner@postgresql.org  Tue May  8 05:09:47 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id B7F2B9FBB48
	for <pgsql-performance-postgresql.org@postgresql.org>; Tue,  8 May 2007 05:09:46 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
 by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
 with ESMTP id 21379-08 for <pgsql-performance-postgresql.org@postgresql.org>;
 Tue,  8 May 2007 05:09:40 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.234])
	by postgresql.org (Postfix) with ESMTP id C1AE49FBB21
	for <pgsql-performance@postgresql.org>; Tue,  8 May 2007 05:09:42 -0300 (ADT)
Received: by nz-out-0506.google.com with SMTP id s1so1988964nze
        for <pgsql-performance@postgresql.org>; Tue, 08 May 2007 01:09:41 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed;
        d=gmail.com; s=beta;
        h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=tMnqJ4jJFkANPEKsjO48H7tdlMkh1PtxD9ojia3Hs7kK4jLUtNUWSWo6GEPQw3nHKK/IFUU42X/xGazATgo19i5FXATTGs/NxHYcWcT4jYq/r84Fmsj2ndDu3lnjOLK8pGaJ+Di1Rw1oLfhcu+zCkBrfTX9KTtbO4BSU2riTwHc=
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=ibMOoo+Gg9CIe5nVV5w4I8kJBzehuai5dBAmssoI98av8cCWzpmlj29ellDiBihXGB3g1EbKZR/mldl4oZV3yA/kvjUtA5mAHvvxnkQMjMHzEOOokHx/41kpWk6p14IjD8PYoLxlcaggO4I9y9xyJR8/1+ikTKvqdLOk3eUa+tc=
Received: by 10.115.95.1 with SMTP id x1mr2497915wal.1178611781182;
        Tue, 08 May 2007 01:09:41 -0700 (PDT)
Received: by 10.114.160.9 with HTTP; Tue, 8 May 2007 01:09:40 -0700 (PDT)
Message-ID: <b41c75520705080109g7655f5f2m5592595c9a578c16@mail.gmail.com>
Date: Tue, 8 May 2007 10:09:40 +0200
From: "Claus Guttesen" <kometen@gmail.com>
To: "David Levy" <dvid.levy@gmail.com>
Subject: Re: Best OS for Postgres 8.2
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1178574927.533849.90730@e51g2000hsg.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1178574927.533849.90730@e51g2000hsg.googlegroups.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/86
X-Sequence-Number: 24476

> I am about to order a new server for my Postgres cluster. I will
> probably get a Dual Xeon Quad Core instead of my current Dual Xeon.
> Which OS would you recommend to optimize Postgres behaviour (i/o
> access, multithreading, etc) ?
>
> I am hesitating between Fedora Core 6, CentOS and Debian. Can anyone
> help with this ?

My only experience is with FreeBSD. My installation is running 6.2 and
pg 7.4 on a four-way woodcrest and besides being very stable it's also
performing very well. But then FreeBSD 6.x might not scale as well
beyond four cores atm. There you probably would need FreeBSD 7 which
is the development branch and should require extensive testing.

How big will the db be in size?

-- 
regards
Claus