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(at)Yahoo(dot)com #