Insert Performance with WAL and Fsync

Hi All,

I've posted this too admin with no responses - looks like I should have
posted it too general - so here goes:

Our application needs to do a moderate level of inserts into
the database as it runs. We can hit 100-200 inserts a second
under certain conditions - and it is not something we can batch up for
later processing. While testing out postgres I noticed
some troubling performance data under this sort of workload. I've
got a run of pgbench which exhibits the behavior. This variant of
pgbench is a good approximation of our application
and the results mirror those of tests I did with our app - so changing
the scaling, client factors, etc of pgbench is not relevant.

I tried changing most other parameters of postmaster.conf (log files,
buffers,
etc) and none have such dramatic effects as illustrated below.

It appears the CPU utilization on both machines is very low (<15%)- so I'm
guessing it is mostly I/O overhead.

At the bottom of the message is the detailed result of pg_bench -t 500 under
the following conditions:

wal_sync_method = open_sync wal_sync_method = fsync
OS FSYNC OFF FSYNC ON FSYNC ON
Solaris 74/75 22/22 30/30
Win2k 112/113 124/127 21/21

so the following questions arise:

1) How can I improve the Solaris performance without
disabling fsync?
2) What are others getting for TPS on Solaris machines with
fysnc on? Are my numbers low/high/right in the ballpark?
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)
5) Assuming the Win2k open_sync implementation is working
why is my Win2k laptop with a much slower harddrive with fsync
on 5.6x faster than the solaris box.
6) Is there anything I could have done in the building of postgres
from source to cause problems on Solaris?

Any help or insight would be greatly appreciated.

Mike

Here's the specs:
Win2k Sp2
PIII 1.2G/512 mb Cache
512mb Ram
1 4400RPM IDE disk (its a laptop)
Postgres 7.1.3 on cygwin
postmaster.conf default execpt where noted below

Solaris 8 01/01 with relatively recent patch sets
E220R 2x450Mhz USII
2GB Ram
1 36Gig 10000RPM SCSI Disk
Postgres 7.1.2 compiled using gcc
postmaster.conf default execpt where noted below

Here are the results of pg_bench:

Solaris:
wal_sync_method = open_sync
fsync = false

starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 500
number of transactions actually processed: 500/500
tps = 74.825791(including connections establishing)
tps = 75.418729(excluding connections establishing)

Solaris:
wal_sync_method = open_sync
fsync = true

transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 500
number of transactions actually processed: 500/500
tps = 22.691402(including connections establishing)
tps = 22.745163(excluding connections establishing)

Solaris:
wal_sync_method = fsync
fsync = true

transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 500
number of transactions actually processed: 500/500
tps = 30.489359(including connections establishing)
tps = 30.591695(excluding connections establishing)
Windows:
wal_sync_method = open_sync
fsync = true

starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 500
number of transactions actually processed: 500/500
tps = 124.409521(including connections establishing)
tps = 127.591055(excluding connections establishing)

Windows:
wal_sync_method = open_sync
fsync = false

transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 500
number of transactions actually processed: 500/500
tps = 112.103652(including connections establishing)
tps = 113.250950(excluding connections establishing)

wal_sync_method = fsync
fsync = true

scaling factor: 1
number of clients: 1
number of transactions per client: 500
number of transactions actually processed: 500/500
tps = 21.191655(including connections establishing)
tps = 21.232517(excluding connections establishing)