Re: Statement-level rollback

On 2018-Dec-07, Andres Freund wrote:

> On December 7, 2018 11:56:55 AM PST, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> >BTW, a couple of months ago I measured the performance implications for
> >a single update under pgbench and it represented a decrease of about
> >3%-5%.  Side-effects such as xid consumption have worse implications,
> >but as far as performance is concerned, it's not as bad as all that.
> 
> I don't think that's a fair test for the performance downsides. For
> pgbench with modifications the full commit is such a large portion of
> the time that you'd need to make things a lot slower to show a large
> slowdown. And for ro pgbench the subxacts don't matter that much. It'd
> probably be more meaningful to have a mixed workload of 15 ro
> statements per xact in one type of session, and 5rw /10ro in another.

I just reviewed the old emails where I reported some performance
figures.  Turns out I tried pgbench with varying number of inserts per
transaction:

 nr_inserts │ transaction │ statement │        decrease
────────────┼─────────────┼───────────┼────────────────────────
         10 │    20674.12 │  19983.33 │ 3.34132722456868780900
         63 │     3704.95 │   3613.39 │ 2.47128841144954722700
         64 │     3646.99 │   3553.79 │ 2.55553209633149528800
         65 │     3582.17 │   3503.47 │ 2.19699232588068126300
         66 │     3513.27 │   3423.34 │ 2.55972356237920797400
         80 │     2906.50 │   2833.80 │ 2.50129021159470153100
        100 │     2337.58 │   2272.01 │ 2.80503768854969669500
        200 │     1171.06 │   1143.22 │ 2.37733335610472563300
        500 │      464.55 │    451.04 │ 2.90819072220428371500
       1000 │      229.79 │    223.66 │ 2.66765307454632490500
      10000 │       22.02 │     21.43 │ 2.67938237965485921900

(The numbers around 64 were tested to make sure we weren't particularly
in trouble when we hit PGPROC subxid cache size.)

In a different test, I used HOT updates, and I said "This works out to
be between 4.3% and 5.6% in the four cases, which is not growing with
the number of ops per transaction."

 nr_updates │   transaction    │    statement
────────────┼──────────────────┼─────────────────
         10 │ 6714.67 +-114.37 │ 6362.89 +-99.96
        100 │ 699.19 +-7.20    │ 671.64 +-7.75
        200 │ 359.92 +-0.86    │ 342.07 +-8.34
        500 │ 143.11 +-2.47    │ 135.14 +-3.21
       1000 │ 70.07 +-2.61     │ 67.04 +-1.52

I didn't measure read-only statements.  I definitely expect that case to
have a larger performance decrease; however, that's the kind of code
site that, if it becomes a hot path, you'd change from the slow mode to
the fast mode.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services