Thread: pgbench - startup delay
Whilst doing some profiling of the server I found it useful to add an
option to pgbench to introduce a delay between client connection setup
and the start of the benchmark itself to allow me time to attach the
profiler to one of the backends.
Attached is the patch in case anyone finds a use for it, or if it's
deemed to be generally useful enough for inclusion in 8.4.
Regards, Dave.
Index: doc/src/sgml/pgbench.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/pgbench.sgml,v
retrieving revision 1.4
diff -c -r1.4 pgbench.sgml
*** doc/src/sgml/pgbench.sgml 10 Dec 2007 05:32:51 -0000 1.4
--- doc/src/sgml/pgbench.sgml 10 Dec 2007 19:05:13 -0000
***************
*** 250,255 ****
--- 250,264 ----
</entry>
</row>
<row>
+ <entry><literal>-w</literal> <replaceable>startup_delay</></entry>
+ <entry>
+ Pause for the specified number of seconds after creating the
+ client connections. This is useful to allow time to connect
+ a debugger or profiler to a backend server process before the
+ benchmark is run.
+ </entry>
+ </row>
+ <row>
<entry><literal>-d</literal></entry>
<entry>
Print debugging output.
Index: contrib/pgbench/pgbench.c
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/pgbench/pgbench.c,v
retrieving revision 1.74
diff -c -r1.74 pgbench.c
*** contrib/pgbench/pgbench.c 15 Nov 2007 21:14:31 -0000 1.74
--- contrib/pgbench/pgbench.c 10 Dec 2007 18:58:55 -0000
***************
*** 188,194 ****
static void
usage(void)
{
! fprintf(stderr, "usage: pgbench [-h hostname][-p port][-c nclients][-t ntransactions][-s scaling_factor][-D
varname=value][-n][-C][-v][-S][-N][-ffilename][-l][-U login][-P password][-d][dbname]\n");
fprintf(stderr, "(initialize mode): pgbench -i [-h hostname][-p port][-s scaling_factor] [-F fillfactor] [-U
login][-Ppassword][-d][dbname]\n");
}
--- 188,194 ----
static void
usage(void)
{
! fprintf(stderr, "usage: pgbench [-h hostname][-p port][-c nclients][-t ntransactions][-s scaling_factor][-D
varname=value][-n][-C][-v][-S][-N][-ffilename][-l][-U login][-P password][-w startup_delay][-d][dbname]\n");
fprintf(stderr, "(initialize mode): pgbench -i [-h hostname][-p port][-s scaling_factor] [-F fillfactor] [-U
login][-Ppassword][-d][dbname]\n");
}
***************
*** 1163,1169 ****
printResults(
int ttype, CState * state,
struct timeval * tv1, struct timeval * tv2,
! struct timeval * tv3)
{
double t1,
t2;
--- 1163,1169 ----
printResults(
int ttype, CState * state,
struct timeval * tv1, struct timeval * tv2,
! struct timeval * tv3, int startup_delay)
{
double t1,
t2;
***************
*** 1174,1183 ****
for (i = 0; i < nclients; i++)
normal_xacts += state[i].cnt;
! t1 = (tv3->tv_sec - tv1->tv_sec) * 1000000.0 + (tv3->tv_usec - tv1->tv_usec);
t1 = normal_xacts * 1000000.0 / t1;
! t2 = (tv3->tv_sec - tv2->tv_sec) * 1000000.0 + (tv3->tv_usec - tv2->tv_usec);
t2 = normal_xacts * 1000000.0 / t2;
if (ttype == 0)
--- 1174,1183 ----
for (i = 0; i < nclients; i++)
normal_xacts += state[i].cnt;
! t1 = (tv3->tv_sec - (tv1->tv_sec + startup_delay)) * 1000000.0 + (tv3->tv_usec - tv1->tv_usec);
t1 = normal_xacts * 1000000.0 / t1;
! t2 = (tv3->tv_sec - (tv2->tv_sec + startup_delay)) * 1000000.0 + (tv3->tv_usec - tv2->tv_usec);
t2 = normal_xacts * 1000000.0 / t2;
if (ttype == 0)
***************
*** 1217,1222 ****
--- 1217,1224 ----
struct timeval tv2; /* after establishing all connections to the
* backend */
struct timeval tv3; /* end time */
+
+ int startup_delay = 0; /* Post-connect delay */
int i;
***************
*** 1258,1264 ****
memset(state, 0, sizeof(*state));
! while ((c = getopt(argc, argv, "ih:nvp:dc:t:s:U:P:CNSlf:D:F:")) != -1)
{
switch (c)
{
--- 1260,1266 ----
memset(state, 0, sizeof(*state));
! while ((c = getopt(argc, argv, "ih:nvp:dc:t:s:U:P:CNSlf:D:F:w:")) != -1)
{
switch (c)
{
***************
*** 1371,1376 ****
--- 1373,1386 ----
exit(1);
}
break;
+ case 'w':
+ startup_delay = atoi(optarg);
+ if (startup_delay < 0)
+ {
+ fprintf(stderr, "invalid startup_delay: %d\n", startup_delay);
+ exit(1);
+ }
+ break;
default:
usage();
exit(1);
***************
*** 1553,1558 ****
--- 1563,1580 ----
/* time after connections set up */
gettimeofday(&tv2, NULL);
+ if (startup_delay)
+ {
+ fprintf(stderr, "pausing for %d seconds...", startup_delay);
+ #ifndef WIN32
+ sleep(startup_delay);
+ #else
+ Sleep(startup_delay * 1000);
+ #endif
+ fprintf(stderr, "end.\n");
+
+ }
+
/* process bultin SQL scripts */
switch (ttype)
{
***************
*** 1600,1606 ****
disconnect_all(state);
/* get end time */
gettimeofday(&tv3, NULL);
! printResults(ttype, state, &tv1, &tv2, &tv3);
if (LOGFILE)
fclose(LOGFILE);
exit(0);
--- 1622,1628 ----
disconnect_all(state);
/* get end time */
gettimeofday(&tv3, NULL);
! printResults(ttype, state, &tv1, &tv2, &tv3, startup_delay);
if (LOGFILE)
fclose(LOGFILE);
exit(0);
Dave Page wrote:
> Whilst doing some profiling of the server I found it useful to add an
> option to pgbench to introduce a delay between client connection setup
> and the start of the benchmark itself to allow me time to attach the
> profiler to one of the backends.
Hmm, the backend already has a delay, see -W.
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Executive Executive Summary: The [Windows] Vista Content Protection
specification could very well constitute the longest suicide note in history."
Peter Guttman, http://www.cs.auckland.ac.nz/~pgut001/pubs/vista_cost.txt
On Mon, 2007-12-10 at 19:27 +0000, Dave Page wrote: > Whilst doing some profiling of the server I found it useful to add an > option to pgbench to introduce a delay between client connection setup > and the start of the benchmark itself to allow me time to attach the > profiler to one of the backends. "postgres -W n" already does this. It is more flexible to put this functionality in the backend that in individual client apps anyway. -Neil
Neil Conway wrote: > On Mon, 2007-12-10 at 19:27 +0000, Dave Page wrote: >> Whilst doing some profiling of the server I found it useful to add an >> option to pgbench to introduce a delay between client connection setup >> and the start of the benchmark itself to allow me time to attach the >> profiler to one of the backends. > > "postgres -W n" already does this. It is more flexible to put this > functionality in the backend that in individual client apps anyway. I'm aware of postgres -W, but wanted something that wouldn't get in the way of other connections and would only affect my pgbench tests. If the patch is of no interest, please just ignore it. I just posted it for anyone that may find it useful - I'm not pushing to have it committed. Regards, Dave.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Mon, 10 Dec 2007 19:58:21 +0000
Dave Page <dpage@postgresql.org> wrote:
> Neil Conway wrote:
> > On Mon, 2007-12-10 at 19:27 +0000, Dave Page wrote:
> >> Whilst doing some profiling of the server I found it useful to add
> >> an option to pgbench to introduce a delay between client
> >> connection setup and the start of the benchmark itself to allow me
> >> time to attach the profiler to one of the backends.
> >
> > "postgres -W n" already does this. It is more flexible to put this
> > functionality in the backend that in individual client apps anyway.
>
> I'm aware of postgres -W, but wanted something that wouldn't get in
> the way of other connections and would only affect my pgbench tests.
>
> If the patch is of no interest, please just ignore it. I just posted
> it for anyone that may find it useful - I'm not pushing to have it
> committed.
I see use for it. Especially in a development environment where you may
have various things going on that you have no control over. It is
rude to send out a broadcast that says, "Yo... I need to restart
postgresql, please stop all productive tasks on your end because I am
more important."
+1 on enabling client side behavior.
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHXZtEATb/zqfZUUQRAiWoAJ0ULTUziKVDkuqXmUyvgYCSA0f+hwCgl/ay
SZjqJZIaGLxTBpbuKEzBc4Y=
=Ku+C
-----END PGP SIGNATURE-----
Dave Page wrote: > I'm aware of postgres -W, but wanted something that wouldn't get in the > way of other connections and would only affect my pgbench tests. I think you could get the same effect by putting the -W in PGOPTIONS (in pgbench's environment). -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "No renuncies a nada. No te aferres a nada."
Alvaro Herrera wrote: > Dave Page wrote: > >> I'm aware of postgres -W, but wanted something that wouldn't get in the >> way of other connections and would only affect my pgbench tests. > > I think you could get the same effect by putting the -W in PGOPTIONS (in > pgbench's environment). > That's a good point. It does have the downside that it will affect the pgbench results - though that wouldn't actually be an issue for what I was doing. /D
Dave Page <dpage@postgresql.org> writes:
> Alvaro Herrera wrote:
>> I think you could get the same effect by putting the -W in PGOPTIONS (in
>> pgbench's environment).
> That's a good point. It does have the downside that it will affect the
> pgbench results - though that wouldn't actually be an issue for what I
> was doing.
Well, if you're attaching a profiler or debugger to a backend, you're
hardly gonna get unadulterated TPS readings from pgbench anyway.
I concur with Alvaro that this case seems adequately covered by
PGOPTIONS="-W n" pgbench ...
which is what I've always used in similar situations...
regards, tom lane
On Mon, 10 Dec 2007, Tom Lane wrote: > I concur with Alvaro that this case seems adequately covered by > PGOPTIONS="-W n" pgbench ... I started to disagree with this, but ultimately realized anyone who is running pgbench for long enough to get useful results shouldn't have their TPS impacted much at all by a few overhead seconds tacked onto the server startup. I once wrote a similar patch to the one Dave submitted here and feel like it's worth committing at least a documentation patch to show how to deal with this. It's not obvious that pgbench pays attention to the environment variables at all, and it's even less obvious that you can pass what look like server options in there. I just poked around the documentation a bit and I didn't find anything that cleared up which options you can pass from a client; in addition to -W, I can imagine pgbench users might also want to use -S (sort memory) or -f (forbid scan/join types). If I can get someone to clarify what is supported there I can put together a pgbench doc patch that addresses this topic. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Mon, 2007-12-10 at 19:12 -0500, Greg Smith wrote: > I just poked around the > documentation a bit and I didn't find anything that cleared up which > options you can pass from a client http://www.postgresql.org/docs/8.2/static/libpq-envars.html Which says only "PGOPTIONS sets additional run-time options for the PostgreSQL server." This could probably be elaborated upon -- for the list of options accepted, see PostgresMain() in tcop/postgres.c Perhaps one of the slightly unfortunate consequences of the postmaster => postgres merge is that there is less of a clear distinction between "postmaster options" and "postgres" options... -Neil
Greg Smith <gsmith@gregsmith.com> writes: > I once wrote a similar patch to the one Dave submitted here and feel like > it's worth committing at least a documentation patch to show how to deal > with this. It's not obvious that pgbench pays attention to the > environment variables at all, and it's even less obvious that you can pass > what look like server options in there. It's not pgbench that is paying attention to this, it's libpq. This is at least referred to in the libpq and server documentation, eg the tenth paragraph here: http://developer.postgresql.org/pgdocs/postgres/config-setting.html It might be worth more emphasis, not sure. It doesn't come up all that often. > I just poked around the > documentation a bit and I didn't find anything that cleared up which > options you can pass from a client; in addition to -W, I can imagine > pgbench users might also want to use -S (sort memory) or -f (forbid > scan/join types). If I can get someone to clarify what is supported there > I can put together a pgbench doc patch that addresses this topic. Anything you'd be allowed to SET can be set from PGOPTIONS (-c or --var syntax). As for the special-purpose postgres command-line switches, I believe they are all equivalent to one or another GUC variable: http://developer.postgresql.org/pgdocs/postgres/runtime-config-short.html so the restrictions are the same as for the underlying variable. regards, tom lane
On Mon, 10 Dec 2007, Neil Conway wrote: > Perhaps one of the slightly unfortunate consequences of the postmaster > => postgres merge is that there is less of a clear distinction between > "postmaster options" and "postgres" options... I'd already read all of the documentation that you and Tom suggested just before I sent my previous message, and I didn't find this subject clear at all. On Mon, 10 Dec 2007, Tom Lane wrote: > It's not pgbench that is paying attention to this, it's libpq. Right, but I wouldn't expect a typical pgbench user to know that. > Anything you'd be allowed to SET can be set from PGOPTIONS (-c or --var > syntax...the restrictions are the same as for the underlying variable. That clarifies the situation well enough for me. I think this is a two part problem then. It's not necessarily obvious that pgbench will use PGOPTIONS. In addition to that, the current documentation is less clear than it could be on the subject of what you can usefully put into PGOPTIONS. That's two small documentation patches I should be able to handle. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith <gsmith@gregsmith.com> writes:
> That clarifies the situation well enough for me. I think this is a two
> part problem then. It's not necessarily obvious that pgbench will use
> PGOPTIONS. In addition to that, the current documentation is less clear
> than it could be on the subject of what you can usefully put into
> PGOPTIONS. That's two small documentation patches I should be able to
> handle.
BTW, PGOPTIONS is actually just the environment-variable fallback for
the pgoptions argument to PQsetdbLogin() or the options=whatever
component of the conninfo string for PQconnectdb() --- it's the same
sort of animal as PGHOST or PGPORT. So those provide alternate paths
for getting at the same functionality, and any documentation patch
should be clear about this.
regards, tom lane
Tom Lane wrote: > Dave Page <dpage@postgresql.org> writes: >> Alvaro Herrera wrote: >>> I think you could get the same effect by putting the -W in PGOPTIONS (in >>> pgbench's environment). > >> That's a good point. It does have the downside that it will affect the >> pgbench results - though that wouldn't actually be an issue for what I >> was doing. > > Well, if you're attaching a profiler or debugger to a backend, you're > hardly gonna get unadulterated TPS readings from pgbench anyway. No, but it can be a simple consistency check between multiple profiler runs - but then it doesn't matter if it's affected by delay of course as long as it's of a consistent length. One small advantage of doing this client-side (which I'm pretty sure noone can shoot down :-) ) is that the initial connection used to vacuum etc. isn't delayed which could be annoying. > I concur with Alvaro that this case seems adequately covered by > PGOPTIONS="-W n" pgbench ... > which is what I've always used in similar situations... Fair 'enuff :-) /D