Re: Insert Performance with WAL and Fsync

Andrew,

Thanks for the reply - responses below:

> > 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?

No swapping problems at all - lots of CPU and mem to go around (vmstat 5):

procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s6 -- in sy cs us sy
id
0 0 0 2140608 1820520 2 15 1 1 1 0 0 2 0 0 0 430 130 71 1 1
99
0 0 0 2091848 1674728 21 65 0 0 0 0 0 67 0 0 0 806 2151 633 18 15
67
0 0 0 2091848 1673928 21 52 0 0 0 0 0 65 0 0 0 806 2081 611 20 15
64
0 0 0 2092472 1674008 12 19 0 8 8 0 0 39 0 0 0 634 1073 333 10 6
84

> 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).

What kind of numbers are you getting - so I can get an idea
of where I am relative.

> 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.

Can you share your config? I've played with it without much help - I
probably
am missing something.

> > 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).

Yeah - this is not a production system - I'm trying to do some testing
to understand our app's characteristics and hardware requirements. I
certainly
would at least mirror for safety and would love to get the wal on a another
spindle.

> > 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.

Hmm - looked through the logs a good while ago and didn't see anything that
I was too
concerned about. I'll double check tho - thanks!

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

Appreciate your help!

Mike

>
> A
> --
> ----
> Andrew Sullivan 87 Mowat Avenue
> Liberty RMS Toronto, Ontario Canada
> <andrew(at)libertyrms(dot)info> M6K 3E3
> +1 416 646 3304 x110
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>