pgbench: faster version of tpcb-like transaction

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pgbench: faster version of tpcb-like transaction
Date: 2017-08-26 22:53:49
Message-ID: CAMkU=1y-66FzO=ALLuwaF4cw3AfXcuB8M+1SCh2=_kNBDEW0mQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

If all the data is in memory and you have a system with fast fsyncs (or are
running with fsync off, or unlogged tables, or synchronous_commit off),
then the big bottleneck in pgbench is the amount of back and forth between
the pgbench program and the backend. There are 7 commands per transaction.

It is easy to package 5 of those commands into a single PL/pgSQL function,
with the other two being implicit via the standard auto-commit behavior
when explicit transactions are not opened. The attached patch does that,
under the name tpcb-func. I first named it tpcb-like-func, but one builtin
name can't be a prefix or another so that won't work.

It creates the function unconditionally during -i, because there is no way
to know if the run-time will end up using it or not. I think this is OK.
PL/pgSQL is installed by default in all supported versions. If someone has
gone through the bother of uninstalling it, I don't see a need to
accommodate them here.

I get nearly a 3 fold speed up using the new transaction, from 9184
to 26383 TPS, on 8 CPU machine using scale 50 and:

PGOPTIONS="-c synchronous_commit=off" pgbench -c32 -j32 -T60 -b tpcb-like

I think this should be committed as a built-in, not just a user-defined
transaction, because I would like to see it widely used. In fact, if it
weren't for historical consistency I would say it should be the default
transaction. Wanting to measure IPC overhead is a valid thing to do, but
certainly isn't the most common thing people want to do with pgbench. If a
user is limited by IO, it wouldn't matter which transaction they use, and
if they are not limited by IO then this transaction is more likely to be
the right one for them than the current default one transaction is.

Also, as a user-defined transaction with -f, you have to go out of your way
to create the function (no "-i" support) and to make sure :scale gets set
correctly during runs (as it won't be automatically read from
pgbench_branches table, you have manually give -D).

Cheers,

Jeff

Attachment Content-Type Size
pgbench_function_v1.patch application/octet-stream 3.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Malis 2017-08-26 23:20:27 Poor cost estimate with interaction between table correlation and partial indexes
Previous Message Jeff Janes 2017-08-26 21:45:20 Re: subscription worker signalling wal writer too much