Re: Overhead cost of Serializable Snapshot Isolation

On Tue, Oct 11, 2011 at 12:46 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> I ran my good old pgbench -S, scale factor 100, shared_buffers =
>> 8GB test on Nate Boley's box.  I ran it on both 9.1 and 9.2dev,
>> and at all three isolation levels.  As usual, I took the median of
>> three 5-minute runs, which I've generally found adequate to
>> eliminate the noise. On both 9.1 and 9.2dev, read committed and
>> repeatable read have basically identical performance; if anything,
>> repeatable read may be slightly better - which would make sense,
>> if it cuts down the number of snapshots taken.
>
> Right.  Thanks for running this.  Could you give enough details to
> allow reproducing on this end (or point to a previous post with the
> details)?

Sure, it's pretty much just a vanilla pgbench -S run, but the scripts
I used are attached here. I build the head of each branch using the
"test-build" script and then used the "runtestiso" script to drive the
test runs. These scripts are throwaway so they're not really
documented, but hopefully it's clear enough what it's doing. The
server itself is a 32-core AMD 6128.

>> Data are attached, in text form and as a plot.  I excluded the
>> repeatable read results from the plot as they just clutter it up -
>> they're basically on top of the read committed results.
>
> That was kind, but really the REPEATABLE READ results are probably
> the more meaningful comparison, even if they are more embarrassing.
> :-(

They're neither more nor less embarrassing - they're pretty much not
different at all. I just didn't see any point in making a graph with
6 lines on it when you could only actually see 4 of them.

> Did these transactions write anything?  If not, were they declared
> to be READ ONLY?  If they were, in fact, only reading, it would be
> interesting to see what the performance looks like if the
> recommendation to use the READ ONLY attribute is followed.

pgbench -S doesn't do any writes, or issue any transaction control
statements. It just fires off SELECT statements against a single
table as fast as it can, retrieving values from rows chosen at random.
Each SELECT implicitly begins and ends a transaction. Possibly the
system could gaze upon the SELECT statement and infer that the
one-statement transaction induced thereby can't possibly write any
tuples, and mark it read-only automatically, but I'm actually not that
excited about that approach - trying to fix the lwlock contention
that's causing the headache in the first place seems like a better use
of time, assuming it's possible to make some headway there.

My general observation is that, on this machine, a lightweight lock
that is taken in exclusive mode by a series of lockers in quick
succession seems to max out around 16-20 clients, and the curve starts
to bend well before that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company