On Fri, Oct 7, 2016 at 8:51 AM, Jeff Janes <jeff.ja...@gmail.com> wrote:

> I think we need to come up with some benchmarking queries which get more
> work done per round-trip to the database. And build them into the binary,
> because otherwise people won't use them as much as they should if they have
> to pass "-f" files around mailing lists and blog postings.   For example,
> we could enclose 5 statements of the TPC-B-like into a single function
> which takes aid, bid, tid, and delta as arguments.  And presumably we could
> drop the other two statements (BEGIN and COMMIT) as well, and rely on
> autocommit to get that job done.  So we could go from 7 statements to 1.

Here is an implementation of that.  I've included the calling code as a
patch to pgbench, because if I make it a separate -f file then it is a pain
to get the correct scale and settings of naccounts, etc., into it.

The create script could be integrated into pgbench -i if this is something
we might want to commit.

This gives me an almost 3 fold increase in performance on a system with
fsync turned off:

pgbench -b tpcb-func  -T30 -c8 -j8
tps = 24193.197877 (excluding connections establishing)

pgbench -b tpcb-like  -T30 -c8 -j8
tps = 8434.746300 (excluding connections establishing)


create or replace function pgbench_transaction(arg_aid int, arg_bid int, 
arg_tid int, arg_delta int) returns int as $$
  abal int;
UPDATE pgbench_accounts SET abalance = abalance + arg_delta WHERE aid = arg_aid;
SELECT abalance into abal FROM pgbench_accounts WHERE aid = arg_aid;
UPDATE pgbench_tellers SET tbalance = tbalance + arg_delta WHERE tid = arg_tid;
UPDATE pgbench_branches SET bbalance = bbalance + arg_delta WHERE bid = arg_bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (arg_tid, 
arg_bid, arg_aid, arg_delta, CURRENT_TIMESTAMP);
RETURN abal;
language plpgsql;

Attachment: pgbench_function.patch
Description: Binary data

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to