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

SSI non-serializalbe UPDATE performance (was: getting to beta)

From: Dan Ports <drkp(at)csail(dot)mit(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: SSI non-serializalbe UPDATE performance (was: getting to beta)
Date: 2011-04-22 22:07:34
Message-ID: 20110422220734.GG57793@csail.mit.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
For background, the issue here is that there are three SSI calls that
get invoked even on non-serializable transactions:
  - PredicateLockPageSplit/Combine, during B-tree page splits/combines
  - PredicateLockTupleRowVersionLink, from heap_update

These have to update any matching SIREAD locks to match the new lock
target. If there aren't any serializable transactions, there won't be
any, but it still has to check and this requires taking a LWLock. Every
other SSI function checks XactIsoLevel and bails out immediately if
non-serializable.

Like Kevin said, I tested this by removing these three calls and
comparing under what I see as worst-case conditions. I used pgbench, an
update-mostly workload, in read committed mode. The database (scale
factor 100) fit in shared_buffers and was backed by tmpfs so disk
accesses didn't enter the picture anywhere. I ran it on a 16-core
machine to stress lock contention.

Even under these conditions I couldn't reliably see a slowdown. My
latest batch of results (16 backends, median of three 10 minute runs)
shows a difference well below 1%. In a couple of cases I saw the code
with the SSI checks running faster than with them removed, so this
difference seems in the noise.

Given that result, and considering it's a pretty extreme condition, it
probably isn't worth worrying about this too much, but...

There's a quick fix: we might as well bail out of these functions early
if there are no serializable transactions running. Kevin points out we
can do this by checking if PredXact->SxactGlobalXmin is invalid. I
would add that we can do this safely without taking any locks, even on
weak-memory-ordering machines. Even if a new serializable transaction
starts concurrently, we have the appropriate buffer page locked, so
it's not able to take any relevant SIREAD locks.

Dan

-- 
Dan R. K. Ports              MIT CSAIL                http://drkp.net/

In response to

pgsql-hackers by date

Next:From: Jeff DavisDate: 2011-04-22 23:13:48
Subject: Re: Patch for pg_upgrade to turn off autovacuum
Previous:From: Kevin GrittnerDate: 2011-04-22 22:00:00
Subject: Re: "stored procedures"

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