Re: Transactions involving multiple postgres foreign servers, take 2

On Tue, Jul 13, 2021 at 1:14 PM r(dot)takahashi_2(at)fujitsu(dot)com
<r(dot)takahashi_2(at)fujitsu(dot)com> wrote:
>
> Hi Sawada-san,
>
>
> Thank you for your reply.
>
> > Not sure but it might be possible to keep holding an xlogreader for
> > reading PREPARE WAL records even after the transaction commit. But I
> > wonder how much open() for wal segment file accounts for the total
> > execution time of 2PC. 2PC requires 2 network round trips for each
> > participant. For example, if it took 500ms in total, we would not get
> > benefits much from the point of view of 2PC performance even if we
> > improved it from 14ms to 1ms.
>
> I made the patch based on your advice and re-run the test on the new machine.
> (The attached patch is just for test purpose.)

Thank you for testing!

>
>
> * foreign_twophase_commit = disabled
> 2686tps
>
> * foreign_twophase_commit = required (It is necessary to set -R ${RATE} as Ikeda-san said)
> 311tps
>
> * foreign_twophase_commit = required with attached patch (It is not necessary to set -R ${RATE})
> 2057tps

Nice improvement!

BTW did you test on the local? That is, the foreign servers are
located on the same machine?

>
>
> This indicate that if we can reduce the number of times to open() wal segment file during "COMMIT PREPARED", the performance can be improved.
>
> This patch can skip closing wal segment file, but I don't know when we should close.
> One idea is to close when the wal segment file is recycled, but it seems difficult for backend process to do so.

I guess it would be better to start a new thread for this improvement.
This idea helps not only 2PC case but also improves the
COMMIT/ROLLBACK PREPARED performance itself. Rather than thinking it
tied with this patch, I think it's good if we can discuss this patch
separately and it gets committed alone.

> BTW, in previous discussion, "Send COMMIT PREPARED remote servers in bulk" is proposed.
> I imagined the new SQL interface like "COMMIT PREPARED 'prep_1', 'prep_2', ... 'prep_n'".
> If we can open wal segment file during bulk COMMIT PREPARED, we can not only reduce the times of communication, but also reduce the times of open() wal segment file.

What if we successfully committed 'prep_1' but an error happened
during committing another one for some reason (i.g., corrupted 2PC
state file, OOM etc)? We might return an error to the client but have
already committed 'prep_1'.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/