Re: Insert Performance with WAL and Fsync

Mike Schroepfer <mike(at)raplix(dot)com> writes:
> It appears the CPU utilization on both machines is very low (<15%)- so I'm
> guessing it is mostly I/O overhead.

It looks like your Solaris box is a dual CPU machine? PG 7.1.* suffers
from pretty awful performance on multiprocessors, due to a rather
braindead implementation of spinlocks. If vmstat shows that neither the
CPUs nor the disks are working real hard, then I'd suspect this to be
the problem --- cf
http://archives.postgresql.org/pgsql-hackers/2002-01/msg00449.php
and other recent pghackers threads.

You might care to try your tests on current development sources (*not*
7.2b4; pull from CVS or use a nightly-snapshot tarball). I think we've
improved the SMP performance considerably since 7.1, though more could
probably be done in future. BTW, don't put a production database on
current sources, there's at least two unpleasant known bugs.

> 3) Why does the Solaris performance with fysnc on/off differ
> by a factor of 3.4x while the windows fsync on/off differs
> by only 1.1x? I thought WAL was supposed to dramatically
> reduce the cost of fsyncs?
> 4) Why does the Win2k behavior with fsync and open_sync differ
> so greatly? Is fysnc on cygwin slow or does OPEN_SYNC
> not work properly (i.e. is not really syncing)

I don't know the innards of cygwin, but it would not surprise me in the
least to hear that it doesn't implement fsync & OPEN_SYNC efficiently
and/or correctly. It has to sit atop Windows, which probably doesn't
have compatible APIs to support these behaviors reasonably. The results
you show sure make it look like OPEN_SYNC is a no-op on cygwin...

regards, tom lane