Re: pgbench - adding pl/pgsql versions of tests
My justification for adding pl/pgsql tests as part of the immediately available tests is that pl/pgsql itself is always enabled, so having a no-effort way to test its performance benefits would be really helpful. We also should have "tps-b-like as SQL function" to round up the "test what's available in server" set. --- Hannu On Fri, Feb 2, 2024 at 9:44 PM Robert Haas wrote: > On Tue, Aug 15, 2023 at 11:41 AM Nathan Bossart > 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 >
Re: pgbench - adding pl/pgsql versions of tests
On Tue, Aug 15, 2023 at 11:41 AM Nathan Bossart 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
Re: pgbench - adding pl/pgsql versions of tests
Thanks for the update. I will give it another go over the weekend Cheers, Hannu On Thu, Feb 1, 2024 at 7:33 PM vignesh C wrote: > On Fri, 18 Aug 2023 at 23:04, Hannu Krosing 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 >
Re: pgbench - adding pl/pgsql versions of tests
On Fri, 18 Aug 2023 at 23:04, Hannu Krosing 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
Re: pgbench - adding pl/pgsql versions of tests
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 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.
Re: pgbench - adding pl/pgsql versions of tests
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.
Re: pgbench - adding pl/pgsql versions of tests
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
Re: pgbench - adding pl/pgsql versions of tests
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.
Re: pgbench - adding pl/pgsql versions of tests
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
Re: pgbench - adding pl/pgsql versions of tests
On Fri, 24 Mar 2023 22:17:33 + Cary Huang 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 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 scripts need them. > > I initialized pgbench database with --no-function, and plpgsql-tpcb-like and > plpgsql-simple-update scripts will fail to run 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
Re: pgbench - adding pl/pgsql versions of tests
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 scripts need 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
Re: pgbench - adding pl/pgsql versions of tests
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: plpgsql-tpcb-like: simple-update: plpgsql-simple-update: 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.
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:
plpgsql-tpcb-like:
simple-update:
plpgsql-simple-update:
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.
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index c4a44debeb..1edcec2f5c 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -172,8 +172,8 @@ typedef struct socket_set
/
* some configurable parameters */
-#define DEFAULT_INIT_STEPS "dtgvp" /* default -I setting */
-#define ALL_INIT_STEPS "dtgGvpf" /* all possible steps */
+#define DEFAULT_INIT_STEPS "dYtgvpy" /* default -I setting */
+#define ALL_INIT_STEPS "dYtgGvpfy" /* all possible steps */
#define LOG_STEP_SECONDS 5 /* seconds between log messages */
#define DEFAULT_NXACTS 10 /* default nxacts */
@@ -800,6 +800,15 @@ static const BuiltinScript builtin_script[] =
"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
"END;\n"
},
+ {
+ "plpgsql-tpcb-like",
+ "",
+ "\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
+ "\\set bid random(1, " CppAsString2(nbranches) " * :scale)\n"
+ "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
+ "\\set delta random(-5000, 5000)\n"
+ "SELECT 1 FROM pgbench_tpcb_like(:aid, :bid, :tid, :delta);\n"
+ },
{
"simple-update",
"",
@@ -813,6 +822,15 @@ static const BuiltinScript builtin_script[] =
"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
"END;\n"
},
+ {
+ "plpgsql-simple-update",
+ "",
+ "\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
+ "\\set bid random(1, " CppAsString2(nbranches) " * :scale)\n"
+ "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
+ "\\set delta random(-5000, 5000)\n"
+ "SELECT 1 FROM pgbench_simple_update(:aid, :bid, :tid, :delta);\n"
+ },
{
"select-only",
"",
@@ -885,6 +903,7 @@ usage(void)
" -q, --quiet quiet logging (one message each 5 seconds)\n"
" -s, --scale=NUM scaling factor\n"
" --foreign-keys create foreign key constraints between tables\n"
+ " --no-functions do not create pl/pgsql functions for internal scripts\n"
" --index-tablespace=TABLESPACE\n"
" create indexes in the specified tablespace\n"
" --partition-method=(range|hash)\n"
@@ -4836,6 +4855,54 @@ initTruncateTables(PGconn *con)
"pgbench_tellers");
}
+/*
+ * Create the functions needed for plpgsql-* builting scripts
+ */
+static void
+initCreateFuntions(PGconn *con)
+{
+ fprintf(stderr, "creating functions...\n");
+
+ executeStatement(con,
+ "CREATE FUNCTION pgbench_tpcb_like(_aid int, _bid int, _tid int, _delta int)\n"
+ "RETURNS void\n"
+ "LANGUAGE plpgsql\n"
+ "AS $plpgsql$\n"
+ "BEGIN\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + _delta WHERE aid = _aid;\n"
+ "PERFORM abalance FROM pgbench_accounts WHERE aid = _aid;\n"
+ "UPDATE pgbench_tellers SET tbalance = tbalance + _delta WHERE tid = _tid;\n"
+ "UPDATE pgbench_branches SET bbalance = bbalance + _delta WHERE bid = _bid;\n"
+ "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (_tid, _bid, _aid, _delta, CURRENT_TIMESTAMP);\n"
+ "END;\n"
+ "$plpgsql$;\n");
+ executeStatement(con,
+ "CREATE FUNCTION pgbench_simple_update(_aid int, _bid int, _tid int, _delta int)\n"
+ "RETURNS void\n"
+ "LANGUAGE plpgsql\n"
+ "AS $plpgsql$\n"
+ "BEGIN\n"
+ "UPDATE pgbench_accounts SET abala
