Batch executemany by fantix · Pull Request #295 · MagicStack/asyncpg · GitHub

Refs #289, this is a rewrite of bind_execute_many():

Batch bind args into 4 x 32KB buffers, sent with writelines() once flow control turns green.
Send one SYNC at the very last - making use of the implicit transaction for the whole call to executemany().
Abort immediately on either client error (with explicit rollback) or server error.
Support async iterable.
Add executemany() to prepared statement.

test_server_failure_during_writes is failing on Windows
Fix unstable test_timeout (test_execute.TestExecuteMany)

This is a breaking change due to the implicit transaction.

The 3-level infinite loop keeps filling buffers and send them under flow control, the only exit is raising StopIteration with 3 possible values:

True means there was data sent in the last loop
False means the last loop sent nothing
exception means the data source raised an exception

Please note, during the loop, if server-side error is detected before exhausting the data source, it is also considered as StopIteration(True), ending the process early with a SYNC_MESSAGE.

(I wrote a post about the details of implicit transaction.)

UPDATED - pgbench results of inserting 1000 rows per query with executemany() on Python 3.6 of 2.2GHz 2015 MacBook Air (best out of 5 runs):

asyncpg 0.18.2

710 queries in 30.31 seconds
Latency: min 341.88ms; max 636.29ms; mean 425.022ms; std: 39.782ms (9.36%)
Latency distribution: 25% under 401.67ms; 50% under 414.26ms; 75% under 435.37ms; 90% under 478.39ms; 99% under 576.638ms; 99.99% under 636.299ms
Queries/sec: 23.42
Rows/sec: 23424.32

This PR:

4125 queries in 30.02 seconds
Latency: min 23.14ms; max 734.91ms; mean 72.723ms; std: 49.226ms (67.69%)
Latency distribution: 25% under 59.958ms; 50% under 65.414ms; 75% under 71.538ms; 90% under 80.95ms; 99% under 175.375ms; 99.99% under 734.912ms
Queries/sec: 137.39
Rows/sec: 137389.64

Most branches are covered by tests, overall coverage 85% (1352 miss) -> 85% (1356 miss).

UPDATED - Python 3.8 of 2.3GHz 2020 MacBook Pro:

… only

Other than the transaction status check, LGTM! Thanks!

Update - I've met a racing condition issue during fixing tests. Trying to find time to fix that.

Hey @fantix! Are you still working on this?

Hi! Yes I’m trying to find some time to fix the racing issue, sorry for the delay!

@fantix, I just merged #361 and #362, which may help improve the situation with the race condition you were seeing.

Working on this now - rebased and I'll review the changes, and deal with the racing issue.

Sorry for the delay! This PR is updated now. Benchmark shows the improvement is consistent. I've also added a diagram to explain the details.

@fantix The test_timeout test is flaky because of pipelining. All commands, including the final Sync can actually go in under 500ms, so the changes are persisted despite the TimeoutError. You might get better luck by passing a generator with time.sleep() in it to executemany().

@elprans any chance this can get merged in after the conflict in asyncpg/protocol/protocol.pyx is fixed?

Checking in to see if this PR is still planning on being merged

@fantix, are you interested in getting this in shape to be merged?

bumping this PR. it would be awesome to see it finally merged!! lets goooo ^^

Let me try this weekend!

Only rebased - I do want to revisit this to make sure everything is still alright.

Now `Bind` and `Execute` pairs are batched into 4 x 32KB buffers to take advantage of `writelines()`. A single `Sync` is sent at last, so that all args live in the same transaction. Closes: MagicStack#289

I've rewritten the core part to be more readable, and included @elprans 's commit to fix the timeout, and added one more test. If the Travis CI passes, I think it'll be good for another review.

Looks like all tests passed! Can we get this merged in?

LGTM and merged. @fantix, thank you very much for working on this!

A new asyncpg release is here, just in time for Christmas. Notable additions include Python 3.9 support, support for recently added PostgreSQL types like `jsonpath`, and last but not least, vastly improved `executemany()` performance. Importantly, `executemany()` is also now _atomic_, which means that either all iterations succeed, or none at all, whereas previously partial results would have remained in place, unless `executemany()` was called in a transaction. There is also the usual assortment of improvements and bugfixes, see the details below. This is the last release of asyncpg that supports Python 3.5, which is has reached EOL in September. Improvements ------------ * Vastly speedup executemany by batching protocol messages (#295) (by @fantix in 690048d for #295) * Allow using custom Record class (by @elprans in db4f1a6 for #577) * Add Python 3.9 support (#610) (by @elprans in c05d726 for #610) * Prefer SSL connections by default (#660) (by @elprans in 16183aa for #660) * Add codecs for a bunch of new builtin types (#665) (by @elprans in b53f038 for #665) * Expose Pool as asyncpg.Pool (#669) (by @rugleb in 0e0eb8d for #669) Fixes ----- * Add a workaround for bpo-37658 (by @elprans in 2bac166 for #21894) * Fix wrong default transaction isolation level (#622) (by @fantix in 4a627d5 for #622) * Fix set_type_codec() to accept standard SQL type names (#619) (by @elprans in 68b40cb for #619) * Ignore custom data codec for internal introspection (#618) (by @fantix in e064f59 for #618) * Fix null/NULL quoting in array text encoder (#627) (by @fantix in 92aa806 for #627) * Fix link in connect docstring (#653) (by @samuelcolvin in 8b313bd for #653) * Make asyncpg work with pyinstaller (#651) (by @Atem18 in 5ddabb1 for #651) * Fix possible AttributeError exception in `ConnectionSettings` (#632) (by @petriborg in 0d23182 for #632) * Prohibit custom codecs on domains (by @elprans in 50f964f for #457) * Raise proper error on anonymous composite input (tuple arguments) (#664) (by @elprans in 7252dbe for #664) * Fix incorrect application of custom codecs in some cases (#662) (by @elprans in 50f65fb for #662)

A new asyncpg release is here, just in time for Christmas. Notable additions include Python 3.9 support, support for recently added PostgreSQL types like `jsonpath`, and last but not least, vastly improved `executemany()` performance. Importantly, `executemany()` is also now _atomic_, which means that either all iterations succeed, or none at all, whereas previously partial results would have remained in place, unless `executemany()` was called in a transaction. There is also the usual assortment of improvements and bugfixes, see the details below. This is the last release of asyncpg that supports Python 3.5, which is has reached EOL in September. Improvements ------------ * Vastly speedup executemany by batching protocol messages (#295) (by @fantix in 690048d for #295) * Allow using custom `Record` class (by @elprans in db4f1a6 for #577) * Add Python 3.9 support (#610) (by @elprans in c05d726 for #610) * Prefer SSL connections by default (#660) (by @elprans in 16183aa for #660) * Add codecs for a bunch of new builtin types (#665) (by @elprans in b53f038 for #665) * Expose Pool as `asyncpg.Pool` (#669) (by @rugleb in 0e0eb8d for #669) Fixes ----- * Add a workaround for bpo-37658 (by @elprans in 2bac166 for #21894) * Fix wrong default transaction isolation level (#622) (by @fantix in 4a627d5 for #622) * Fix `set_type_codec()` to accept standard SQL type names (#619) (by @elprans in 68b40cb for #619) * Ignore custom data codec for internal introspection (#618) (by @fantix in e064f59 for #618) * Fix null/NULL quoting in array text encoder (#627) (by @fantix in 92aa806 for #627) * Fix link in connect docstring (#653) (by @samuelcolvin in 8b313bd for #653) * Make asyncpg work with pyinstaller (#651) (by @Atem18 in 5ddabb1 for #651) * Fix possible `AttributeError` exception in `ConnectionSettings` (#632) (by @petriborg in 0d23182 for #632) * Prohibit custom codecs on domains (by @elprans in 50f964f for #457) * Raise proper error on anonymous composite input (tuple arguments) (#664) (by @elprans in 7252dbe for #664) * Fix incorrect application of custom codecs in some cases (#662) (by @elprans in 50f65fb for #662)

A new asyncpg release is here, just in time for Christmas. Notable additions include Python 3.9 support, support for recently added PostgreSQL types like `jsonpath`, and last but not least, vastly improved `executemany()` performance. Importantly, `executemany()` is also now _atomic_, which means that either all iterations succeed, or none at all, whereas previously partial results would have remained in place, unless `executemany()` was called in a transaction. There is also the usual assortment of improvements and bugfixes, see the details below. This is the last release of asyncpg that supports Python 3.5, which has reached EOL in September. Improvements ------------ * Vastly speedup executemany by batching protocol messages (#295) (by @fantix in 690048d for #295) * Allow using custom `Record` class (by @elprans in db4f1a6 for #577) * Add Python 3.9 support (#610) (by @elprans in c05d726 for #610) * Prefer SSL connections by default (#660) (by @elprans in 16183aa for #660) * Add codecs for a bunch of new builtin types (#665) (by @elprans in b53f038 for #665) * Expose Pool as `asyncpg.Pool` (#669) (by @rugleb in 0e0eb8d for #669) Fixes ----- * Add a workaround for bpo-37658 (by @elprans in 2bac166 for #21894) * Fix wrong default transaction isolation level (#622) (by @fantix in 4a627d5 for #622) * Fix `set_type_codec()` to accept standard SQL type names (#619) (by @elprans in 68b40cb for #619) * Ignore custom data codec for internal introspection (#618) (by @fantix in e064f59 for #618) * Fix null/NULL quoting in array text encoder (#627) (by @fantix in 92aa806 for #627) * Fix link in connect docstring (#653) (by @samuelcolvin in 8b313bd for #653) * Make asyncpg work with pyinstaller (#651) (by @Atem18 in 5ddabb1 for #651) * Fix possible `AttributeError` exception in `ConnectionSettings` (#632) (by @petriborg in 0d23182 for #632) * Prohibit custom codecs on domains (by @elprans in 50f964f for #457) * Raise proper error on anonymous composite input (tuple arguments) (#664) (by @elprans in 7252dbe for #664) * Fix incorrect application of custom codecs in some cases (#662) (by @elprans in 50f65fb for #662)

A new asyncpg release is here. Notable additions include Python 3.9 support, support for recently added PostgreSQL types like `jsonpath`, and last but not least, vastly improved `executemany()` performance. Importantly, `executemany()` is also now _atomic_, which means that either all iterations succeed, or none at all, whereas previously partial results would have remained in place, unless `executemany()` was called in a transaction. There is also the usual assortment of improvements and bugfixes, see the details below. This is the last release of asyncpg that supports Python 3.5, which has reached EOL last September. Improvements ------------ * Vastly speedup executemany by batching protocol messages (#295) (by @fantix in 690048d for #295) * Allow using custom `Record` class (by @elprans in db4f1a6 for #577) * Add Python 3.9 support (#610) (by @elprans in c05d726 for #610) * Prefer SSL connections by default (#660) (by @elprans in 16183aa for #660) * Add codecs for a bunch of new builtin types (#665) (by @elprans in b53f038 for #665) * Expose Pool as `asyncpg.Pool` (#669) (by @rugleb in 0e0eb8d for #669) * Avoid unnecessary overhead during connection reset (#648) (by @kitogo in ff5da5f for #648) Fixes ----- * Add a workaround for bpo-37658 (by @elprans in 2bac166 for #21894) * Fix wrong default transaction isolation level (#622) (by @fantix in 4a627d5 for #622) * Fix `set_type_codec()` to accept standard SQL type names (#619) (by @elprans in 68b40cb for #619) * Ignore custom data codec for internal introspection (#618) (by @fantix in e064f59 for #618) * Fix null/NULL quoting in array text encoder (#627) (by @fantix in 92aa806 for #627) * Fix link in connect docstring (#653) (by @samuelcolvin in 8b313bd for #653) * Make asyncpg work with pyinstaller (#651) (by @Atem18 in 5ddabb1 for #651) * Fix possible `AttributeError` exception in `ConnectionSettings` (#632) (by @petriborg in 0d23182 for #632) * Prohibit custom codecs on domains (by @elprans in 50f964f for #457) * Raise proper error on anonymous composite input (tuple arguments) (#664) (by @elprans in 7252dbe for #664) * Fix incorrect application of custom codecs in some cases (#662) (by @elprans in 50f65fb for #662)

A new asyncpg release is here. Notable additions include Python 3.9 support, support for recently added PostgreSQL types like `jsonpath`, and last but not least, vastly improved `executemany()` performance. Importantly, `executemany()` is also now _atomic_, which means that either all iterations succeed, or none at all, whereas previously partial results would have remained in place, unless `executemany()` was called in a transaction. There is also the usual assortment of improvements and bugfixes, see the details below. This is the last release of asyncpg that supports Python 3.5, which has reached EOL last September. Improvements ------------ * Vastly speedup executemany by batching protocol messages (MagicStack#295) (by @fantix in 690048d for MagicStack#295) * Allow using custom `Record` class (by @elprans in db4f1a6 for MagicStack#577) * Add Python 3.9 support (MagicStack#610) (by @elprans in c05d726 for MagicStack#610) * Prefer SSL connections by default (MagicStack#660) (by @elprans in 16183aa for MagicStack#660) * Add codecs for a bunch of new builtin types (MagicStack#665) (by @elprans in b53f038 for MagicStack#665) * Expose Pool as `asyncpg.Pool` (MagicStack#669) (by @rugleb in 0e0eb8d for MagicStack#669) * Avoid unnecessary overhead during connection reset (MagicStack#648) (by @kitogo in ff5da5f for MagicStack#648) Fixes ----- * Add a workaround for bpo-37658 (by @elprans in 2bac166 for #21894) * Fix wrong default transaction isolation level (MagicStack#622) (by @fantix in 4a627d5 for MagicStack#622) * Fix `set_type_codec()` to accept standard SQL type names (MagicStack#619) (by @elprans in 68b40cb for MagicStack#619) * Ignore custom data codec for internal introspection (MagicStack#618) (by @fantix in e064f59 for MagicStack#618) * Fix null/NULL quoting in array text encoder (MagicStack#627) (by @fantix in 92aa806 for MagicStack#627) * Fix link in connect docstring (MagicStack#653) (by @samuelcolvin in 8b313bd for MagicStack#653) * Make asyncpg work with pyinstaller (MagicStack#651) (by @Atem18 in 5ddabb1 for MagicStack#651) * Fix possible `AttributeError` exception in `ConnectionSettings` (MagicStack#632) (by @petriborg in 0d23182 for MagicStack#632) * Prohibit custom codecs on domains (by @elprans in 50f964f for MagicStack#457) * Raise proper error on anonymous composite input (tuple arguments) (MagicStack#664) (by @elprans in 7252dbe for MagicStack#664) * Fix incorrect application of custom codecs in some cases (MagicStack#662) (by @elprans in 50f65fb for MagicStack#662)

fantix mentioned this pull request May 29, 2018

[RFC] Combine args of executemany() in batches #289

Closed

fantix changed the title [WIP] Batch executemany Batch executemany May 29, 2018

elprans reviewed May 29, 2018

View reviewed changes

Comment thread asyncpg/protocol/coreproto.pyx Outdated

elprans reviewed May 29, 2018

View reviewed changes

Comment thread asyncpg/protocol/coreproto.pyx Outdated

fantix added a commit to fantix/asyncpg that referenced this pull request May 30, 2018

CRF MagicStack#295 , use simple query ROLLBACK in implicit transaction…

19a2798

… only

elprans reviewed May 30, 2018

View reviewed changes

Comment thread asyncpg/protocol/coreproto.pyx Outdated

elprans approved these changes May 30, 2018

View reviewed changes

1st1 requested changes May 30, 2018

View reviewed changes

Comment thread asyncpg/protocol/protocol.pyx Outdated

Comment thread asyncpg/protocol/protocol.pyx Outdated

Comment thread asyncpg/protocol/protocol.pyx Outdated

elprans mentioned this pull request Aug 26, 2018

Very bad performance using insert many #346

Closed

fantix force-pushed the t289_batch_executemany branch 5 times, most recently from f62a347 to 06b412b Compare November 26, 2018 12:24

fantix force-pushed the t289_batch_executemany branch 7 times, most recently from 46ecc07 to 6c206e9 Compare November 28, 2018 10:05

elprans mentioned this pull request Mar 22, 2019

Upset best practice? #420

Closed

elprans mentioned this pull request Apr 15, 2019

Q: how does Connection.executemany work? #433

Closed

fantix force-pushed the t289_batch_executemany branch from 4be5198 to a3bfcff Compare November 2, 2020 05:04

fantix force-pushed the t289_batch_executemany branch from a3bfcff to 557f515 Compare November 24, 2020 06:41

Rewrite executemany() to batch args for performance.

c4c5b5e

Now `Bind` and `Execute` pairs are batched into 4 x 32KB buffers to take advantage of `writelines()`. A single `Sync` is sent at last, so that all args live in the same transaction. Closes: MagicStack#289

fantix force-pushed the t289_batch_executemany branch from 557f515 to b137184 Compare November 24, 2020 18:34

Keep track of timeout in executemany properly

39dfbb4

fantix force-pushed the t289_batch_executemany branch from b137184 to 39dfbb4 Compare November 24, 2020 22:51

Bump to osx 10.14 for Travis build

712d60d

elprans merged commit 690048d into MagicStack:master Nov 26, 2020

elprans mentioned this pull request Nov 26, 2020

Bump to macOS 10.14 for Travis build #659

Closed

fantix deleted the t289_batch_executemany branch November 27, 2020 00:12

amotl mentioned this pull request Dec 3, 2020

Efficient batch operations crate/tsperf#18

Open

mfussenegger mentioned this pull request Dec 4, 2020

Support SQLAlchemy dialect with psycopg2 crate/sqlalchemy-cratedb#100

Closed

elprans mentioned this pull request Dec 21, 2020

asyncpg v0.22.0 #680

Merged

amotl mentioned this pull request Feb 10, 2021

split large data inserts orchestracities/ngsi-timeseries-api#445

Closed

chicco785 mentioned this pull request Mar 10, 2021

asyncpg potential orchestracities/ngsi-timeseries-api#463

Open

Batch executemany by fantix · Pull Request #295 · MagicStack/asyncpg · GitHub

Uh oh!

Batch executemany - #295

Batch executemany#295elprans merged 3 commits intoMagicStack:masterMagicStack/asyncpg:masterfrom fantix:t289_batch_executemanyfantix/asyncpg:t289_batch_executemanyCopy head branch name to clipboard

Conversation

fantix commented May 29, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

elprans left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

fantix commented Jun 23, 2018

Uh oh!

elprans commented Aug 28, 2018

Uh oh!

fantix commented Aug 28, 2018

Uh oh!

elprans commented Sep 18, 2018

Uh oh!

fantix commented Oct 25, 2018

Uh oh!

fantix commented Nov 26, 2018

Uh oh!

elprans commented May 8, 2019

Uh oh!

robd003 commented Sep 29, 2020

Uh oh!

gtlanigan315 commented Oct 27, 2020

Uh oh!

elprans commented Oct 29, 2020

Uh oh!

cmflynn commented Oct 31, 2020

Uh oh!

fantix commented Oct 31, 2020

Uh oh!

fantix commented Nov 2, 2020

Uh oh!

fantix commented Nov 24, 2020

Uh oh!

robd003 commented Nov 25, 2020

Uh oh!

elprans commented Nov 26, 2020

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

10 participants

Batch executemany#295
elprans merged 3 commits into
MagicStack:masterfrom
fantix:t289_batch_executemany

fantix commented May 29, 2018 •
edited
Loading