Обсуждение: 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). +----------------------------------+------------------------------------+ | 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 | | +----------------------------------+------------------------------------+
> 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, Pennsylvania19026
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
> 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, Pennsylvania19026
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
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 | | +----------------------------------+------------------------------------+
> 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, Pennsylvania19026
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@acm.org | | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 | | +----------------------------------+------------------------------------+
> 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 I don't believe any mention has been made in the docs yet. Seems we are still exploring this. -- 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, Pennsylvania19026