Re: Insert Performance with WAL and Fsync

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

Are you seeing anything due to swapping, &c?  What do memstat and
friends tell you?

> 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

We're using Solaris 7 and seeing considerably better performance than
that (mind you' we've got some honkin' big hardware underneath, and a
big RAID array with internal battery-backed smart caches, so I might
not be the right person to ask).

I note that you have IDE disks on your Win machine.  Are you sure
you're really getting an fsync?  Win2000, AFAIR, is pretty aggressive
about write-caching.  I may be wrong though -- I'm no Windows guy.

One thing I noticed is that the WAL commit_delay and siblings
settings were a big help.  My theory was WAL was costing us too much,
because we had such volume that WAL became a bottleneck -- it was
firing too quickly.  My answer was to increase those settings; I
noticed an immediate improvement.  I had to increase the segments as
well, in order to keep up; this takes slightly longer to recover in
case of a crash, of course, but not so long as to make the difference
worth worrying about.

> 1 36Gig 10000RPM SCSI Disk

I'd also worry about this 1-disk set-up.  I'd be inclined to double
the disk in order to put the WAL file on another spindle (or use RAID
to speed things up, but that's a lot more disk).

> Postgres 7.1.2 compiled using gcc

There are a couple of issues that make it worth the upgrade to 7.1.3.
See the archives.  Nothing to help your perf. though, if I recall.

If I can think of anything else, I'll let you know.

A
--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110