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

What about it? Application normally uses exclusive row locks only when
it's really required by application logic.

Exclusive PK locks are not required for FK inserts by RI logic,
we just have no other means to ensure PK existence currently.

Keeping in mind that RI is used near in every application I would
like to see this fixed. And ppl already complained about it.

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

I have only one explanation: it reduces number of transactions ready
to commit (because of the same FK writers will wait till first one
committed - ie log fsynced) and WAL commit performance greatly depends
on how many commits were done by single log fsync.
7.0.3+nofsync commit performance doesn't depend on this factor.

Vadim

Fri, 8 Dec 2000 17:12:26 -0800 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: 7.0.3(nofsync) vs 7.1 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> I have only one explanation: it reduces number of transactions ready
> to commit (because of the same FK writers will wait till first one
> committed - ie log fsynced) and WAL commit performance greatly depends
> on how many commits were done by single log fsync.
> 7.0.3+nofsync commit performance doesn't depend on this factor.

Sure, but that's not exactly a fair comparison is it?  7.0.3+nofsync
should be compared against 7.1+nofsync.  I put the pg_fsync routine
back in a little while ago, so nofsync should work again.

It occurs to me though that disabling fsync should probably also reduce
the WAL commit delay to zero, no?  What is the default commit delay now?

			regards, tom lane

Fri, 08 Dec 2000 20:32:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.0.3(nofsync) vs 7.1 
Mikheev, Vadim wrote:
> > > 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?
>
> What about it? Application normally uses exclusive row locks only when
> it's really required by application logic.
>
> Exclusive PK locks are not required for FK inserts by RI logic,
> we just have no other means to ensure PK existence currently.
>
> Keeping in mind that RI is used near in every application I would
> like to see this fixed. And ppl already complained about it.

    I  still don't see how dirty reads can solve the RI problems.
    If Xact A deletes a PK while Xact B inserts  an  FK,  one  of
    them  will  either  see the new reference or the PK gone. But
    from a transactional POV it depends on if the  opposite  Xact
    finally commits or not to tell if that really happened.

    With dirty read, you only get "maybe my PK is gone" or "maybe
    there is a reference".


Jan

>
> > 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...
>
> I have only one explanation: it reduces number of transactions ready
> to commit (because of the same FK writers will wait till first one
> committed - ie log fsynced) and WAL commit performance greatly depends
> on how many commits were done by single log fsync.
> 7.0.3+nofsync commit performance doesn't depend on this factor.
>
> Vadim
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Wed, 13 Dec 2000 08:39:57 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: 7.0.3(nofsync) vs 7.1