Re: [PERFORM] [HACKERS] fsync vs open_sync
Guys, just so you know: OSDL did some testing and found Ext3 to be perhaps the worst FS for PostgreSQL -- although this testing was with the default options. Ext3 involved an almost 40% write performance penalty compared with Ext2, whereas the penalty for ReiserFS and JFS was less than 10%. This concurs with my personal experience. I had exactly the same experience ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] fsync vs open_sync
On Tue, 2004-08-10 at 10:18 -0700, Josh Berkus wrote: Guys, just so you know: OSDL did some testing and found Ext3 to be perhaps the worst FS for PostgreSQL -- although this testing was with the default options. Ext3 involved an almost 40% write performance penalty compared with Ext2, whereas the penalty for ReiserFS and JFS was less than 10%. This concurs with my personal experience. Yes, I have been wondering about the relative trade offs between underlying file systems and pgsql. For metadata journalled filesystems, wouldn't fdatasync be a better option, since the fs is journalling the metadata anyway? With its default settings (data=ordered), ext3 is making a guaranty that after a crash, the filesystem will not only be in a consistent state, but the files (including the WAL) will not contain garbage, though their contents may not be the latest. With reiserfs and JFS, files can contain garbage. (I'm not sure what the implications of all this for pgsql are.) And wouldn't the following comparisons with ext3 be more interesting: ext3,data=writeback,fdatasync vs Other_Journalled_FS,fdatasync or ext3,data=journal,open_sync vs Other_Journalled_FS,fdatasync Just wondering. -Steve ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [HACKERS] fsync vs open_sync
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: |Anyway, with fsync enabled using standard fsync(), I get roughly | |300-400 | |inserts per second. With fsync disabled, I get about 7000 inserts per |second. When I re-enable fsync but use the open_sync option, I can get |about 2500 inserts per second. | |You are getting 300-400 inserts/sec with fsync on? If you don't mind me |asking, what's your hardware? (also, have you checked fsync on #s with |the new bgwriter in 7.5?) | | | | 300 inserts persecond with fsync on using fdatasync. 2500 inserts per | second with fsync on using open_sync. | | [EMAIL PROTECTED] mwoodward]$ cat /proc/cpuinfo | processor : 0 | vendor_id : GenuineIntel | cpu family : 15 | model : 2 | model name : Intel(R) Xeon(TM) CPU 2.40GHz | stepping: 5 | cpu MHz : 2399.373 | cache size : 512 KB | fdiv_bug: no | hlt_bug : no | f00f_bug: no | coma_bug: no | fpu : yes | fpu_exception : yes | cpuid level : 2 | wp : yes | flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca | cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid | bogomips: 4784.12 | | Linux node1 2.4.25 #1 Mon Mar 22 13:33:41 EST 2004 i686 i686 i386 GNU/Linux | | ide2: BM-DMA at 0xc400-0xc407, BIOS settings: hde:pio, hdf:pio | hde: Maxtor 6Y200P0, ATA DISK drive | hde: attached ide-disk driver. | hde: host protected area = 1 | hde: 398297088 sectors (203928 MB) w/7936KiB Cache, CHS=24792/255/63, | UDMA(100) | | PDC20268: IDE controller at PCI slot 06:05.0 I did some experiments too: inserting 1 rows in a table with an integer column: fsync=false ~7.5 secs 1300 insert/sec wal_sync_method=fsync ~15.5 secs 645 insert/sec wal_sync_method=fdatasync ~15.5 secs 645 insert/sec wal_sync_method=open_sync ~10.0 secs 1000 insert/sec wal_sync_method=open_datasync the server doesn't start Test bed: Postgresql 8.0beta1, linux kernel 2.4.22, ~ hda: IC35L060AVVA07-0, ATA DISK drive ~ hda: 120103200 sectors (61493 MB) w/1863KiB Cache, CHS=7476/255/63, UDMA(100) # cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family: 6 model : 8 model name: Pentium III (Coppermine) stepping : 6 cpu MHz : 877.500 cache size: 256 KB physical id : 0 siblings : 1 fdiv_bug : no hlt_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 2 wp: yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse runqueue : 0 bogomips : 1749.81 processor : 1 vendor_id : GenuineIntel cpu family: 6 model : 8 model name: Pentium III (Coppermine) stepping : 6 cpu MHz : 877.500 cache size: 256 KB physical id : 0 siblings : 1 fdiv_bug : no hlt_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 2 wp: yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse runqueue : 1 bogomips : 1749.81 Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGrSE7UpzwH2SGd4RAoXnAKCHhuw/pWKgY+OD3JcWYMTPDbmgZwCgyqfT +OugUEvUF8usYYrWSGDAnn4= =FAaI -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [HACKERS] fsync vs open_sync
Anyway, with fsync enabled using standard fsync(), I get roughly 300-400 inserts per second. With fsync disabled, I get about 7000 inserts per second. When I re-enable fsync but use the open_sync option, I can get about 2500 inserts per second. You are getting 300-400 inserts/sec with fsync on? If you don't mind me asking, what's your hardware? (also, have you checked fsync on #s with the new bgwriter in 7.5?) 300 inserts persecond with fsync on using fdatasync. 2500 inserts per second with fsync on using open_sync. [EMAIL PROTECTED] mwoodward]$ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Xeon(TM) CPU 2.40GHz stepping: 5 cpu MHz : 2399.373 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid bogomips: 4784.12 Linux node1 2.4.25 #1 Mon Mar 22 13:33:41 EST 2004 i686 i686 i386 GNU/Linux ide2: BM-DMA at 0xc400-0xc407, BIOS settings: hde:pio, hdf:pio hde: Maxtor 6Y200P0, ATA DISK drive hde: attached ide-disk driver. hde: host protected area = 1 hde: 398297088 sectors (203928 MB) w/7936KiB Cache, CHS=24792/255/63, UDMA(100) PDC20268: IDE controller at PCI slot 06:05.0 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] fsync vs open_sync
Guys, just so you know: OSDL did some testing and found Ext3 to be perhaps the worst FS for PostgreSQL -- although this testing was with the default options. Ext3 involved an almost 40% write performance penalty compared with Ext2, whereas the penalty for ReiserFS and JFS was less than 10%. This concurs with my personal experience. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
