[HACKERS] 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: [EMAIL PROTECTED]| | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 || +--++ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] XFS File systems and PostgreSQL
[EMAIL PROTECTED] (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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] XFS File systems and PostgreSQL
[EMAIL PROTECTED] (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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] XFS File systems and PostgreSQL
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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: [EMAIL PROTECTED]| | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 || +--++ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] XFS File systems and PostgreSQL
Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
