Re: XFS File systems and PostgreSQL

I have run a simple PostgreSQL benchmark on my SGI system which uses
XFS for its file system on all disks to compare the effect of fsync.
The benchmark was the loading of a database from 157 MB of pg_dump data
including the construction of 11 Btree indexes covering nearly all
of the data. The second column was just for the data load,
and the third column is for the index creation.
The system is an SGI Indigo2 R10000 running Irix 6.5.7
with 384 MB RAM writing to Seagate 18GB 7200RPM narrow SCSI disks.

Fsync enabled Elapsed load time Elapsed indexing time
Yes 15:53 9:16
No 10:33 8:40

The CPU is not fully utilized for loading, and thus the system is I/O
bound and the use of fsync has an impact. By contrast, the indexing
process is CPU bound, and fsync is less important.

The performance penalty for using fsync is modest, and therefore,
I do not believe that we should discourage people from using XFS
because it is a journaling file system. The note advising against
installing Postgres on XFS should be removed from the installation
guide. Instead, we need to explore how to use XFS's features to improve
PostgreSQL's performance. For example, the XFS filesystem journal can be
placed on a drive different from the data drive. This would substantially
improve write performance.

+----------------------------------+------------------------------------+
| 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(at)acm(dot)org |
| P.O. Box 314 | URL: http://www.congen.com/~bruc |
| Pennington, NJ 08534 | |
+----------------------------------+------------------------------------+