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.

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(at)postgresql(dot)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(at)postgresql(dot)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(at)gregsmith(dot)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(at)gregsmith(dot)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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith(at)gregsmith(dot)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(at)postgresql(dot)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