Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
> On 10.10.2011 21:25, Greg Sabino Mullane wrote:
>> I agree it is better versus SELECT FOR, but what about repeatable
>> read versus the new serializable? How much overhead is there in
>> the 'monitoring of read/write dependencies'? This is my only
>> concern at the moment. Are we talking insignificant overhead?
>> Minor? Is it measurable? Hard to say without knowing the number
>> of txns, number of locks, etc.?
>
> I'm sure it does depend heavily on all of those things, but IIRC
> Kevin ran some tests earlier in the spring and saw a 5% slowdown.
> That feels like reasonable initial guess to me. If you can run
> some tests and measure the overhead in your application, it would
> be nice to hear about it.
Right: the only real answer is "it depends". At various times I ran
different benchmarks where the overhead ranged from "lost in the
noise" to about 5% for one variety of "worst case". Dan ran DBT-2,
following the instructions on how to measure performance quite
rigorously, and came up with a 2% hit versus repeatable read for
that workload. I rarely found a benchmark where the hit exceeded
2%, but I have a report of a workload where they hit was 20% -- for
constantly overlapping long-running transactions contending for the
same table.
I do have some concern about whether the performance improvements
from reduced LW locking contention elsewhere in the code may (in
whack-a-mole fashion) cause the percentages to go higher in SSI.
The biggest performance issues in some of the SSI benchmarks were on
LW lock contention, so those may become more noticeable as other
contention is reduced. I've been trying to follow along on the
threads regarding Robert's work in that area, with hopes of applying
some of the same techniques to SSI, but it's not clear whether I'll
have time to work on that for the 9.2 release. (It's actually
looking improbably at this point.)
If you give it a try, please optimize using the performance
considerations for SSI in the manual. They can make a big
difference.
-Kevin