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(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110