public inbox for [email protected]
help / color / mirror / Atom feedFrom: Fujii Masao <[email protected]>
To: Etsuro Fujita <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: postgres_fdw: commit remote (sub)transactions in parallel during pre-commit
Date: Thu, 18 Nov 2021 13:09:53 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPmGK14rr1R_t4xxWyJVng04nDjGnxi=J5fz-TBNBH9Xn0OFYQ@mail.gmail.com>
References: <CAPmGK17dAZCXvwnfpr1eTfknTGdt=hYTV9405Gt5SqPOX8K84w@mail.gmail.com>
<[email protected]>
<CAPmGK17XczZmzU=0mhk2_oG9Omo1p-uZCNdZAh_3vuC6+YboiA@mail.gmail.com>
<[email protected]>
<CAPmGK14rr1R_t4xxWyJVng04nDjGnxi=J5fz-TBNBH9Xn0OFYQ@mail.gmail.com>
On 2021/11/16 18:55, Etsuro Fujita wrote:
> Sorry, my explanation was not enough, but I don’t think this is always
> true. Let me explain using an example:
>
> create server loopback foreign data wrapper postgres_fdw options
> (dbname 'postgres', parallel_commit 'true');
> create user mapping for current_user server loopback;
> create table t1 (a int, b int);
> create table t2 (a int, b int);
> create foreign table ft1 (a int, b int) server loopback options
> (table_name 't1');
> create foreign table ft2 (a int, b int) server loopback options
> (table_name 't2');
> create role view_owner superuser;
> create user mapping for view_owner server loopback;
> grant SELECT on ft1 to view_owner;
> create view v1 as select * from ft1;
> alter view v1 owner to view_owner;
>
> begin;
> insert into v1 values (10, 10);
> insert into ft2 values (20, 20);
> commit;
>
> For this transaction, since the first insert is executed as the view
> owner while the second insert is executed as the current user, we
> create a connection to the foreign server for each of the users to
> execute the inserts. This leads to sending two commit commands to the
> foreign server at the same time during pre-commit.
>
> To avoid spike loads on a remote server induced by such a workload, I
> think it’s a good idea to have a server option to control whether this
> is enabled,
I understand your point. But even if the option is disabled (i.e.,
commit command is sent to each foreign server in serial way),
multiple queries still can run on the server concurrently and
which may cause performance "spike". Other clients may open several
sessions to the server and issue queries at the same time. Other
sessions using postgres_fdw may send commit command at the same time.
If we want to avoid that "spike", probably we need to decrease
max_connections or use connection pooling, etc. So ISTM that it's
half-baked and not enough to provide the option that controls
whether postgres_fdw issues commit command in parallel or serial way.
> but I might be too worried about that, so I want to hear
> the opinions of people.
Yes.
> IIUC I think the overheads of WaitLatchOrSocket() incurred by a series
> of epoll system calls are much larger compared to the overheads of
> PQconsumeInput() incurred by a recv system call in non-blocking mode
> when no data is available. I didn’t do testing, though.
Understood.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
view thread (22+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: postgres_fdw: commit remote (sub)transactions in parallel during pre-commit
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox