On 2017-06-20 17:51:23 +0200, Daniel Verite wrote: > Andres Freund wrote: > > > FWIW, I still think this needs a pgbench or similar example integration, > > so we can actually properly measure the benefits. > > Here's an updated version of the patch I made during review, > adding \beginbatch and \endbatch to pgbench. > The performance improvement appears clearly > with a custom script of this kind: > \beginbatch > UPDATE pgbench_branches SET bbalance = bbalance + 1 WHERE bid = 0; > ..above repeated 1000 times... > \endbatch > > versus the same with a BEGIN; END; pair instead of \beginbatch \endbatch > > On localhost on my desktop I tend to see a 30% difference in favor > of the batch mode with that kind of test. > On slower networks there are much bigger differences.
This is seriously impressive. Just using the normal pgbench mixed workload, wrapping a whole transaction into a batch *doubles* the throughput. And that's locally over a unix socket - the gain over actual network will be larger. \set nbranches 1 * :scale \set ntellers 10 * :scale \set naccounts 100000 * :scale \set aid random(1, :naccounts) \set bid random(1, :nbranches) \set tid random(1, :ntellers) \set delta random(-5000, 5000) \beginbatch BEGIN; UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END; \endbatch - Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
