Re: Best OS for Postgres 8.2 - Mailing list pgsql-performance

I am back with the chatlog and seem it's the Transparent compression
that helps a lot, very interesting...

here is the log of #postgresql on Apr. 21th around 13:20 GMT (snipped) :
<Solatis>      why is that, when hard disk i/o is my bottleneck ?
<Solatis>      well i have 10 disks in a raid1+0 config
<Solatis>      it's sata2 yes
<Solatis>      i run solaris express, whose kernel says SunOS
<Solatis>      running 'SunOS solatis2 5.11 snv_61 i86pc i386 i86pc
<Solatis>      well, the thing is, i'm using zfs
<Solatis>      yeah, it was the reason for me to install solaris in
the first place
<Solatis>      and a benchmark for my system comparing debian linux
with solaris express showed a +- 18% performance gain when switching
to solaris
<Solatis>      so i'm happy
<Solatis>      (note: the benchmarking was not scientifically
grounded at all, it was just around 50 million stored procedure
calls which do select/update/inserts on my database which would
simulate my specific case)
<Solatis>      but the killer thing was to enable compression on zfs
<Solatis>      that reduced the hard disk i/o with a factor 3, which
was the probable cause of the performance increase
<Solatis>      oh, at the moment it's factor 2.23
<Solatis>      still, it's funny to see that postgresql says that my
database is using around 41GB's, while only taking up 18GB on the
hard disk
=== end of log ===

david@lang.hm wrote:
> 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
>
Regards
Ian