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

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@libertyrms.info>                              M6K 3E3
>                                          +1 416 646 3304 x110
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>