Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Daniel Gustafsson]

> On 27 Aug 2017, at 08:37, Fabien COELHO  wrote:
> 
> 
> About the patch:
> 
> I'm generally in favor of providing more options to pgbench, especially if it 
> can give optimization ideas to the performance conscious user.
> 
> I think that the name should be "tpcb-like-plfunc": the script does not 
> implement tpcb per spec, and such a function could be written in another 
> language with some performance benefit, or not.
> 
> Maybe that mean to relax the prefix condition to "take the first matching 
> name" when prefix are used.
> 
> If you are reimplementing the transaction anyway, you could consider using 
> UPDATE RETURNING instead of SELECT to get the balance. On the other hand the 
> doc says that the "steps" are put in a PL function, so maybe it should 
> reflect the original script.
> 
> I'm surprised by:
> 
>  "select * from pgbench_transaction(:aid, :bid, :tid, :delta);\n"
> 
> Why not simply:
> 
>"select pgbench_transaction(:aid, :bid, :tid, :delta);\n"
> 
> I would suggest to use a more precise function name, in case other functions 
> are thought of. Maybe "pgbench_tpcb_like_plfunc".
> 
> I would suggest to indent better the PL/function and put keywords and types 
> in capital, and add explicitely the properties of the function (eg STRICT, 
> VOLATILE?).
> 
> There is a spurious space at the end of the executeStatement call line.
> 
> The patch potentially interacts with other patches in the long and slow 
> queue...
> 
> As usual with pgbench there are no regression tests.

This patch has been Waiting for author during the commitfest without updates,
moving to Returned with feedback.

cheers ./daniel



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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Michael Paquier]

On Sun, Aug 27, 2017 at 12:16 PM, Tom Lane  wrote:
> Jeff Janes  writes:
>> 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.
>
> I dunno, it seems like this proposal involves jacking up the test case
> and driving a completely different one underneath.  There is no reason
> to consider that you've improved the benchmark results --- you've just
> substituted a different benchmark, one with no historical basis, and
> not a lot of field justification either.

Performance comparison between major releases matters only if the same
set of tests is used.

>> 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.
>
> I think that's nonsense.  Measuring how fast PG can do client interactions
> is EXACTLY what this is about.  Certainly, pushing SQL operations into
> server-side functions is a great way to reduce network overhead, but it
> has nothing to do with what we choose as a benchmark.

This thread makes me think that it would be a good idea to add in the
documentation of pgbench a section that gives out a set of scripts
that can be used for emulating more patterns instead of having them in
the code. The proposed test has value if one would like to compare if
it is better for an application to move more things server-side if
there is a lot of latency with the existing tpcb test of pgbench, but
that's not the end of it.
-- 
Michael


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Tatsuo Ishii]

> Don't think anybody is proposing to remove the existing way to run
> pgbench, so I'm not sure what your point is?

I know. I just wanted to point out that the proposal is not good for
cluster environment tests.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Andres Freund]

Hi,

On 2017-08-28 08:05:11 +0900, Tatsuo Ishii wrote:
> With the proposed implementation it is not possible to do that kind of
> test anymore since everything is packed into a function.

Don't think anybody is proposing to remove the existing way to run
pgbench, so I'm not sure what your point is?

Greetings,

Andres Freund


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Tatsuo Ishii]

> Jeff Janes  writes:
>> 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.
> 
> Yeah ...
> 
>> 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.
> 
> I dunno, it seems like this proposal involves jacking up the test case
> and driving a completely different one underneath.  There is no reason
> to consider that you've improved the benchmark results --- you've just
> substituted a different benchmark, one with no historical basis, and
> not a lot of field justification either.
> 
>> 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.
> 
> I think that's nonsense.  Measuring how fast PG can do client interactions
> is EXACTLY what this is about.  Certainly, pushing SQL operations into
> server-side functions is a great way to reduce network overhead, but it
> has nothing to do with what we choose as a benchmark.

Current implementation of pgbench allows Pgpool-II (or any proxy type
middle ware) to test the behavior on PostgreSQL clusters. For example
it sends write queries to the master DB node and read queries to
standby nodes to distribute loads among DB nodes.

With the proposed implementation it is not possible to do that kind of
test anymore since everything is packed into a function.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Fabien COELHO]

About the patch:

I'm generally in favor of providing more options to pgbench, especially if 
it can give optimization ideas to the performance conscious user.


I think that the name should be "tpcb-like-plfunc": the script does not 
implement tpcb per spec, and such a function could be written in another 
language with some performance benefit, or not.


Maybe that mean to relax the prefix condition to "take the first matching 
name" when prefix are used.


If you are reimplementing the transaction anyway, you could consider using 
UPDATE RETURNING instead of SELECT to get the balance. On the other hand 
the doc says that the "steps" are put in a PL function, so maybe it should 
reflect the original script.


I'm surprised by:

  "select * from pgbench_transaction(:aid, :bid, :tid, :delta);\n"

Why not simply:

"select pgbench_transaction(:aid, :bid, :tid, :delta);\n"

I would suggest to use a more precise function name, in case other 
functions are thought of. Maybe "pgbench_tpcb_like_plfunc".


I would suggest to indent better the PL/function and put keywords and 
types in capital, and add explicitely the properties of the function (eg 
STRICT, VOLATILE?).


There is a spurious space at the end of the executeStatement call line.

The patch potentially interacts with other patches in the long and 
slow queue...


As usual with pgbench there are no regression tests.

--
Fabien.


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Fabien COELHO]

Hello Tom,


I dunno, it seems like this proposal involves jacking up the test case
and driving a completely different one underneath.  There is no reason
to consider that you've improved the benchmark results --- you've just
substituted a different benchmark, one with no historical basis, and
not a lot of field justification either.


ISTM that putting some SQL in a function and calling it is standard 
practice in some classes of applications, although probably not the most 
frequent.


Moreover, as far as the TPC-B benchmark is concerned, it looks like a 
perflectly legitimate implementation of the benchmark: the transaction 
profile (Section 1.2) is described as 4 inputs sent in and one result 
returned. The fact that the SQL commands are sent one at a time by the 
client to the server is a pgbench choice that I would not have done if I 
wanted to show the greatest TPC-B numbers with Pg.


Nor does it mean that it is a bad idea to do so... For instance an ORM web 
application might tend to generate simple unprepared CRUD queries and 
interact a lot back and forth, and the default test is not to bad to 
reflect that particular kind of behavior.


Basically there are different kind of application implementations and 
different tests could reflect those. So I am fine with providing more 
benchmarking options to pgbench, and I intend to do so once its 
capabilities are improved.


A caveat I have with Jeff patch is that "tpcb-func" is a misnommer because 
pgbench does NOT implement tpcb per spec, and it is my intention to 
propose a variant which does implement the spec when possible. Now I think 
that I'm also responsible for the prefix constraint on names...


--
Fabien.


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Fabien COELHO]

Hello,

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.


Sure. The throughput of a benchmark depends on a bottleneck which may be 
disk ios, cpu, network, load... depending on the test conditions.


I tested quite a few variants for my PgDay Paris 2017 talk, including PL 
functions, see https://wiki.postgresql.org/wiki/PgDay_Paris_2017.


--
Fabien.


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Tom Lane]

Jeff Janes  writes:
> 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.

Yeah ...

> 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.

I dunno, it seems like this proposal involves jacking up the test case
and driving a completely different one underneath.  There is no reason
to consider that you've improved the benchmark results --- you've just
substituted a different benchmark, one with no historical basis, and
not a lot of field justification either.

> 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.

I think that's nonsense.  Measuring how fast PG can do client interactions
is EXACTLY what this is about.  Certainly, pushing SQL operations into
server-side functions is a great way to reduce network overhead, but it
has nothing to do with what we choose as a benchmark.

regards, tom lane


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Peter Geoghegan]

On Sat, Aug 26, 2017 at 4:59 PM, Jeff Janes  wrote:
> I still get a 2 fold improvement, from 13668 to 27036, when both
> transactions are tested with -M prepared.
>
> I am surprised, I usually haven't seen that much difference for the default
> queries between prepared or not, to the point that I got out of the habit of
> testing with it.  But back when I was testing with and without
> systematically, I did notice that it changed a lot depending on hardware and
> concurrency.  And of course from version to version different bottlenecks
> come and go.

I must admit that I had a similar unpleasant surprise at one point --
"-M prepared" seems to matter *a lot* these days. That's the default
that I'd change, if any.

-- 
Peter Geoghegan


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

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Jeff Janes]

On Sat, Aug 26, 2017 at 4:28 PM, Peter Geoghegan  wrote:

> On Sat, Aug 26, 2017 at 3:53 PM, Jeff Janes  wrote:
> > 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
>
> What about with "-M prepared"? I think that most of us use that
> setting already, especially with CPU-bound workloads.
>

I still get a 2 fold improvement, from 13668 to 27036, when both
transactions are tested with -M prepared.

I am surprised, I usually haven't seen that much difference for the default
queries between prepared or not, to the point that I got out of the habit
of testing with it.  But back when I was testing with and without
systematically, I did notice that it changed a lot depending on hardware
and concurrency.  And of course from version to version different
bottlenecks come and go.

And thanks to Tom for letting me put -M at the end of the command line now.

Cheers,

Jeff

Re: [HACKERS] pgbench: faster version of tpcb-like transaction

[Peter Geoghegan]

On Sat, Aug 26, 2017 at 3:53 PM, Jeff Janes  wrote:
> 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

What about with "-M prepared"? I think that most of us use that
setting already, especially with CPU-bound workloads.


-- 
Peter Geoghegan


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