Thread: Re: [HACKERS] fsync vs open_sync

Re: [HACKERS] fsync vs open_sync

From
"Merlin Moncure"
Date:
> 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



Re: [HACKERS] fsync vs open_sync

From
pgsql@mohawksoft.com
Date:
>> 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


Re: [HACKERS] fsync vs open_sync

From
Josh Berkus
Date:
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

Re: [HACKERS] fsync vs open_sync

From
Steve Bergman
Date:
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


Re: [HACKERS] fsync vs open_sync

From
Gaetano Mendola
Date:
-----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-----


Re: [HACKERS] fsync vs open_sync

From
pgsql@mohawksoft.com
Date:
> 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