If you use "pgbench -S -M prepared" at a scale where all data fits in
memory, most of what you are benchmarking is network/IPC chatter, and
table locking. Which is fine if that is what you want to do. This
patch adds a new transaction type of -P, which does the same thing as
-S but it moves the main loop of selects, 10,000 at at time, into
pl/pgSQL. This does a good job of exercising the executor rather than
IPC.
This can simulate workloads that have primary key look ups as the
inner side of large nested loop. It is also useful for isolating and
profiling parts of the backend code.
I did not implement this as a new query mode (-M plpgsql), because the
lack of transaction control in pl/pgSQL means it can only be used for
select-only transactions rather than as a general method. So I
thought a new transaction type made more sense.
I didn't implement it as a custom file using -f because:
1) It seems to be a natural extension of the existing built-ins. Also
-f is fiddly. Several times I've wanted to ask posters who are
discussing the other built in transactions to run something like this
and report back, which is easier to do if it is also builtin.
2) It uses a initialization code which -f does not support.
3) I don't see how I can make it automatically detect and respond to
:scale if it were run under -f.
Perhaps issues 2 and 3 would be best addressed by extending the
general -f facility, but that would be a lot more work, and I don't
know how well received it would be.
The reporting might be an issue. I don't want to call it TPS when it
is really not a transaction being reported, so for now I've just left
the TPS as as true transactions, and added a separate reporting line
for selects per second.
I know I also need to add to the web-docs, but I'm hoping to wait on
that until I get some feedback on whether the whole approach is
considered to be viable or not.
some numbers for single client runs on 64-bit AMD Opteron Linux:
12,567 sps under -S
19,646 sps under -S -M prepared
58,165 sps under -P
Cheers,
Jeff
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index bb18c89..9a43fd5 100644
*** a/contrib/pgbench/pgbench.c
--- b/contrib/pgbench/pgbench.c
*************** int fillfactor = 100;
*** 130,135 ****
--- 130,136 ----
* -s instead */
#define ntellers 10
#define naccounts 100000
+ #define plpgsql_loops 10000
bool use_log; /* log transaction latencies to a file */
bool is_connect; /* establish connection for each transaction */
*************** static char *select_only = {
*** 273,278 ****
--- 274,284 ----
"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
};
+ /* -P case */
+ static char *select_only_plpgsql = {
+ "SELECT pgbench_query(" CppAsString2(naccounts) " *:scale," CppAsString2(plpgsql_loops) ");\n"
+ };
+
/* Function prototypes */
static void setalarm(int seconds);
static void *threadRun(void *arg);
*************** printResults(int ttype, int normal_xacts
*** 1686,1691 ****
--- 1692,1699 ----
s = "Update only pgbench_accounts";
else if (ttype == 1)
s = "SELECT only";
+ else if (ttype == 4)
+ s = "SELECT only via plpgsql";
else
s = "Custom query";
*************** printResults(int ttype, int normal_xacts
*** 1708,1713 ****
--- 1716,1726 ----
}
printf("tps = %f (including connections establishing)\n", tps_include);
printf("tps = %f (excluding connections establishing)\n", tps_exclude);
+ if (ttype==4)
+ {
+ printf("selects per second = %f (including connections establishing)\n", tps_include*plpgsql_loops);
+ printf("selects per second = %f (excluding connections establishing)\n", tps_exclude*plpgsql_loops);
+ };
/* Report per-command latencies */
if (is_latencies)
*************** main(int argc, char **argv)
*** 1766,1772 ****
int is_no_vacuum = 0; /* no vacuum at all before testing? */
int do_vacuum_accounts = 0; /* do vacuum accounts before testing? */
int ttype = 0; /* transaction type. 0: TPC-B, 1: SELECT only,
! * 2: skip update of branches and tellers */
char *filename = NULL;
bool scale_given = false;
--- 1779,1785 ----
int is_no_vacuum = 0; /* no vacuum at all before testing? */
int do_vacuum_accounts = 0; /* do vacuum accounts before testing? */
int ttype = 0; /* transaction type. 0: TPC-B, 1: SELECT only,
! * 2: skip update of branches and tellers 3: custom sql, 4: SELECT only via plpgsql*/
char *filename = NULL;
bool scale_given = false;
*************** main(int argc, char **argv)
*** 1823,1829 ****
state = (CState *) xmalloc(sizeof(CState));
memset(state, 0, sizeof(CState));
! while ((c = getopt(argc, argv, "ih:nvp:dSNc:j:Crs:t:T:U:lf:D:F:M:")) != -1)
{
switch (c)
{
--- 1836,1842 ----
state = (CState *) xmalloc(sizeof(CState));
memset(state, 0, sizeof(CState));
! while ((c = getopt(argc, argv, "ih:nvp:dSNPc:j:Crs:t:T:U:lf:D:F:M:")) != -1)
{
switch (c)
{
*************** main(int argc, char **argv)
*** 1851,1856 ****
--- 1864,1872 ----
case 'N':
ttype = 2;
break;
+ case 'P':
+ ttype = 4;
+ break;
case 'c':
nclients = atoi(optarg);
if (nclients <= 0 || nclients > MAXCLIENTS)
*************** main(int argc, char **argv)
*** 2100,2105 ****
--- 2116,2138 ----
scale);
}
+ if (ttype == 4)
+ {
+ executeStatement(con,
+ "create or replace function pgbench_query(scale integer,loops integer) RETURNS integer AS $$ "
+ "DECLARE sum integer default 0; "
+ "amount integer; "
+ "account_id integer; "
+ "BEGIN FOR i IN 1..loops LOOP "
+ " account_id=1+floor(random()*scale); "
+ " SELECT abalance into strict amount FROM pgbench_accounts WHERE aid = account_id; "
+ " sum := sum + amount; "
+ "END LOOP; "
+ "return sum; "
+ "END $$ LANGUAGE plpgsql ");
+ fprintf(stderr, "plgsql function created.\n");
+ };
+
/*
* :scale variables normally get -s or database scale, but don't override
* an explicit -D switch
*************** main(int argc, char **argv)
*** 2153,2158 ****
--- 2186,2196 ----
num_files = 1;
break;
+ case 4:
+ sql_files[0] = process_builtin(select_only_plpgsql);
+ num_files = 1;
+ break;
+
default:
break;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
