Thread: Best OS for Postgres 8.2

Best OS for Postgres 8.2

From
David Levy
Date:
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 ?


Regards


Re: Best OS for Postgres 8.2

From
"Joshua D. Drake"
Date:
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 you just described three linux distributions, which is hardly a
question about which OS to use ;). I would stick with the long supported
versions of Linux, thus CentOS 5, Debian 4, Ubuntu Dapper.

Joshua D. Drake


>
>
> Regards
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


Re: Best OS for Postgres 8.2

From
Bill Moran
Date:
In response to "Joshua D. Drake" <jd@commandprompt.com>:

> 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 you just described three linux distributions, which is hardly a
> question about which OS to use ;). I would stick with the long supported
> versions of Linux, thus CentOS 5, Debian 4, Ubuntu Dapper.

There used to be a prominent site that recommended FreeBSD for Postgres.
Don't know if that's still recommended or not -- but bringing it up is
likely to start a Holy War.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Re: Best OS for Postgres 8.2

From
"Joshua D. Drake"
Date:
Bill Moran wrote:
> In response to "Joshua D. Drake" <jd@commandprompt.com>:
>
>> 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 you just described three linux distributions, which is hardly a
>> question about which OS to use ;). I would stick with the long supported
>> versions of Linux, thus CentOS 5, Debian 4, Ubuntu Dapper.
>
> There used to be a prominent site that recommended FreeBSD for Postgres.
> Don't know if that's still recommended or not -- but bringing it up is
> likely to start a Holy War.

Heh... I doubt it will start a war. FreeBSD is a good OS. However, I
specifically noted the Dual Xeon Quad Core, which means, 8 procs. It is
my understanding (and I certainly could be wrong) that FreeBSD doesn't
handle SMP nearly as well as Linux (and Linux not as well as Solaris).

Sincerely,

Joshua D. Drake

>


Re: Best OS for Postgres 8.2

From
Steve Atkins
Date:
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



Re: Best OS for Postgres 8.2

From
Chris
Date:
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 ?

Use the one you're most comfortable with.

I don't think you'll notice *that* much difference between linux systems
for performance - but whether you're comfortable using any of them will
make a difference in managing it in general.

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: Best OS for Postgres 8.2

From
david@lang.hm
Date:
On Tue, 8 May 2007, Chris wrote:

> 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 ?
>
> Use the one you're most comfortable with.
>
> I don't think you'll notice *that* much difference between linux systems for
> performance - but whether you're comfortable using any of them will make a
> difference in managing it in general.

the tuneing that you do (both of the OS and of postgres) will make more
of a difference then anything else.

David Lang

Re: Best OS for Postgres 8.2

From
Chris
Date:
david@lang.hm wrote:
> On Tue, 8 May 2007, Chris wrote:
>
>> 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 ?
>>
>> Use the one you're most comfortable with.
>>
>> I don't think you'll notice *that* much difference between linux
>> systems for performance - but whether you're comfortable using any of
>> them will make a difference in managing it in general.
>
> the tuneing that you do (both of the OS and of postgres) will make more
> of a difference then anything else.

Which is why it's best to know/understand the OS first ;)

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: Best OS for Postgres 8.2

From
Ron
Date:
I am using FC6 in production for our pg 8.2.4 DB server and am quite
happy with it.

The big advantage with FC6 for me was that the FC6 team seems to keep
more current with the latest stable revs of most OSSW (including
kernel revs!) better than any of the other major distros.

(Also, SE Linux is a =good= thing security-wise.  If it's good enough
for the NSA...)

Downside is that initial install and config can be a bit complicated.

We're happy with it.

Cheers,
Ron Peacetree


At 05:55 PM 5/7/2007, 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 ?
>
>
>Regards
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org


Re: Best OS for Postgres 8.2

From
Greg Smith
Date:
On Mon, 7 May 2007, David Levy wrote:

> I am hesitating between Fedora Core 6, CentOS and Debian. Can anyone
> help with this ?

Debian packages PostgreSQL in a fashion unique to it; it's arguable
whether it's better or not (I don't like it), but going with that will
assure your installation is a bit non-standard compared with most Linux
installas.  The main reasons you'd pick Debian are either that you like
that scheme (which tries to provide some structure to running multiple
clusters on one box), or that you plan to rely heavily on community
packages that don't come with the Redhat distributions and therefore would
appreciate how easy it is to use apt-get against the large Debian software
repository.

Given the buginess and unexpected changes from packages updates of every
Fedora Core release I've ever tried, I wouldn't trust any OS from that
line to run a database keeping track of where my socks are at.  Core 6
seems better than most of the older ones.  I find it hard to understand
what it offers that Centos doesn't such that you'd want Fedora instead.

Centos just released a new version 5 recently.  It's running a fairly
modern kernel with several relevant performance improvements over the much
older V4; unless you have some odd piece of hardware where there is only a
driver available for Centos 4 (I ran into this with a disk controller),
the new version would better.

The main advantages of Centos over the other two are that so many people
are/will be running very similar configurations that you should able to
find help easily if you run into any issues.  I revisited fresh installs
of each recently, and after trying both I found it more comfortable to run
the database server on Centos, but I did miss the gigantic and easy to
install Debian software repository.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Best OS for Postgres 8.2

From
Tom Lane
Date:
Greg Smith <gsmith@gregsmith.com> writes:
> Debian packages PostgreSQL in a fashion unique to it; it's arguable
> whether it's better or not (I don't like it), but going with that will
> assure your installation is a bit non-standard compared with most Linux
> installas.

<dons red fedora>

What Debian has done is set up an arrangement that lets you run two (or
more) different PG versions in parallel.  Since that's amazingly helpful
during a major-PG-version upgrade, most of the other packagers are
scheming how to do something similar.  I'm not sure when this will
happen in the PGDG or Red Hat RPMs, but it probably will eventually.

> Given the buginess and unexpected changes from packages updates of every
> Fedora Core release I've ever tried, I wouldn't trust any OS from that
> line to run a database keeping track of where my socks are at.  Core 6
> seems better than most of the older ones.  I find it hard to understand
> what it offers that Centos doesn't such that you'd want Fedora instead.

Fedora is about cutting edge, RHEL is about stability, and Centos tracks
RHEL.  No surprises there.  (<plug> and if someday you want commercial
support for your OS, a Centos->RHEL update will get you there easily.
AFAIK Red Hat doesn't have a clean solution for someone running Fedora
who suddenly realizes he needs a 24x7-supportable OS right now.
Something to work on... </plug>)

</dons red fedora>

            regards, tom lane

Re: Best OS for Postgres 8.2

From
李彦 Ian Li
Date:
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?

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

Re: Best OS for Postgres 8.2

From
david@lang.hm
Date:
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
>

Re: Best OS for Postgres 8.2

From
"Claus Guttesen"
Date:
> 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

Re: Best OS for Postgres 8.2

From
Heikki Linnakangas
Date:
david@lang.hm wrote:
> 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.

PostgreSQL itself journals it's data to the WAL, so that shouldn't happen.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: Best OS for Postgres 8.2

From
"Claus Guttesen"
Date:
> > 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.

Even though those posters may have proven them selves wrong, zfs is
still a very handy fs and it should not be judged relative to these
statements.

> 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.

One could install pg on solaris 10 and format the data-area as ufs and
then as zfs and compare import- and query-times and other benchmarking
but comparing ufs/zfs to Linux-filesystems would also be a comparison
of those two os'es.

--
regards
Claus

Re: Best OS for Postgres 8.2

From
david@lang.hm
Date:
On Tue, 8 May 2007, Claus Guttesen 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.
>
> Even though those posters may have proven them selves wrong, zfs is
> still a very handy fs and it should not be judged relative to these
> statements.

I don't disagree with you, I'm just noteing that too many of the 'ZFS is
great' posts need to be discounted as a result (the same thing goes for
the 'reiserfs4 is great' posts)

>>  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.
>
> One could install pg on solaris 10 and format the data-area as ufs and
> then as zfs and compare import- and query-times and other benchmarking
> but comparing ufs/zfs to Linux-filesystems would also be a comparison
> of those two os'es.

however, such a comparison is very legitimate, it doesn't really matter
which filesystem is better if the OS that it's tied to limits it so much
that the other one wins out with an inferior filesystem

currently ZFS is only available on Solaris, parts of it have been released
under GPLv2, but it doesn't look like enough of it to be ported to Linux
(enough was released for grub to be able to access it read-only, but not
the full filesystem). there are also patent concerns that are preventing
any porting to Linux.

on the other hand, it's integrated userspace tools are pushing people to
create similar tools for Linux (without needeing to combine the vairous
pieces in the kernel)

David Lang

Re: Best OS for Postgres 8.2

From
Trygve Laugstøl
Date:
david@lang.hm wrote:
> On Tue, 8 May 2007, Claus Guttesen 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.
>>
>> Even though those posters may have proven them selves wrong, zfs is
>> still a very handy fs and it should not be judged relative to these
>> statements.
>
> I don't disagree with you, I'm just noteing that too many of the 'ZFS is
> great' posts need to be discounted as a result (the same thing goes for
> the 'reiserfs4 is great' posts)
>
>>>  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.
>>
>> One could install pg on solaris 10 and format the data-area as ufs and
>> then as zfs and compare import- and query-times and other benchmarking
>> but comparing ufs/zfs to Linux-filesystems would also be a comparison
>> of those two os'es.
>
> however, such a comparison is very legitimate, it doesn't really matter
> which filesystem is better if the OS that it's tied to limits it so much
> that the other one wins out with an inferior filesystem
>
> currently ZFS is only available on Solaris, parts of it have been
> released under GPLv2, but it doesn't look like enough of it to be ported
> to Linux (enough was released for grub to be able to access it
> read-only, but not the full filesystem). there are also patent concerns
> that are preventing any porting to Linux.

This is not entirely correct. ZFS is only under the CDDL license and it
has been ported to FreeBSD.

http://mail.opensolaris.org/pipermail/zfs-discuss/2007-April/026922.html

--
Trygve

Re: Best OS for Postgres 8.2

From
"Steinar H. Gunderson"
Date:
On Mon, May 07, 2007 at 11:56:14PM -0400, Greg Smith wrote:
> Debian packages PostgreSQL in a fashion unique to it; it's arguable
> whether it's better or not (I don't like it), but going with that will
> assure your installation is a bit non-standard compared with most Linux
> installas.  The main reasons you'd pick Debian are either that you like
> that scheme (which tries to provide some structure to running multiple
> clusters on one box), or that you plan to rely heavily on community
> packages that don't come with the Redhat distributions and therefore would
> appreciate how easy it is to use apt-get against the large Debian software
> repository.

Just to add to this: As far as I understand it, this scheme was originally
mainly put in place to allow multiple _versions_ of Postgres to be installed
alongside each other, for smoother upgrades. (There's a command that does all
the details of running first pg_dumpall for the users and groups, then the
new pg_dump with -Fc to get all data and LOBs over, then some hand-fixing to
change explicit paths to $libdir, etc...)

Of course, you lose all that if you need a newer Postgres version than the OS
provides. (Martin Pitt, the Debian/Ubuntu maintainer of Postgres -- the
packaging in Debian and Ubuntu is the same, sans version differences -- makes
his own backported packages of the newest Postgres to Debian stable; it's up
to you if you'd trust that or not.)

/* Steinar */
--
Homepage: http://www.sesse.net/

Re: Best OS for Postgres 8.2

From
"Alexander Staubo"
Date:
On 5/8/07, david@lang.hm <david@lang.hm> wrote:
[snip]
> I personally don't trust reiserfs, jfs seems to be a tools for
> transitioning from AIX more then anything else [...]

What makes you say this? I have run JFS for years with complete
satisfaction, and I have never logged into an AIX box.

JFS has traditionally been seen as an underdog, but undeservedly so,
in my opinion; one cause might be the instability of the very early
releases, which seems to have tainted its reputation, or the alienness
of its AIX heritage. However, every benchmark I have come across puts
its on par with, and often surpassing, the more popular file systems
in performance. In particular, JFS seems to shine with respect to CPU
overhead.

Alexander.

Re: Best OS for Postgres 8.2

From
"Steinar H. Gunderson"
Date:
On Mon, May 07, 2007 at 03:14:08PM -0700, Joshua D. Drake wrote:
> It is my understanding (and I certainly could be wrong) that FreeBSD
> doesn't handle SMP nearly as well as Linux (and Linux not as well as
> Solaris).

I'm not actually sure about the last part. There are installations as big as
1024 CPUs that run Linux -- most people won't need that, but it's probably an
indicator that eight cores should run OK :-)

/* Steinar */
--
Homepage: http://www.sesse.net/

Re: Best OS for Postgres 8.2

From
david@lang.hm
Date:
On Tue, 8 May 2007, Trygve Laugstøl wrote:

>>  currently ZFS is only available on Solaris, parts of it have been released
>>  under GPLv2, but it doesn't look like enough of it to be ported to Linux
>>  (enough was released for grub to be able to access it read-only, but not
>>  the full filesystem). there are also patent concerns that are preventing
>>  any porting to Linux.
>
> This is not entirely correct. ZFS is only under the CDDL license and it has
> been ported to FreeBSD.
>
> http://mail.opensolaris.org/pipermail/zfs-discuss/2007-April/026922.html
>
I wonder how they handled the license issues? I thought that if you
combined stuff that was BSD licensed with stuff with a more restrictive
license the result was under the more restrictive license. thanks for the
info.

here's a link about the GPLv2 stuff for zfs

http://blogs.sun.com/darren/entry/zfs_under_gplv2_already_exists

Re: Best OS for Postgres 8.2

From
david@lang.hm
Date:
On Tue, 8 May 2007, Steinar H. Gunderson wrote:

> On Mon, May 07, 2007 at 03:14:08PM -0700, Joshua D. Drake wrote:
>> It is my understanding (and I certainly could be wrong) that FreeBSD
>> doesn't handle SMP nearly as well as Linux (and Linux not as well as
>> Solaris).
>
> I'm not actually sure about the last part. There are installations as big as
> 1024 CPUs that run Linux -- most people won't need that, but it's probably an
> indicator that eight cores should run OK :-)

over the weekend the question of scalability was raised on the linux
kernel mailing list and people are shipping 1024 cpu systems with linux,
and testing 4096 cpu systems. there are occasionally still bottlenecks
that limit scalability, butunless you run into a bad driver or filesystem
you should have no problems in the 8-16 core range.

any comparison between Linux and any other OS needs to include a date for
when the comparison was made, Linux is changing at a frightning pace (I
think I saw something within the last few weeks that said that the rate of
change for the kernel has averaged around 9000 lines of code per day over
the last couple of years) you need to re-check comparisons every year or
two or you end up working with obsolete data.

David Lang

Re: Best OS for Postgres 8.2

From
Trygve Laugstøl
Date:
david@lang.hm wrote:
> On Tue, 8 May 2007, Trygve Laugstøl wrote:
>
>>>  currently ZFS is only available on Solaris, parts of it have been
>>> released
>>>  under GPLv2, but it doesn't look like enough of it to be ported to
>>> Linux
>>>  (enough was released for grub to be able to access it read-only, but
>>> not
>>>  the full filesystem). there are also patent concerns that are
>>> preventing
>>>  any porting to Linux.
>>
>> This is not entirely correct. ZFS is only under the CDDL license and
>> it has been ported to FreeBSD.
>>
>> http://mail.opensolaris.org/pipermail/zfs-discuss/2007-April/026922.html
>>
> I wonder how they handled the license issues? I thought that if you
> combined stuff that was BSD licensed with stuff with a more restrictive
> license the result was under the more restrictive license. thanks for
> the info.

The CDDL is not a restrictive license like GPL, it is based on the MIT
license so it can be used with BSD stuff without problems. There are
lots of discussion going on (read: flamewars) on the opensolaris lists
about how it can/should it/will it be integrated into linux.

> here's a link about the GPLv2 stuff for zfs
>
> http://blogs.sun.com/darren/entry/zfs_under_gplv2_already_exists

That title is fairly misleading as it's only some read-only bits to be
able to boot off ZFS with grub.

--
Trygve

Re: [OT] Best OS for Postgres 8.2

From
"C. Bergström"
Date:
I'm really not a senior member around here and while all this licensing
stuff and underlying fs between OSs is very interesting can we please
think twice before continuing it.

Thanks for the minute,

./C

Re: Best OS for Postgres 8.2

From
"Luke Lonergan"
Date:
WRT ZFS on Linux, if someone were to port it, the license issue would get worked out IMO (with some discussion to back
meup).  From discussions with the developers, the biggest issue is a technical one: the Linux VFS layer makes the port
difficult.

I don't hold any hope that the FUSE port will be a happy thing, the performance won't be there.

Any volunteers to port ZFS to Linux?

- Luke


Re: [OT] Best OS for Postgres 8.2

From
Adam Tauno Williams
Date:
> I'm really not a senior member around here and while all this licensing
> stuff and underlying fs between OSs is very interesting can we please
> think twice before continuing it.

Agree, there are other lists for this stuff;  and back to what one of
the original posters said: it doesn't matter much.

[Also not a regular poster, but I always gain something from reading
this list.]

Most people who really go into OS selection /  FS selection are looking
for a cheap/silver bullet for performance.  No such thing exists.  The
difference made by any modern OS/FS is almost immaterial.  You need to
do the slow slogging work of site/application specific optimization and
tuning;  that is where you will find significant performance
improvements.

-
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org


Re: Best OS for Postgres 8.2

From
Ron
Date:
I've seen the FUSE port of ZFS, and it does run sslloowwllyy.  It
appears that a native linux port is going to be required if we want
ZFS to be reasonably performant.

WRT which FS to use for pg; the biggest issue is what kind of DB you
will be building.  The best pg FS for OLTP and OLAP are not the same
IME.  Ditto a dependence on how large your records and the amount of
IO in your typical transactions are.

For lot's of big, more reads than writes transactions, SGI's XFS
seems to be best.
XFS is not the best for OLTP.  Especially for OLTP involving lots of small IOs.

jfs seems to be best for that.

Caveat: I have not yet experimented with any version of reiserfs in
production.

Cheers,
Ron Peacetree


At 08:01 AM 5/8/2007, Luke Lonergan wrote:
>WRT ZFS on Linux, if someone were to port it, the license issue
>would get worked out IMO (with some discussion to back me up).  From
>discussions with the developers, the biggest issue is a technical
>one: the Linux VFS layer makes the port difficult.
>
>I don't hold any hope that the FUSE port will be a happy thing, the
>performance won't be there.
>
>Any volunteers to port ZFS to Linux?
>
>- Luke
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings


Re: Best OS for Postgres 8.2

From
李彦 Ian Li
Date:
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

Re: Best OS for Postgres 8.2

From
Charles Sprickman
Date:
On Tue, 8 May 2007, david@lang.hm wrote:

> 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.

That sounds like an ad for FreeBSD and UFS2+Softupdates. :)

Metadata is as safe as it is in a journaling filesystem, but none of the
overhead of journaling.

Charles

> 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
>>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>

Re: Best OS for Postgres 8.2

From
Greg Smith
Date:
On Tue, 8 May 2007, Tom Lane wrote:

> What Debian has done is set up an arrangement that lets you run two (or
> more) different PG versions in parallel.  Since that's amazingly helpful
> during a major-PG-version upgrade, most of the other packagers are
> scheming how to do something similar.

I alluded to that but it is worth going into more detail on for those not
familiar with this whole topic.  I normally maintain multiple different PG
versions in parallel already, mostly using environment variables to switch
between them with some shell code.  Debian has taken an approach where
commands like pg_ctl are wrapped in multi-version/cluster aware scripts,
so you can do things like restarting multiple installations more easily
than that.

My issue wasn't with the idea, it was with the implementation.  When I
have my newbie hat on, it adds a layer of complexity that isn't needed for
simple installs.  And when I have my developer hat on, I found that need
to conform to the requirements of that system on top of Debian's already
unique install locations and packaging issues just made it painful to
build and work with with customized versions of Postgres, compared to
distributions that use a relatively simple packaging scheme (like the RPM
based RedHat or SuSE).

I hope anyone else working this problem is thinking about issues like
this.  Debian's approach strikes me as being a good one for a seasoned
systems administrator or DBA, which is typical for them.  I'd hate to see
a change in this area make it more difficult for new users though, as
that's already perceived as a PG weakness.  I think you can build a layer
that adds the capability for the people who need it without complicating
things for people who don't.

> and if someday you want commercial support for your OS, a Centos->RHEL
> update will get you there easily.

For those that like to live dangerously, it's also worth mentioning that
it's possible to hack this conversion in either direction without actually
doing an OS re-install/upgrade just by playing with the packages that are
different between the two.  So someone who installs CentOS now could swap
to RHEL very quickly in a pinch if they have enough cojones to do the
required package substitutions.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Best OS for Postgres 8.2

From
Greg Smith
Date:
On Tue, 8 May 2007, Luke Lonergan wrote:

> From discussions with the developers, the biggest issue is a technical
> one: the Linux VFS layer makes the [ZFS] port difficult.

Difficult on two levels.  First you'd have to figure out how to make it
work at all; then you'd have to reshape it into a form that it would be
acceptable to the Linux kernel developers, who haven't seemed real keen on
the idea so far.

The standard article I'm you've already seen this week on this topic is
Jeff Bonwick's at
http://blogs.sun.com/bonwick/entry/rampant_layering_violation

What really bugged me was his earlier article linked to there where he
talks about how ZFS eliminates the need for hardware RAID controllers:
http://blogs.sun.com/bonwick/entry/raid_z

While there may be merit to that idea for some applications, like
situations where you have a pig of a RAID5 volume, that's just hype for
database writes.  "We issue the SYNCHRONIZE CACHE command to the disks
after pushing all data in a transaction group"--see, that would be the
part the hardware controller is needed to accelerate.  If you really care
about whether your data hit disk, there is no way to break the RPM barrier
without hardware support.  The fact that he misunderstands such a
fundamental point makes me wonder what other gigantic mistakes might be
buried in his analysis.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Best OS for Postgres 8.2

From
david@lang.hm
Date:
On Tue, 8 May 2007, Greg Smith wrote:

> On Tue, 8 May 2007, Luke Lonergan wrote:
>
>>  From discussions with the developers, the biggest issue is a technical
>>  one: the Linux VFS layer makes the [ZFS] port difficult.
>
> Difficult on two levels.  First you'd have to figure out how to make it work
> at all; then you'd have to reshape it into a form that it would be acceptable
> to the Linux kernel developers, who haven't seemed real keen on the idea so
> far.

given that RAID, snapshots, etc are already in the linux kernel, I suspect
that what will need to happen is for the filesystem to be ported without
those features and then the userspace tools (that manipulate the volumes )
be ported to use the things already in the kernel.

> The standard article I'm you've already seen this week on this topic is Jeff
> Bonwick's at http://blogs.sun.com/bonwick/entry/rampant_layering_violation

yep, that sounds like what I've been hearing.

what the ZFS (and reiserfs4) folks haven't been wanting to hear from the
linux kernel devs is that they are interested in having all these neat
features available for use with all filesystems (and the linux kernel has
a _lot_ of filesystems available), with solaris you basicly have UFS and
ZFS so it's not as big a deal.

> What really bugged me was his earlier article linked to there where he talks
> about how ZFS eliminates the need for hardware RAID controllers:
> http://blogs.sun.com/bonwick/entry/raid_z
>
> While there may be merit to that idea for some applications, like situations
> where you have a pig of a RAID5 volume, that's just hype for database writes.
> "We issue the SYNCHRONIZE CACHE command to the disks after pushing all data
> in a transaction group"--see, that would be the part the hardware controller
> is needed to accelerate.  If you really care about whether your data hit
> disk, there is no way to break the RPM barrier without hardware support.  The
> fact that he misunderstands such a fundamental point makes me wonder what
> other gigantic mistakes might be buried in his analysis.

I've seen similar comments from some of the linux kernel devs, they've
used low-end raid controllers with small processors on them and think that
a second core/socket in the main system to run software raid on is better.

David Lang

Re: Best OS for Postgres 8.2

From
"Steinar H. Gunderson"
Date:
On Wed, May 09, 2007 at 01:57:51AM -0700, david@lang.hm wrote:
> given that RAID, snapshots, etc are already in the linux kernel, I suspect
> that what will need to happen is for the filesystem to be ported without
> those features and then the userspace tools (that manipulate the volumes )
> be ported to use the things already in the kernel.

Well, part of the idea behind ZFS is that these parts are _not_ separated in
"layers" -- for instance, the filesystem can push data down to the RAID level
to determine the stripe size used.

Whether this is a good idea is of course hotly debated, but I don't think you
can port just the filesystem part and call it a day.

/* Steinar */
--
Homepage: http://www.sesse.net/

Re: Best OS for Postgres 8.2

From
david@lang.hm
Date:
On Wed, 9 May 2007, Steinar H. Gunderson wrote:

> On Wed, May 09, 2007 at 01:57:51AM -0700, david@lang.hm wrote:
>> given that RAID, snapshots, etc are already in the linux kernel, I suspect
>> that what will need to happen is for the filesystem to be ported without
>> those features and then the userspace tools (that manipulate the volumes )
>> be ported to use the things already in the kernel.
>
> Well, part of the idea behind ZFS is that these parts are _not_ separated in
> "layers" -- for instance, the filesystem can push data down to the RAID level
> to determine the stripe size used.

there's nothing preventing this from happening if they are seperate layers
either.

there are some performance implications of the seperate layers, but until
someone has the ability to do head-to-head comparisons it's hard to say
which approach will win (in theory the lack of layers makes for faster
code, but in practice the fact that each layer is gone over by experts
looking for ways to optimize it may overwelm the layering overhead)

> Whether this is a good idea is of course hotly debated, but I don't think you
> can port just the filesystem part and call it a day.

Oh, I'm absolutly sure that doing so won't satidfy people (wnd would
generate howles of outrage from some parts), but having watched other
groups try and get things into the kernel that the kernel devs felt were
layering violations  I think that it's wat will ultimatly happen.

David Lang

ZFS and Postgresql - WASRe: Best OS for Postgres 8.2

From
Jignesh Shah
Date:
Hello Ian,

I have done some testing with postgresql and ZFS on Solaris 10 11/06.
While I work for Sun, I dont claim to be a ZFS expert (for that matter
not even Solaris or PostgreSQL).

Lets first look at the scenarios of how postgresql can be deployed on
Solaris
First the Solaris Options
1. UFS with default setup (which is buffered file system)
2. UFS with forcedirectio option (or unbuffered file system)
3. ZFS by default (128K recordsize with checksum but no compression)
4. ZFS with Compression (Default compression using LZ* algorithm .. now
even a gzip algorithm is supported)

(For simplicity I am not considering RAID levels here since that
increases the number of scenarios quite a bit and also skipping Solaris
Volume Manager - legacy volume management capabilities in Solaris)

Now for the postgresql.conf options
a. wal_sync_method  set to default - maps to opendatasync
b. wal_sync_method set to fdatasync

(assuming checkpoint_segments and wal_buffers are high already)

(This are my tests results  based on the way I used the workload and
your mileage will vary)
So with this type of configurations I found the following
1a. Default UFS with default wal_sync_method - Sucks for me mostly
using pgbench or EAStress type workloads
1b. Default UFS with fdatasync - works well  specially increasing
segmapsize from default 12% to higher values
2a  ForcedirectIO with  default wal_sync_method - works well but then is
limited to hardware disk performances
     (In a way good to have RAID controller with big Write cache for
it.. One advantage is lower system cpu utilization)
2b Didn't see huge difference from 2a in this case
3a  It was better than 1a but still limited
3b  It was better even than 3a and 1b but cpu utilization seemed higher
4a   - Didn't test this out
4b  - Hard to say since in my case since I wasnt disk bound (per se) but
CPU bound. The compression helps when number of IOs to the disk are high
and it helps to cut it down at the cost of CPU cycles


Overall ZFS seems to improve performance with PostgreSQL on Solaris 10
with a bit increased system times compared to UFS.
(So the final results depends on the metrics that you are measuring the
performance :-) ) (ZFS engineers are constantly improving the
performance and I have seen the improvements from Solaris 10 1/06
release to my current setup)

Of course I haven't compared against any other OS.. If someone has
already done that I would be interested in knowing the results.

Now comes the thing that I am still exploring
* Do we do checksum in WAL ? I guess we do .. Which means that we are
now doing double checksumming on the data. One in ZFS and one in
postgresql. ZFS does allow checksumming to be turned off (but on new
blocks allocated). But of course the philosophy is where should it be
done (ZFS or PostgreSQL). ZFS checksumming gives ability to  correct the
data on the bad checksum if you use mirror devices. PostgreSQL doesnt
give that ability and in case of an error would fail. ( I  dont know the
exact behavior of postgresql when it would encounter a failed checksum)

Hope this helps.


Regards,
Jignesh



李彦 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?
>
> 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

Re: ZFS and Postgresql - WASRe: Best OS for Postgres 8.2

From
Alvaro Herrera
Date:
Jignesh Shah escribió:

> Now comes the thing that I am still exploring
> * Do we do checksum in WAL ? I guess we do .. Which means that we are
> now doing double checksumming on the data. One in ZFS and one in
> postgresql. ZFS does allow checksumming to be turned off (but on new
> blocks allocated). But of course the philosophy is where should it be
> done (ZFS or PostgreSQL).

Checksums on WAL are not optional in Postgres, because AFAIR they are
used to determine when it should stop recovering.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Best OS for Postgres 8.2

From
Jim Nasby
Date:
On May 8, 2007, at 2:59 AM, david@lang.hm wrote:
> 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.

That's why you want to mount ext3 partitions used with PostgreSQL
with data=writeback.

Some folks will also use a small filesystem for pg_xlog and mount
that as ext2.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



Re: ZFS and Postgresql - WASRe: Best OS for Postgres 8.2

From
Jignesh Shah
Date:
But we still pay the penalty on WAL while writing them in the first
place I guess .. Is there an option to disable it.. I can test how much
is the impact I guess couple of %s but good to verify :-) )


Regards,
Jignesh


Alvaro Herrera wrote:
> Jignesh Shah escribió:
>
>
>> Now comes the thing that I am still exploring
>> * Do we do checksum in WAL ? I guess we do .. Which means that we are
>> now doing double checksumming on the data. One in ZFS and one in
>> postgresql. ZFS does allow checksumming to be turned off (but on new
>> blocks allocated). But of course the philosophy is where should it be
>> done (ZFS or PostgreSQL).
>>
>
> Checksums on WAL are not optional in Postgres, because AFAIR they are
> used to determine when it should stop recovering.
>
>

Re: ZFS and Postgresql - WASRe: Best OS for Postgres 8.2

From
david@lang.hm
Date:
On Wed, 9 May 2007, Jignesh Shah wrote:

> But we still pay the penalty on WAL while writing them in the first place I
> guess .. Is there an option to disable it.. I can test how much is the impact
> I guess couple of %s but good to verify :-) )

on modern CPU's where the CPU is significantly faster then RAM,
calculating a checksum is free if the CPU has to touch the data anyway
(cycles where it would be waiting for a cache miss are spent doing the
calculations)

if you don't believe me, hack the source to remove the checksum and see if
you can measure any difference.

David Lang

  >
> Regards,
> Jignesh
>
>
> Alvaro Herrera wrote:
>>  Jignesh Shah escribió:
>>
>>
>> >  Now comes the thing that I am still exploring
>> >  * Do we do checksum in WAL ? I guess we do .. Which means that we are
>> >  now doing double checksumming on the data. One in ZFS and one in
>> >  postgresql. ZFS does allow checksumming to be turned off (but on new
>> >  blocks allocated). But of course the philosophy is where should it be
>> >  done (ZFS or PostgreSQL).
>> >
>>
>>  Checksums on WAL are not optional in Postgres, because AFAIR they are
>>  used to determine when it should stop recovering.
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

Re: Best OS for Postgres 8.2

From
Adam Witney
Date:

> currently ZFS is only available on Solaris, parts of it have been released
> under GPLv2, but it doesn't look like enough of it to be ported to Linux
> (enough was released for grub to be able to access it read-only, but not
> the full filesystem). there are also patent concerns that are preventing
> any porting to Linux.

I don't know if anyone mentioned this in the thread already, but it looks
like ZFS may be coming to MacOSX 10.5

http://news.worldofapple.com/archives/2006/12/17/zfs-file-system-makes-it-to
-mac-os-x-leopard/


Re: Best OS for Postgres 8.2

From
Robert Treat
Date:
On Tuesday 08 May 2007 23:31, Greg Smith wrote:
> On Tue, 8 May 2007, Tom Lane wrote:
> > What Debian has done is set up an arrangement that lets you run two (or
> > more) different PG versions in parallel.  Since that's amazingly helpful
> > during a major-PG-version upgrade, most of the other packagers are
> > scheming how to do something similar.
>
> I alluded to that but it is worth going into more detail on for those not
> familiar with this whole topic.  I normally maintain multiple different PG
> versions in parallel already, mostly using environment variables to switch
> between them with some shell code.  Debian has taken an approach where
> commands like pg_ctl are wrapped in multi-version/cluster aware scripts,
> so you can do things like restarting multiple installations more easily
> than that.
>
> My issue wasn't with the idea, it was with the implementation.  When I
> have my newbie hat on, it adds a layer of complexity that isn't needed for
> simple installs.

I think I would disagree with this. The confusion comes from the fact that it
is different, not that it is more complex.  For new users what seems to be
most confusing is getting from install to initdb to logging in... if you tell
them to use pg_ctlcluster rather than pg_ctl, it isn't more confusing, there
just following directions at that point anyway.  If the upstream project were
to switch to debian's system, I think you'd end most of the confusion, make
it easier to run concurrent servers and simplify the upgrade process for
source installs, and give other package maintiners a way to achive what
debian has.  Maybe in PG 9...

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Re: Best OS for Postgres 8.2

From
Andrew McMillan
Date:
On Tue, 2007-05-08 at 23:31 -0400, Greg Smith wrote:
>
> My issue wasn't with the idea, it was with the implementation.  When I
> have my newbie hat on, it adds a layer of complexity that isn't needed for
> simple installs.

I find it very hard to agree with that.

As a newbie I install postgresql and have a database server installed,
and operating.  The fact that the DB files are installed somewhere
like /var/lib/postgresql/8.1/main is waaay beyond newbie.

At that point I can "createdb" or "createuser", but I _do_ _not_ need to
know anything about the cluster stuff until there is more than one DB on
the machine.

The Debian wrappers all default appropriately for the single-cluster
case, so having a single cluster has added _no_ perceivable complexity
for a newbie (as it should).

If you have a second cluster, whether it's the same Pg version or not,
things necessarily start to get complicated.  OTOH I haven't had any
problem explaining to people that the --cluster option applies, and
there are sane ways to make that default to a reasonable thing as well.

All in all I think that the Debian scripts are excellent.  I'm sure
there are improvements that could be made, but overall they don't get in
the way, they do the right thing in the minimal case, and they give the
advanced user a lot more choices about multiple DB instances on the same
machine.

Cheers,
                    Andrew McMillan.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
         Open Source: the difference between trust and antitrust
-------------------------------------------------------------------------


Attachment