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) Cheers, Jeff
create or replace function pgbench_transaction(arg_aid int, arg_bid int, arg_tid int, arg_delta int) returns int as $$ DECLARE abal int; BEGIN 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; END; $$ language plpgsql;
pgbench_function.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
