Re: Speedup twophase transactions

> On 27 Dec 2016, at 07:31, Michael Paquier <michael.paquier@gmail.com> wrote:
> 
> I think that it would be a good idea to actually test that in pure
> recovery time, aka no client, and just use a base backup and make it
> recover X prepared transactions that have created Y checkpoints after
> dropping cache (or restarting server).

I did tests with following setup:

* Start postgres initialised with pgbench
* Start pg_receivexlog
* Take basebackup
* Perform 1.5 M transactions
* Stop everything and apply wal files stored by pg_receivexlog to base backup.

All tests performed on a laptop with nvme ssd
number of transactions: 1.5M
start segment: 0x4

-master non-2pc:
    last segment: 0x1b
    average recovery time per 16 wal files: 11.8s
    average total recovery time: 17.0s

-master 2pc:
    last segment: 0x44
    average recovery time per 16 wal files: 142s
    average total recovery time: 568s

-patched 2pc (previous patch):
    last segment: 0x44
    average recovery time per 16 wal files: 5.3s
    average total recovery time: 21.2s

-patched2 2pc (dlist_push_tail changed to dlist_push_head):
    last segment: 0x44
    average recovery time per 16 wal files: 5.2s
    average total recovery time: 20.8s

So skipping unnecessary fsyncs gave us x25 speed increase even on ssd (on hdd difference should be bigger).
Pushing to list's head didn’t yield measurable results, but anyway seems to be conceptually better.

PS:
I’ve faced situation when pg_basebackup freezes until checkpoint happens (automatic or user-issued).
Is that expected behaviour?

--
Stas Kelvich
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company