Re: postgres_fdw: commit remote (sub)transactions in parallel during pre-commit - Mailing list pgsql-hackers

Applied patches v6-0002 and v6-0003 to master branch, and the `make check` test is ok.

Here is my test result in 10 times average on 3 virtual machines:
before the patches:

abort.1 = 2.5473 ms

abort.2 = 4.1572 ms

after the patches with OPTIONS (ADD parallel_abort 'true'):

abort.1 = 1.7136 ms

abort.2 = 2.5833 ms

Overall, it has about 32 ~ 37 % improvement in my testing environment.

table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}

On Mon, Feb 28, 2022 at 6:53 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:

Here is an updated version.  I added to the 0003 patch a macro for
defining the milliseconds to wait, as proposed by David upthread.

I modified the 0003 patch further: 1) I added to
pgfdw_cancel_query_end/pgfdw_exec_cleanup_query_end the PQconsumeInput
optimization that we have in do_sql_command_end, and 2) I
added/tweaked comments a bit further.  Attached is an updated version.

Like [1], I ran a simple performance test using the following transaction:

BEGIN;
SAVEPOINT s;
INSERT INTO ft1 VALUES (10, 10);
INSERT INTO ft2 VALUES (20, 20);
ROLLBACK TO SAVEPOINT s;
RELEASE SAVEPOINT s;
INSERT INTO ft1 VALUES (10, 10);
INSERT INTO ft2 VALUES (20, 20);
ABORT;

where ft1 is a foreign table created on a foreign server hosted on the
same machine as the local server, and ft2 is a foreign table created
on a foreign server hosted on a different machine.  (In this test I
used two machines, while in [1] I used three machines: one for the
local server and the others for ft1 and ft2.)  The average latencies
for the ROLLBACK TO SAVEPOINT and ABORT commands over ten runs of the
above transaction with the parallel_abort option disabled/enabled are:

* ROLLBACK TO SAVEPOINT  parallel_abort=0: 0.3217 ms  parallel_abort=1: 0.2396 ms

* ABORT  parallel_abort=0: 0.4749 ms  parallel_abort=1: 0.3733 ms

This option reduces the latency for ROLLBACK TO SAVEPOINT by 25.5
percent, and the latency for ABORT by 21.4 percent.  From the results,
I think the patch is useful.

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/CAPmGK17dAZCXvwnfpr1eTfknTGdt%3DhYTV9405Gt5SqPOX8K84w%40mail.gmail.com