Skip site navigation (1) Skip section navigation (2)

Re: SSI non-serializable UPDATE performance

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Simon Riggs" <simon(at)2ndQuadrant(dot)com>, "Dan Ports" <drkp(at)csail(dot)mit(dot)edu>
Cc: "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: SSI non-serializable UPDATE performance
Date: 2011-04-27 18:15:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> Reading the code, IIUC, we check for RW conflicts after each write
> but only if the writer is running a serializable transaction.
Correct as far as that statement goes.  There are cases where
predicate lock maintenance is needed when dealing with locked
resources; see below.
> Am I correct in thinking that there is zero impact of SSI if
> nobody is running a serializable transaction?
Benchmarks until a recent change actually were coming out slightly
faster with the SSI patch.  Since there's not other reason that
should have been true, that pretty much had to be the random
alignment of code on CPU cache boundaries.  A recent change canceled
that improvement; until we reverted SSI entirely for a comparative
benchmark point we briefly thought SSI caused an overall fraction of
a percent regression.
We've considered changing the "call SSI method which does a quick
return if not applicable" technique to "try to call SSI with a macro
which skips the call if not applicable", but in the absence of any
evidence of a performance hit with the simple, straightforward
approach we have held off on that optimization attempt.
There are cases where some minimal work is done in SSI for
non-serializable transactions:
(1) If a tuple which is predicate locked, or sits on a predicate-
locked page, is updated, the predicate lock is duplicated for the
new tuple.  We have found patterns of updates involving four or more
transactions where a non-serializable transaction can hide
serialization anomalies among serializable transactions if we don't
do this.  Someone suggested that we could take out this call and
just document that serializable transactions may not comply with the
standard-defined behavior when there are concurrent non-serializable
transactions.  We were unable to show a measurable performance hit
on this, although this was just with 32 clients hitting a 16
processor machine.  There was at least a theoretical possibility
that with higher levels of concurrency there could have been a new
contention point for a LW lock here which could affect performance. 
We added a quick return which didn't need to check any locks at the
front of this routine which is taken if there are no active
serializable transactions on the cluster at the moment of update.
(2) Page splits and page combines, even if they are from
non-serializable transactions (like autovacuum) must take action if
there are predicate locks on the pages involved.  Again, there is a
fast exit if no serializable transactions are active, and even
before that check was added there was not a measurable impact on
If any of that isn't clear or leaves some concern, please let me

In response to


pgsql-hackers by date

Next:From: Peter EisentrautDate: 2011-04-27 18:21:21
Subject: Re: timeline garbage in pg_basebackup (was gcc 4.6 warnings -Wunused-but-set-variable)
Previous:From: Dan PortsDate: 2011-04-27 17:59:14
Subject: Re: SSI non-serializable UPDATE performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group