Re: Insert Performance with WAL and Fsync

Tom,

Thanks for the prompt reply. I have some more info for you:

Results:

a.1) 69tps 7.1.2 (same build as before) on a Uniprocessor Ultra 10 Box with
fysnc
a.2) 52tps 7.1.2 (same build as before) on a Uniprocessor Ultra 10 Box
without fysnc
b.1) 32tps 7.1.2 on the dual processor box again
b.2) 31tps 7.2 tip of cvs on the dual processor box
b.3) Output of vmstat 5 during b.1

So 7.2 doesn't appear to help. For fun I also disabled one of the
processors
and re-ran the tests. Overall the numbers went down not up. So
I do not think it is the locking/SMP problem.

Any other thoughts? I'm happy to run tests/collect more info as it helps.

The other curious thing is that my little Ultra 10 is also beating the
220R. Can anyone else chime in with comparative numbers so I know
how good/bad these are ?

Detailed results below.

Thanks!

Mike

a.1)
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 = 69.109284(including connections establishing)
tps = 69.580949(excluding connections establishing)

a.2)
scaling factor: 1
number of clients: 1
number of transactions per client: 500
number of transactions actually processed: 500/500
tps = 52.001209(including connections establishing)
tps = 52.283831(excluding connections establishing)

b.1)
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 = 32.517491(including connections establishing)
tps = 32.635141(excluding connections establishing)

b.2)
scaling factor: 1
number of clients: 1
number of transactions per client: 500
number of transactions actually processed: 500/500
tps = 31.648947(including connections establishing)
tps = 31.723237(excluding connections establishing)

b.3) output of vmstat 5 during b.1:

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 2140760 1820952 2 15 1 1 1 0 0 2 0 0 0 430 130 71 1 1
99
0 0 0 2093696 1690392 15 128 0 91 91 0 0 128 0 0 0 1232 1886 894 11 3
86
0 0 0 2093440 1690048 5 0 0 0 0 0 0 168 0 0 0 1475 2213 1181 17 4
79
0 0 0 2093440 1689824 3 0 0 1 1 0 0 167 0 0 0 1462 2228 1169 20 3
77
0 0 0 2094472 1690568 27 42 0 12 12 0 0 36 0 0 0 630 646 286 4 1
94

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, January 10, 2002 1:42 PM
> To: Mike Schroepfer
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Insert Performance with WAL and Fsync
>
>
> Mike Schroepfer <mike(at)raplix(dot)com> writes:
> > It appears the CPU utilization on both machines is very low
> (<15%)- so I'm
> > guessing it is mostly I/O overhead.
>
> It looks like your Solaris box is a dual CPU machine? PG
> 7.1.* suffers
> from pretty awful performance on multiprocessors, due to a rather
> braindead implementation of spinlocks. If vmstat shows that
> neither the
> CPUs nor the disks are working real hard, then I'd suspect this to be
> the problem --- cf
> http://archives.postgresql.org/pgsql-hackers/2002-01/msg00449.php
> and other recent pghackers threads.
>
> You might care to try your tests on current development sources (*not*
> 7.2b4; pull from CVS or use a nightly-snapshot tarball). I
> think we've
> improved the SMP performance considerably since 7.1, though more could
> probably be done in future. BTW, don't put a production database on
> current sources, there's at least two unpleasant known bugs.
>
> > 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)
>
> I don't know the innards of cygwin, but it would not surprise
> me in the
> least to hear that it doesn't implement fsync & OPEN_SYNC efficiently
> and/or correctly. It has to sit atop Windows, which probably doesn't
> have compatible APIs to support these behaviors reasonably.
> The results
> you show sure make it look like OPEN_SYNC is a no-op on cygwin...
>
> regards, tom lane
>