Re: Speedup twophase transactions

> On 10 Jan 2016, at 12:15, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> So we've only optimized half the usage? We're still going to cause replication delays.

Yes, replica will go through old procedures of moving data to and from file.

> We can either
>
> 1) Skip fsyncing the RecreateTwoPhaseFile and then fsync during restartpoints

From what i’ve seen with old 2pc code main performance bottleneck was caused by frequent creating of files. So better to avoid files if possible.

>
> 2) Copy the contents to shmem and then write them at restartpoint as we do for checkpoint
> (preferred)

Problem with shared memory is that we can’t really predict size of state data, and anyway it isn’t faster then reading data from WAL
(I have tested that while preparing original patch).

We can just apply the same logic on replica that on master: do not do anything special on prepare, and just read that data from WAL.
If checkpoint occurs during recovery/replay probably existing code will handle moving data to files.

I will update patch to address this issue.

> I think padding will negate the effects of the additional bool.
>
> If we want to reduce the size of the array GIDSIZE is currently 200, but XA says maximum 128 bytes.
>
> Anybody know why that is set to 200?

Good catch about GID size.

If we talk about further optimisations i see two ways:

1) Optimising access to GXACT. Here we can try to shrink it; introduce more granular locks,
e.g. move GIDs out of GXACT and lock GIDs array only once while checking new GID uniqueness; try to lock only part of GXACT by hash; etc.

2) Be optimistic about consequent COMMIT PREPARED. In normal workload next command after PREPARE will be COMMIT/ROLLBACK, so we can save
transaction context and release it only if next command isn’t our designated COMMIT/ROLLBACK. But that is a big amount of work and requires
changes to whole transaction pipeline in postgres.

Anyway I suggest that we should consider that as a separate task.

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