I have been using PostgreSQL and XFS file systems on SGI's for many
years, and PostgreSQL is fast. Dumping and loading 100GB of table
files takes less than one day elapsed (provided there is no other
activity on that database -- large amounts of transactional activity
will slow things down).  I always turn off fsync. Most of my experience has
been with 6.5.3, although I've been using 7.1 and I don't see much
of a difference in performance.

I don't know if the Linux version of XFS is substantially different
than the SGI version, but XFS is a wonderful filesystem to use and
administer (at least on SGI's).

+----------------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D.      | Phone: 609 737 6383                |
| President, Congenomics, Inc.     | Fax:   609 737 7528                |
| 114 W Franklin Ave, Suite K1,4,5 | email: bruc@acm.org                |
| P.O. Box 314                     | URL:   http://www.congen.com/~bruc |
| Pennington, NJ 08534             |                                    |
+----------------------------------+------------------------------------+

Wed, 2 May 2001 18:45:16 -0400 (EDT)
bruc@stone.congenomics.com (Robert E. Bruccoleri)
XFS File systems and PostgreSQL
bruc@stone.congenomics.com (Robert E. Bruccoleri) writes:

> I have been using PostgreSQL and XFS file systems on SGI's for many
> years, and PostgreSQL is fast. Dumping and loading 100GB of table
> files takes less than one day elapsed (provided there is no other
> activity on that database -- large amounts of transactional activity
> will slow things down).  I always turn off fsync. 
                           ^^^^^^^^^^^^^^^^^^^^^^^

Then your performance numbers are largely useless for those of us that 
like our data.  ;)

The point at issue is the performance of fsync() on journaling
filesystems...

-Doug
-- 
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

02 May 2001 19:44:42 -0400
Doug McNaught <doug@wireboard.com>
Re: XFS File systems and PostgreSQL
> I have been using PostgreSQL and XFS file systems on SGI's for many
> years, and PostgreSQL is fast. Dumping and loading 100GB of table
> files takes less than one day elapsed (provided there is no other
> activity on that database -- large amounts of transactional activity
> will slow things down).  I always turn off fsync. Most of my experience has
> been with 6.5.3, although I've been using 7.1 and I don't see much
> of a difference in performance.
> 
> I don't know if the Linux version of XFS is substantially different
> than the SGI version, but XFS is a wonderful filesystem to use and
> administer (at least on SGI's).

Yes, but you turn off fsync.  It is with fsync on that XFS will be slow.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 2 May 2001 19:51:04 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: XFS File systems and PostgreSQL
> bruc@stone.congenomics.com (Robert E. Bruccoleri) writes:
> 
> > I have been using PostgreSQL and XFS file systems on SGI's for many
> > years, and PostgreSQL is fast. Dumping and loading 100GB of table
> > files takes less than one day elapsed (provided there is no other
> > activity on that database -- large amounts of transactional activity
> > will slow things down).  I always turn off fsync. 
>                            ^^^^^^^^^^^^^^^^^^^^^^^
> 
> Then your performance numbers are largely useless for those of us that 
> like our data.  ;)
> 
> The point at issue is the performance of fsync() on journaling
> filesystems...

Yes, the irony is that a journaling file system is being used to have
fast, reliable restore after crash bootup, but with no fsync, the db is
probably hosed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 2 May 2001 20:19:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: XFS File systems and PostgreSQL
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Yes, the irony is that a journaling file system is being used to have
> fast, reliable restore after crash bootup, but with no fsync, the db is
> probably hosed.

It just struck me--is it necessarily true that we get the big
performance hit?  

On a non-data-journaling FS (like ext3), since WAL files are
preallocated (right?), a WAL sync shouldn't involve any metadata
updates.  So we just write the WAL data to a (hopefully contiguous)
chunk of data blocks.

On an FS that journals data AND metadata, fsync() can return once the
updates are committed to the log--it doesn't have to wait until the
log is back-flushed (or whatever you call it) to the main filesystem. 

The above is theoretical, and I don't know enough about Reiser or XFS
to know how they behave. 

-Doug
-- 
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

02 May 2001 20:27:35 -0400
Doug McNaught <doug@wireboard.com>
Re: XFS File systems and PostgreSQL
Dear Bruce,
> 
> Yes, the irony is that a journaling file system is being used to have
> fast, reliable restore after crash bootup, but with no fsync, the db is
> probably hosed.

There is no irony in these cases. In my systems, which are used for
bioinformatics, the updating process is generally restartable. I
normally have lots of data to load or many records to change,
and the quantities are much more than any reasonable
sized transaction. Some jobs run for days. If I lose some data
because of a crash, I just restart the jobs, and they'll delete some
of the last data to be loaded, and then resume. Furthermore, the SGI's
that I run on are highly reliable, and they rarely crash. So, I might
have to clean up a big mess rarely (I've had one really big one in
two years), but performance otherwise is really good. I should
also point out that most of my work has been with PostgreSQL 6.5.3
which doesn't have the WAL.

If I have some time, I will try the experiment of loading a database
of mine into PG 7.1 using -F or not and I'll report the timing.

+----------------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D.      | Phone: 609 737 6383                |
| President, Congenomics, Inc.     | Fax:   609 737 7528                |
| 114 W Franklin Ave, Suite K1,4,5 | email: bruc@acm.org                |
| P.O. Box 314                     | URL:   http://www.congen.com/~bruc |
| Pennington, NJ 08534             |                                    |
+----------------------------------+------------------------------------+

Wed, 2 May 2001 20:49:36 -0400 (EDT)
bruc@stone.congenomics.com (Robert E. Bruccoleri)
Re: XFS File systems and PostgreSQL
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
> > Yes, the irony is that a journaling file system is being used to have
> > fast, reliable restore after crash bootup, but with no fsync, the db is
> > probably hosed.
> 
> It just struck me--is it necessarily true that we get the big
> performance hit?  
> 
> On a non-data-journaling FS (like ext3), since WAL files are
> preallocated (right?), a WAL sync shouldn't involve any metadata
> updates.  So we just write the WAL data to a (hopefully contiguous)
> chunk of data blocks.
> 
> On an FS that journals data AND metadata, fsync() can return once the
> updates are committed to the log--it doesn't have to wait until the
> log is back-flushed (or whatever you call it) to the main filesystem. 
> 
> The above is theoretical, and I don't know enough about Reiser or XFS
> to know how they behave. 

Theoretically, yes, all these log-based file system just log metadata
changes, not user data, so it should not affect it.  I just don't know
how well the fsync's are implemented on these things.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 2 May 2001 20:54:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: XFS File systems and PostgreSQL