Re: Insert Performance with WAL and Fsync

Mike Schroepfer <mike@raplix.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