Thread: pgbench - adding pl/pgsql versions of tests

Hello Hackers,

The attached patch adds pl/pgsql versions of "tpcb-like" and
"simple-update" internal test scripts

The tests perform functionally exactly the same, but are generally
faster as they avoid most client-server latency.

The reason I'd like to have them as part of pgbench are two

1. so I don't have to create the script and function manually each
time I want to test mainly the database (instead of the
client-database system)

2. so that new users of PostgreSQL can easily see how much better OLTP
workloads perform when packaged up as a server-side function

The new user-visible functionalities are two new build-in scripts  -b list :

$ pgbench -b list
Available builtin scripts:
              tpcb-like: <builtin: TPC-B (sort of)>
      plpgsql-tpcb-like: <builtin: TPC-B (sort of) as a pl/pgsql function>
          simple-update: <builtin: simple update>
  plpgsql-simple-update: <builtin: simple update as a pl/pgsql function>
            select-only: <builtin: select only>

which one can run  using the -b / --builtin= option

pgbench -b plpgsql-tpcb-like ...
or
pgbench -b plpgsql-simple-update ...

And a flag --no-functions which lets you not to create the functions at init

there are also character flags to -I / --init ,
-- Y to drop the functions and
-- y to create the functions. Creating is default behaviour, but can
be disabled fia long flag --no-functions )

I selected Yy as they were unused and can be thought of as "inverted
lambda symbol" :)

If there are no strong objections, I'll add it to the commitfest as well

-----
Hannu Krosing
Google Cloud - We have a long list of planned contributions and we are hiring.
Contact me if interested.

Hello,

> The attached patch adds pl/pgsql versions of "tpcb-like" and
> "simple-update" internal test scripts

Why not, it makes sense because it is relevant to some usage patterns.

Why not having the select version as a version as well?

If we are going to follow this road, we could also consider
"combined" queries with \; as well?

> $ pgbench -b list
> Available builtin scripts:
>              tpcb-like: <builtin: TPC-B (sort of)>
>      plpgsql-tpcb-like: <builtin: TPC-B (sort of) as a pl/pgsql function>
>          simple-update: <builtin: simple update>
>  plpgsql-simple-update: <builtin: simple update as a pl/pgsql function>
>            select-only: <builtin: select only>
>
> which one can run  using the -b / --builtin= option

ISTM that the -b had a fast selection so that only a prefix was enough to 
select a script (-b se = -b select-only). Maybe such convenient shortcut 
should be preserved, it seems that the long name will be needed for the pl 
versions.

> And a flag --no-functions which lets you not to create the functions at init

Hmmm. Not so sure.

> there are also character flags to -I / --init ,
> -- Y to drop the functions and
> -- y to create the functions. Creating is default behaviour, but can
> be disabled fia long flag --no-functions )

Ok.

> I selected Yy as they were unused and can be thought of as "inverted
> lambda symbol" :)

:-)

> If there are no strong objections, I'll add it to the commitfest as well

Please do that.

-- 
Fabien Coelho.

The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:       tested, passed
Spec compliant:           not tested
Documentation:            not tested

Hi

thank you for the patch. It can be applied to current master branch and compiled fine. 

The feature works as described, I am able to run plpgsql-tpcb-like and plpgsql-simple-update scripts as you have added
them.

But I am not sure the purpose of --no-function to prevent the creation of pl/pgsql functions when the new plpgsql test
scriptsneed them. 
 

I initialized pgbench database with --no-function, and plpgsql-tpcb-like and plpgsql-simple-update scripts will fail to
run

thanks

Cary Huang
===============
Highgo Software Canada
www.highgo.ca

On Fri, 24 Mar 2023 22:17:33 +0000
Cary Huang <cary.huang@highgo.ca> wrote:

> The following review has been posted through the commitfest application:
> make installcheck-world:  not tested
> Implements feature:       tested, passed
> Spec compliant:           not tested
> Documentation:            not tested

The patch would need documentations describing the new options.

> 
> Hi
> 
> thank you for the patch. It can be applied to current master branch and compiled fine. 

I also confirmed that it can be applied and complied, although it raised warnings
about whitespace errors.

/tmp/pgbench-plpgsql-001.patch:68: trailing whitespace.
    executeStatement(con, 
/tmp/pgbench-plpgsql-001.patch:102: trailing whitespace.
    executeStatement(con, 
warning: 2 lines add whitespace errors.

> The feature works as described, I am able to run plpgsql-tpcb-like and plpgsql-simple-update scripts as you have
addedthem.
 
> 
> But I am not sure the purpose of --no-function to prevent the creation of pl/pgsql functions when the new plpgsql
testscripts need them. 
 
> 
> I initialized pgbench database with --no-function, and plpgsql-tpcb-like and plpgsql-simple-update scripts will fail
torun
 

I am not sure either whether --no-function option is necessary.
Although there is --no-vacuum, I guess this would be intended to
reduce the initialization time.  I don't think omitting creating
functions  has such effect. So, I wonder -no-function is unnecessary,
as similar to that there are no option to omitting to create tables.


Regards,
Yugo Nagata


-- 
Yugo NAGATA <nagata@sraoss.co.jp>

On Wed, Jan 04, 2023 at 07:06:44PM +0100, Hannu Krosing wrote:
> 1. so I don't have to create the script and function manually each
> time I want to test mainly the database (instead of the
> client-database system)
> 
> 2. so that new users of PostgreSQL can easily see how much better OLTP
> workloads perform when packaged up as a server-side function

I'm not sure we should add micro-optimized versions of the existing scripts
to pgbench.  Your point about demonstrating the benefits of server-side
functions seems reasonable, but it also feels a bit like artifically
improving pgbench numbers.  I think I'd rather see some more variety in the
built-in scripts so that folks can more easily test a wider range of common
workloads.  Perhaps this could include a test that is focused on
server-side functions.

In any case, it looks like there is unaddressed feedback for this patch, so
I'm marking it as "waiting on author."

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Hello Nathan,

>> 1. so I don't have to create the script and function manually each
>> time I want to test mainly the database (instead of the
>> client-database system)
>>
>> 2. so that new users of PostgreSQL can easily see how much better OLTP
>> workloads perform when packaged up as a server-side function
>
> I'm not sure we should add micro-optimized versions of the existing scripts
> to pgbench.  Your point about demonstrating the benefits of server-side
> functions seems reasonable, but it also feels a bit like artifically
> improving pgbench numbers.  I think I'd rather see some more variety in the
> built-in scripts so that folks can more easily test a wider range of common
> workloads.  Perhaps this could include a test that is focused on
> server-side functions.

ISTM that your argument suggests to keep the tpcb-like PL/pgSQL version.
It is the more beneficial anyway as it merges 4/5 commands in one call, so 
it demonstrate the effect of investing in this kind of approach.

I'm unclear about what variety of scripts that could be provided given the 
tables made available with pgbench. ISTM that other scenari would involve 
both an initialization and associated scripts, and any proposal would be 
bared because it would open the door to anything.

> In any case, it looks like there is unaddressed feedback for this patch, so
> I'm marking it as "waiting on author."

Indeed.

-- 
Fabien.

On Tue, Aug 15, 2023 at 09:46:59AM +0200, Fabien COELHO wrote:
> I'm unclear about what variety of scripts that could be provided given the
> tables made available with pgbench. ISTM that other scenari would involve
> both an initialization and associated scripts, and any proposal would be
> bared because it would open the door to anything.

Why's that?  I'm not aware of any project policy that prohibits such
enhancements to pgbench.  It might take some effort to gather consensus on
a proposal like this, but IMHO that doesn't mean we shouldn't try.  If the
prevailing wisdom is that we shouldn't add more built-in scripts because
there is an existing way to provide custom ones, then it's not clear that
we should proceed with $SUBJECT, anyway.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Hello Nathan,

>> I'm unclear about what variety of scripts that could be provided given the
>> tables made available with pgbench. ISTM that other scenari would involve
>> both an initialization and associated scripts, and any proposal would be
>> bared because it would open the door to anything.
>
> Why's that?

Just a wild guess based on 19 years of occasional contributions to pg and 
pgbench in particular:-)

> I'm not aware of any project policy that prohibits such enhancements to 
> pgbench.

Attempts in extending pgbench often fall under "you can do it outside (eg 
with a custom script) so there is no need to put that in pgbench as it 
would add to the maintenance burden with a weak benefit proven by the fact 
that it is not there already".

> It might take some effort to gather consensus on a proposal like this, 
> but IMHO that doesn't mean we shouldn't try.

Done it in the past. Probably will do it again in the future:-)

> If the prevailing wisdom is that we shouldn't add more built-in scripts 
> because there is an existing way to provide custom ones, then it's not 
> clear that we should proceed with $SUBJECT, anyway.

I'm afraid there is that argument. I do not think that this policy is good 
wrt $SUBJECT, ISTM that having an easy way to test something with a 
PL/pgSQL function would help promote the language by advertising/showing 
the potential performance benefit (or not, depending). Just one function 
would be enough for that.

-- 
Fabien.

I will address the comments here over this coming weekend.


I think that in addition to current "tpc-b like" test we could also
have more modern "tpc-c like" and "tpc-h like" tests

And why not any other "* -like" from the rest of TPC-*, YCSP, sysbench, ... :)

though maybe not as part of pg_bench but as extensions ?

---
Hannu

On Wed, Aug 16, 2023 at 10:06 AM Fabien COELHO <coelho@cri.ensmp.fr> wrote:
>
>
> Hello Nathan,
>
> >> I'm unclear about what variety of scripts that could be provided given the
> >> tables made available with pgbench. ISTM that other scenari would involve
> >> both an initialization and associated scripts, and any proposal would be
> >> bared because it would open the door to anything.
> >
> > Why's that?
>
> Just a wild guess based on 19 years of occasional contributions to pg and
> pgbench in particular:-)
>
> > I'm not aware of any project policy that prohibits such enhancements to
> > pgbench.
>
> Attempts in extending pgbench often fall under "you can do it outside (eg
> with a custom script) so there is no need to put that in pgbench as it
> would add to the maintenance burden with a weak benefit proven by the fact
> that it is not there already".
>
> > It might take some effort to gather consensus on a proposal like this,
> > but IMHO that doesn't mean we shouldn't try.
>
> Done it in the past. Probably will do it again in the future:-)
>
> > If the prevailing wisdom is that we shouldn't add more built-in scripts
> > because there is an existing way to provide custom ones, then it's not
> > clear that we should proceed with $SUBJECT, anyway.
>
> I'm afraid there is that argument. I do not think that this policy is good
> wrt $SUBJECT, ISTM that having an easy way to test something with a
> PL/pgSQL function would help promote the language by advertising/showing
> the potential performance benefit (or not, depending). Just one function
> would be enough for that.
>
> --
> Fabien.

On Fri, 18 Aug 2023 at 23:04, Hannu Krosing <hannuk@google.com> wrote:
>
> I will address the comments here over this coming weekend.

The patch which you submitted has been awaiting your attention for
quite some time now.  As such, we have moved it to "Returned with
Feedback" and removed it from the reviewing queue. Depending on
timing, this may be reversible.  Kindly address the feedback you have
received, and resubmit the patch to the next CommitFest.

Regards,
Vignesh

On Tue, Aug 15, 2023 at 11:41 AM Nathan Bossart
<nathandbossart@gmail.com> wrote:
> Why's that?  I'm not aware of any project policy that prohibits such
> enhancements to pgbench.  It might take some effort to gather consensus on
> a proposal like this, but IMHO that doesn't mean we shouldn't try.  If the
> prevailing wisdom is that we shouldn't add more built-in scripts because
> there is an existing way to provide custom ones, then it's not clear that
> we should proceed with $SUBJECT, anyway.

I don't think there's a policy against adding more built-in scripts to
pgbench, but I'm skeptical of such efforts because I don't see how to
decide which ones are worthy of inclusion and which are not. Adding
everyone's favorite thing will be too cluttered, and adding nothing
forecloses nothing because people can always provide their own. If we
could establish that certain custom scripts are widely used across
many people, then those might be worth adding.

I have a vague recollection of someone proposing something similar to
this in the past, possibly Jeff Davis. If there is in fact a paper
trail showing that the same thing has been proposed more than once by
unrelated people, that would be a point in favor of adding that
particular thing.

--
Robert Haas
EDB: http://www.enterprisedb.com