Thread: Re: [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?) Merlin
>> 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.
[mwoodward@penguin-021 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
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
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 pgsql@mohawksoft.com 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. | | [mwoodward@penguin-021 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 10000 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-----
> 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