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

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@sss.pgh.pa.us]
> Sent: Thursday, January 10, 2002 1:42 PM
> To: Mike Schroepfer
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Insert Performance with WAL and Fsync
>
>
> Mike Schroepfer <mike@raplix.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
>