I've run tests (with 50 .. 250 simult users) for some PG project
of my company. 7.1 was 3 times faster than 7.0.3 (fsync) but near
3 times slower than 7.0.3 (nofsync). It was not the best day in
my life - WAL looked like big bottleneck -:(

But finally I've realized that this test makes ~3 FK insertions
... and FK insert means SELECT FOR UPDATE ... and this could
reduce # of commits per fsync.

So, I've run simple test (below) to check this. Seems that 7.1
is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI
triggers is quite bad for performance.

Please take this into account when comparing 7.1 with 7.0.3.
Also, we should add new TODO item: implement dirty reads
and use them in RI triggers.

Vadim

==================================================================

Tables:
PK (p int primary key), p in 1 .. 1000.
FK (f int, foreign key(f) references pk(p)).

First column below - # of users; second - # of FK inserts
per user; next - what values were used in each insert:
either unique (ie there was no users inserting same value -
no waiters on SELECT FOR UPDATE on PK) or some random value
from range.

7.1

 50 1000 unique:        250 tps
100 1000 unique:        243 tps
 50 1000 rand(1 .. 10): 178 tps
 50 1000 rand(1 ..  5): 108 tps

7.0.3 (nofsync)

 50 1000 unique:        157 tps
 50 1000 rand(1 .. 10): 154 tps
 50 1000 rand(1 ..  5): 154 tps


Fri, 8 Dec 2000 15:02:53 -0800 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
7.0.3(nofsync) vs 7.1
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> So, I've run simple test (below) to check this. Seems that 7.1
> is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI
> triggers is quite bad for performance.
> Also, we should add new TODO item: implement dirty reads
> and use them in RI triggers.

That would fix RI triggers, I guess, but what about plain SELECT FOR
UPDATE being used by applications?

Why exactly is SELECT FOR UPDATE such a performance problem for 7.1,
anyway?  I wouldn't have thought it'd be a big deal...

			regards, tom lane

Fri, 08 Dec 2000 19:14:38 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.0.3(nofsync) vs 7.1 
> I've run tests (with 50 .. 250 simult users) for some PG project
> of my company. 7.1 was 3 times faster than 7.0.3 (fsync) but near
> 3 times slower than 7.0.3 (nofsync). It was not the best day in
> my life - WAL looked like big bottleneck -:(
> 
> But finally I've realized that this test makes ~3 FK insertions
> ... and FK insert means SELECT FOR UPDATE ... and this could
> reduce # of commits per fsync.
> 
> So, I've run simple test (below) to check this. Seems that 7.1
> is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI
> triggers is quite bad for performance.
> 
> Please take this into account when comparing 7.1 with 7.0.3.
> Also, we should add new TODO item: implement dirty reads
> and use them in RI triggers.
> 
> Vadim

I did some testings using contrib/pgbench (100k tuples, 32 concurrent
users) with 7.1 and 7.0.3.  It seems 7.1 is 5 times faster than 7.0.3
with fsync, but 1.5 times slower than 7.0.3 without fsync.

So I modified access/transam/xlog.c to disable fsync() call at
all. Now I get nearly equal performance as 7.0.3 without fsync. It
seems the bottle neck is logging with fsync(). It might be interesting
moving data/pg_xlog to a separate disk drive and see how it performs
better.

BTW pgbench does PK insertions and updates, but does no FK things.
--
Tatsuo Ishii

Sat, 09 Dec 2000 16:27:11 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: 7.0.3(nofsync) vs 7.1
At 16:27 9/12/00 +0900, Tatsuo Ishii wrote:
>It might be interesting
>moving data/pg_xlog to a separate disk drive and see how it performs
>better.

Good idea. One of the fundamental rules with Dec/RDB is to put the
XLOG-equivant on a different drive from *any* database-related file.
Another setting you might consider is 'fsync-every-n-commits'.

The different drive provides enhanced recoverability as well as enhanced
performance.





----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sat, 09 Dec 2000 18:53:18 +1100
Philip Warner <pjw@rhyme.com.au>
Re: 7.0.3(nofsync) vs 7.1
[ Charset ISO-8859-1 unsupported, converting... ]
> I've run tests (with 50 .. 250 simult users) for some PG project
> of my company. 7.1 was 3 times faster than 7.0.3 (fsync) but near
> 3 times slower than 7.0.3 (nofsync). It was not the best day in
> my life - WAL looked like big bottleneck -:(
> 
> But finally I've realized that this test makes ~3 FK insertions
> ... and FK insert means SELECT FOR UPDATE ... and this could
> reduce # of commits per fsync.
> 
> So, I've run simple test (below) to check this. Seems that 7.1
> is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI
> triggers is quite bad for performance.
> 
> Please take this into account when comparing 7.1 with 7.0.3.
> Also, we should add new TODO item: implement dirty reads
> and use them in RI triggers.

Added:

* Implement dirty reads and use them in RI triggers

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Dec 2000 20:44:24 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 7.0.3(nofsync) vs 7.1