Re: Transactions involving multiple postgres foreign servers, take 2

On Thu, Jun 24, 2021 at 9:46 PM k(dot)jamison(at)fujitsu(dot)com
<k(dot)jamison(at)fujitsu(dot)com> wrote:
>
> Hi Sawada-san,
>
> I also tried to play a bit with the latest patches similar to Ikeda-san,
> and with foreign 2PC parameter enabled/required.

Thank you for testing the patch!

>
> > > >> b. about performance bottleneck (just share my simple benchmark
> > > >> results)
> > > >>
> > > >> The resolver process can be performance bottleneck easily although
> > > >> I think some users want this feature even if the performance is not so
> > good.
> > > >>
> > > >> I tested with very simple workload in my laptop.
> > > >>
> > > >> The test condition is
> > > >> * two remote foreign partitions and one transaction inserts an
> > > >> entry in each partitions.
> > > >> * local connection only. If NW latency became higher, the
> > > >> performance became worse.
> > > >> * pgbench with 8 clients.
> > > >>
> > > >> The test results is the following. The performance of 2PC is only
> > > >> 10% performance of the one of without 2PC.
> > > >>
> > > >> * with foreign_twophase_commit = requried
> > > >> -> If load with more than 10TPS, the number of unresolved foreign
> > > >> -> transactions
> > > >> is increasing and stop with the warning "Increase
> > > >> max_prepared_foreign_transactions".
> > > >
> > > > What was the value of max_prepared_foreign_transactions?
> > >
> > > Now, I tested with 200.
> > >
> > > If each resolution is finished very soon, I thought it's enough
> > > because 8clients x 2partitions = 16, though... But, it's difficult how
> > > to know the stable values.
> >
> > During resolving one distributed transaction, the resolver needs both one
> > round trip and fsync-ing WAL record for each foreign transaction.
> > Since the client doesn’t wait for the distributed transaction to be resolved,
> > the resolver process can be easily bottle-neck given there are 8 clients.
> >
> > If foreign transaction resolution was resolved synchronously, 16 would
> > suffice.
>
>
> I tested the V36 patches on my 16-core machines.
> I setup two foreign servers (F1, F2) .
> F1 has addressbook table.
> F2 has pgbench tables (scale factor = 1).
> There is also 1 coordinator (coor) server where I created user mapping to access the foreign servers.
> I executed the benchmark measurement on coordinator.
> My custom scripts are setup in a way that queries from coordinator
> would have to access the two foreign servers.
>
> Coordinator:
> max_prepared_foreign_transactions = 200
> max_foreign_transaction_resolvers = 1
> foreign_twophase_commit = required
>
> Other external servers 1 & 2 (F1 & F2):
> max_prepared_transactions = 100
>
>
> [select.sql]
> \set int random(1, 100000)
> BEGIN;
> SELECT ad.name, ad.age, ac.abalance
> FROM addressbook ad, pgbench_accounts ac
> WHERE ad.id = :int AND ad.id = ac.aid;
> COMMIT;
>
> I then executed:
> pgbench -r -c 2 -j 2 -T 60 -f select.sql coor
>
> While there were no problems with 1-2 clients, I started having problems
> when running the benchmark with more than 3 clients.
>
> pgbench -r -c 4 -j 4 -T 60 -f select.sql coor
>
> I got the following error on coordinator:
>
> [95396] ERROR: could not prepare transaction on server F2 with ID fx_151455979_1216200_16422
> [95396] STATEMENT: COMMIT;
> WARNING: there is no transaction in progress
> pgbench: error: client 1 script 0 aborted in command 3 query 0: ERROR: could not prepare transaction on server F2 with ID fx_151455979_1216200_16422
>
> Here's the log on foreign server 2 <F2> matching the above error:
> <F2> LOG: statement: PREPARE TRANSACTION 'fx_151455979_1216200_16422'
> <F2> ERROR: maximum number of prepared transactions reached
> <F2> HINT: Increase max_prepared_transactions (currently 100).
> <F2> STATEMENT: PREPARE TRANSACTION 'fx_151455979_1216200_16422'
>
> So I increased the max_prepared_transactions of <F1> and <F2> from 100 to 200.
> Then I got the error:
>
> [146926] ERROR: maximum number of foreign transactions reached
> [146926] HINT: Increase max_prepared_foreign_transactions: "200".
>
> So I increased the max_prepared_foreign_transactions to "300",
> and got the same error of need to increase the max_prepared_transactions of foreign servers.
>
> I just can't find the right tuning values for this.
> It seems that we always run out of memory in FdwXactState insert_fdwxact
> with multiple concurrent connections during PREPARE TRANSACTION.
> This one I only encountered for SELECT benchmark.
> Although I've got no problems with multiple connections for my custom scripts for
> UPDATE and INSERT benchmarks when I tested up to 30 clients.
>
> Would the following possibly solve this bottleneck problem?

With the following idea, the performance will get better but will not
be completely solved. Because those results shared by you and
Ikeda-san come from the fact that with the patch we asynchronously
commit the foreign prepared transaction (i.g., asynchronously
performing the second phase of 2PC), but not the architecture. As I
mentioned before, I intentionally removed the synchronous committing
foreign prepared transaction part from the patch set since we still
need to have a discussion of that part. Therefore, with this version
patch, the backend returns OK to the client right after the local
transaction commits with neither committing foreign prepared
transactions by itself nor waiting for those to be committed by the
resolver process. As long as the backend doesn’t wait for foreign
prepared transactions to be committed and there is a limit of the
number of foreign prepared transactions to be held, it could reach the
upper bound if committing foreign prepared transactions cannot keep
up.

Regards,

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