Re: SSI patch version 8

On 01/10/2011 06:03 PM, Kevin Grittner wrote:
> Due to popular request (Hey, David's popular, right?), I'm posting a
> patch for Serializable Snapshot Isolation (SSI), although I don't
> yet have everything in it that I was planning on submitting before
> the CF. I will probably be submitting another version before the
> deadline with the following, but there should be plenty here for
> people to test and benchmark. We're done with the major refactoring
> needed to address concerns raised in earlier reviews, and I don't
> expect the remaining work to destabilize what's there or to have a
> significant impact on performance.
A speed test showing a significant drop in performance when using SSI:

hot2=> create table test_t2 as (select generate_series(0, 1000000));
hot2=> \timing
begin transaction isolation level repeatable read;
Time: 0.185 ms
hot2=> select count(*) from test_t2;
Time: 134.521 ms
hot2=> select count(*) from test_t2;
Time: 142.132 ms
hot2=> select count(*) from test_t2;
Time: 147.469 ms
hot2=> commit;
hot2=> begin transaction isolation level serializable;
Time: 0.165 ms
hot2=> select count(*) from test_t2;
Time: 349.219 ms
hot2=> select count(*) from test_t2;
Time: 354.010 ms
hot2=> select count(*) from test_t2;
Time: 355.960 ms
hot2=> commit;

So, count(*) queries are more than twice as slow compared to the old
serializable transaction isolation level. The relative speed difference
stays the same if testing with more rows. Also, the same speed
difference is there if testing with more columns:

create table test_t4 as (select g g1, g g2, g g3, g g4, g g5, g g6 from
(select generate_series as g from generate_series(0, 1000000)) as t1);

repeatable read times:
140.113 ms 134.628 ms 140.113 ms 156.166 ms

serializable:
353.257 ms 366.558 ms 373.914 ms 359.682 ms

- Anssi