Re: Statement-level rollback

On 2018-Dec-07, Andres Freund wrote:

> On December 7, 2018 11:56:55 AM PST, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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