Insert Performance with WAL and Fsync - Mailing list pgsql-general

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)