While looking at some proposed patches and pondering some questions on
performance, I realized I desperately needed ways to run benchmarks with
different settings without needing to edit postgresql.conf and
restart/reload the server each time.
Most commonly, I want to run with synchronous_commit on or off at whim. I
thought of writing a patch specifically for that, but decided a more
general approach would be better. The attached patch allows any arbitrary
command to be executed upon the start up of each connection intended for
benchmarking (as opposed to utility connections, intended for either -i or
for counting the rows in in pgbench_branches and for vacuuming), and so
covers the need for changing any session-changeable GUCs, plus doing other
things as well.
I created doBenchMarkConnect() to segregate bench-marking connections from
utility connections. At first I thought of adding the startup code to only
the normal path and leaving support for -C in the wind, but decided that
was just lazy.
Will add to commitfest-next.
Cheers,
Jeff
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
new file mode 100644
index 11c0062..3a00127
*** a/contrib/pgbench/pgbench.c
--- b/contrib/pgbench/pgbench.c
*************** double sample_rate = 0.0;
*** 142,147 ****
--- 142,148 ----
char *tablespace = NULL;
char *index_tablespace = NULL;
+ char *startup = NULL;
/*
* end of configurable parameters
*********************************************************************/
*************** usage(void)
*** 394,400 ****
" --unlogged-tables\n"
" create tables as unlogged tables\n"
"\nBenchmarking options:\n"
! " -c NUM number of concurrent database clients (default: 1)\n"
" -C establish new connection for each transaction\n"
" -D VARNAME=VALUE\n"
" define variable for use by custom script\n"
--- 395,401 ----
" --unlogged-tables\n"
" create tables as unlogged tables\n"
"\nBenchmarking options:\n"
! " -c NUM number of concurrent database clients (default: 1)\n"
" -C establish new connection for each transaction\n"
" -D VARNAME=VALUE\n"
" define variable for use by custom script\n"
*************** usage(void)
*** 412,420 ****
" -r report average latency per command\n"
" -s NUM report this scale factor in output\n"
" -S perform SELECT-only transactions\n"
! " -t NUM number of transactions each client runs (default: 10)\n"
" -T NUM duration of benchmark test in seconds\n"
" -v vacuum all four standard tables before tests\n"
"\nCommon options:\n"
" -d print debugging output\n"
" -h HOSTNAME database server host or socket directory\n"
--- 413,423 ----
" -r report average latency per command\n"
" -s NUM report this scale factor in output\n"
" -S perform SELECT-only transactions\n"
! " -t NUM number of transactions each client runs (default: 10)\n"
" -T NUM duration of benchmark test in seconds\n"
" -v vacuum all four standard tables before tests\n"
+ " --startup=COMMAND\n"
+ " Run COMMAND upon creating each benchmarking connection\n"
"\nCommon options:\n"
" -d print debugging output\n"
" -h HOSTNAME database server host or socket directory\n"
*************** executeStatement(PGconn *con, const char
*** 520,526 ****
res = PQexec(con, sql);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
! fprintf(stderr, "%s", PQerrorMessage(con));
exit(1);
}
PQclear(res);
--- 523,529 ----
res = PQexec(con, sql);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
! fprintf(stderr, "Command failed with %s", PQerrorMessage(con));
exit(1);
}
PQclear(res);
*************** doConnect(void)
*** 593,598 ****
--- 596,613 ----
return conn;
}
+ /* set up a connection to the backend intended for benchmarking */
+ static PGconn *
+ doBenchMarkConnect(void)
+ {
+ static PGconn * conn;
+ conn = doConnect();
+ if (startup != NULL) {
+ executeStatement(conn, startup);
+ };
+ return conn;
+ };
+
/* throw away response from backend */
static void
discard_response(CState *state)
*************** top:
*** 1131,1137 ****
end;
INSTR_TIME_SET_CURRENT(start);
! if ((st->con = doConnect()) == NULL)
{
fprintf(stderr, "Client %d aborted in establishing connection.\n", st->id);
return clientDone(st, false);
--- 1146,1152 ----
end;
INSTR_TIME_SET_CURRENT(start);
! if ((st->con = doBenchMarkConnect()) == NULL)
{
fprintf(stderr, "Client %d aborted in establishing connection.\n", st->id);
return clientDone(st, false);
*************** main(int argc, char **argv)
*** 2139,2144 ****
--- 2154,2160 ----
{"unlogged-tables", no_argument, &unlogged_tables, 1},
{"sampling-rate", required_argument, NULL, 4},
{"aggregate-interval", required_argument, NULL, 5},
+ {"startup", required_argument, NULL, 6},
{NULL, 0, NULL, 0}
};
*************** main(int argc, char **argv)
*** 2366,2371 ****
--- 2382,2390 ----
case 2: /* tablespace */
tablespace = pg_strdup(optarg);
break;
+ case 6: /* tablespace */
+ startup = pg_strdup(optarg);
+ break;
case 3: /* index-tablespace */
index_tablespace = pg_strdup(optarg);
break;
*************** threadRun(void *arg)
*** 2749,2755 ****
/* make connections to the database */
for (i = 0; i < nstate; i++)
{
! if ((state[i].con = doConnect()) == NULL)
goto done;
}
}
--- 2768,2774 ----
/* make connections to the database */
for (i = 0; i < nstate; i++)
{
! if ((state[i].con = doBenchMarkConnect()) == NULL)
goto done;
}
}
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
new file mode 100644
index 79b4baf..a4324f1
*** a/doc/src/sgml/pgbench.sgml
--- b/doc/src/sgml/pgbench.sgml
*************** pgbench <optional> <replaceable>options<
*** 479,484 ****
--- 479,499 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--startup=<replaceable>COMMAND</replaceable></option></term>
+ <listitem>
+ <para>
+ Execute the <replaceable>COMMAND</replaceable> upon the establishment
+ of each connection intended for receipt of benchmarking queries.
+ </para>
+ <para>
+ For example <literal>--startup="set synchronous_commit=off"</> will cause benchmarking
+ to be done without fsyncing the WAL for every transaction, regardless of the default
+ server settings.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
