Re: POC: postgres_fdw insert batching - Mailing list pgsql-hackers
| From | Craig Ringer |
|---|---|
| Subject | Re: POC: postgres_fdw insert batching |
| Date | |
| Msg-id | CAGRY4nzfduKo63e2NKmpcuRBdkhd4SPFXXBehJuowCUrhT_A-g@mail.gmail.com Whole thread Raw |
| In response to | RE: POC: postgres_fdw insert batching ("tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>) |
| Responses |
RE: POC: postgres_fdw insert batching
("tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>)
|
| List | pgsql-hackers |
On Fri, Nov 27, 2020 at 10:47 AM tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com> wrote:
Covering this one first:
I expect postgresExecForeignBatchInsert() would be able to use the libpq batching API, because it receives an array of tuples and can generate and issue INSERT statement for each tuple.
Sure, you can generate big multi-inserts. Or even do a COPY. But you still have to block for a full round-trip until the foreign server replies. So if you have 6000 calls to postgresExecForeignBatchInsert() during a single query, and a 100ms round trip time to the foreign server, you're going to waste 6000*0.1 = 600s = 10 min blocked in postgresExecForeignBatchInsert() waiting for results from the foreign server.
Such batches have some major downsides:
* The foreign server cannot start executing the first query in the batch until the last query in the batch has been accumulated and the whole batch has been sent to the foreign server;
* The FDW has to block waiting for the batch to execute on the foreign server and for a full network round-trip before it can start another batch or let the backend do other work
This means RTTs get multiplied by batch counts. Still a lot better than individual statements, but plenty slow for high latency connections.
* Prepare 1000 rows to insert [10ms]
* INSERT 1000 values [100ms RTT + 50ms foreign server execution time]
* Prepare 1000 rows to insert [10ms]
* INSERT 1000 values [100ms RTT + 50ms foreign server execution time]
* ...
If you can instead send new inserts (or sets of inserts) to the foreign server without having to wait for the result of the previous batch to arrive, you can spend 100ms total waiting for results instead of 10 mins. You can start the execution of the first query earlier, spend less time blocked waiting on network, and let the local backend continue doing other work while the foreign server is busy executing the statements.
The time spent preparing local rows to insert now overlaps with the RTT and remote execution time, instead of happening serially. And there only has to be one RTT wait, assuming the foreign server and network can keep up with the rate we are generating requests at.
I can throw together some diagrams if it'll help. But in the libpq pipelining patch I demonstrated a 300 times (3000%) performance improvement on a test workload...
Anyway, this thread's batch insert can be progressed (and hopefully committed), and once the libpq batching has been committed, we can give it a try to use it and modify postgres_fdw to see if we can get further performance boost.
My point is that you should seriously consider whether batching is the appropriate interface here, or whether the FDW can expose a pipeline-like "queue work" then "wait for results" interface. That can be used to implement batching exactly as currently proposed, it does not have to wait for any libpq pipelining features. But it can *also* be used to implement concurrent async requests in other FDWs, and to implement pipelining in postgres_fdw once the needed libpq support is available.
I don't know the FDW to postgres API well enough, and it's possible I'm talking entirely out of my hat here.
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
> Not sure how is this related to app developers? I think the idea was
> that the libpq features might be useful between the two PostgreSQL
> instances. I.e. the postgres_fdw would use the libpq batching to send
> chunks of data to the other side.
> Well, my point was that we could keep the API, but maybe it should be
> implemented using the proposed libpq batching. They could still use the
> postgres_fdw example how to use the API, but the internals would need to
> be different, of course.
Yes, I understand them. I just wondered if app developers use the statement batching API for libpq or JDBC in what kind of apps.
For JDBC, yes, it's used very heavily and has been for a long time, because PgJDBC doesn't rely on libpq - it implements the protocol directly and isn't bound by libpq's limitations. The application interface for it in JDBC is a batch interface [1][2], not a pipelined interface, so that's what PgJDBC users interact with [3] but batch execution is implemented using protocol pipelining support inside PgJDBC [4]. A while ago I did some work on deadlock prevention to work around issues with PgJDBC's implementation [5] which was needed because the feature was so heavily used. Both were to address customer needs in real world applications. The latter increased application performance over 50x through round-trip elimination.
For libpq, no, batching and pipelining are not yet used by anybody because application authors have to write to the libpq API and there hasn't been any in-core support for batching. We've had async / non-blocking support for a while, but it still enforces strict request/response ordering without interleaving, so application authors cannot make use of the same postgres server and protocol capabilities as PgJDBC. Most other drivers (like psqlODBC and psycopg2) are implemented on top of libpq, so they inherit the same limitations.
I don't expect most application authors to adopt pipelining directly, mainly because hardly anyone writes application code against libpq anyway. But drivers written on top of libpq will be able to adopt it to expose the batching, pipeline, or async/callback/event driven interfaces supported by their client database language interface specifications, or expose their own extension interfaces to give users callback-driven or batched query capabilities. In particular, psqlODBC will be able to implement ODBC batch query [6] efficiently. Right now psqlODBC can't execute batches efficiently via libpq, since it must perform one round-trip per query. It will be able to use the libpq pipelining API to greatly reduce round trips.
But I'm not sure either if the libpq batching is likely to be committed in the near future. (The thread looks too long...)
I think it's getting there tbh.
Regards
Takayuki Tsunakawa
pgsql-hackers by date: