Re: usleep feature for pgbench

To test some changes in Slony I needed a

\usleep [microseconds|:variable]

in pgbench's scripting language to be able to have hundreds of
concurrent running transactions without totally swamping the system. I
was wondering if anyone would object to permanently adding this to the
pgbench code?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> To test some changes in Slony I needed a
> \usleep [microseconds|:variable]
> in pgbench's scripting language to be able to have hundreds of
> concurrent running transactions without totally swamping the system. I
> was wondering if anyone would object to permanently adding this to the
> pgbench code?

How's it implemented? Does the whole pgbench app freeze up for the
duration of the sleep, or does it just delay that one client script?

regards, tom lane

On 7/5/2007 3:34 PM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>> To test some changes in Slony I needed a
>> \usleep [microseconds|:variable]
>> in pgbench's scripting language to be able to have hundreds of
>> concurrent running transactions without totally swamping the system. I
>> was wondering if anyone would object to permanently adding this to the
>> pgbench code?
>
> How's it implemented? Does the whole pgbench app freeze up for the
> duration of the sleep, or does it just delay that one client script?

Only that one client script.

The \usleep command itself calculates a "struct timeval until" that sits
in the state struct and sets a "sleeping" flag also in the state struct.
So both are per client script/connection and multiple clients can be
sleeping in an overlapping fashion. The loop in main() then picks the
lowest timeval (if there is anyone sleeping) and uses that to calculate
the timeout for select(). doCustom() resets the sleeping flag for those
scheduled for wakeup and advances the client to the next scripted command.

I think I've coded it in a way that if one doesn't use the \usleep
command at all, it will never even call gettimeofday() and use a NULL
timeout in select() as it used to.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> I think I've coded it in a way that if one doesn't use the \usleep
> command at all, it will never even call gettimeofday() and use a NULL
> timeout in select() as it used to.

Did you check that the observed performance for non-usleep-using scripts
didn't change? If this extra overhead causes a reduction in reported
TPS rates it would make it hard to compare older and newer tests.

Other than that I've got no objection to it.

regards, tom lane

Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> > I think I've coded it in a way that if one doesn't use the \usleep
> > command at all, it will never even call gettimeofday() and use a NULL
> > timeout in select() as it used to.
>
> Did you check that the observed performance for non-usleep-using scripts
> didn't change? If this extra overhead causes a reduction in reported
> TPS rates it would make it hard to compare older and newer tests.

I keep wondering, why is that important? If you want to compare results
of different PG versions, surely you can run the tests with the newest
version of pgbench?

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Those who use electric razors are infidels destined to burn in hell while
we drink from rivers of beer, download free vids and mingle with naked
well shaved babes." (http://slashdot.org/comments.pl?sid=44793&cid=4647152)

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> Did you check that the observed performance for non-usleep-using scripts
>> didn't change? If this extra overhead causes a reduction in reported
>> TPS rates it would make it hard to compare older and newer tests.

> I keep wondering, why is that important? If you want to compare results
> of different PG versions, surely you can run the tests with the newest
> version of pgbench?

It's more about not having to repeat old test cases every time you want
to have numbers comparable to your latest case.

regards, tom lane

On 7/5/2007 5:30 PM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>> I think I've coded it in a way that if one doesn't use the \usleep
>> command at all, it will never even call gettimeofday() and use a NULL
>> timeout in select() as it used to.
>
> Did you check that the observed performance for non-usleep-using scripts
> didn't change? If this extra overhead causes a reduction in reported
> TPS rates it would make it hard to compare older and newer tests.

Given pgbench's unpredictability of results ... lets see.

I ran

dropdb x
createdb x
pgbench -i -s10 x
psql -c 'checkpoint' x
sleep 1
psql -c 'checkpoint' x
pgbench -s10 -c5 -t10000 x
pgbench -s10 -c5 -t10000 x
pgbench -s10 -c5 -t10000 x

Original pgbench reported 39, 37 and 33 TPS. Having my patch applied it
reported 40, 38 and 33 TPS. Inserting a "\usleep 1" after the update to
accounts of a default equivalent script changed those numbers to 40, 37
and 33. I interpret that as "does not change observed performance".

>
> Other than that I've got no objection to it.

Will be committed after adjusting the README.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

On Thu, 5 Jul 2007, Jan Wieck wrote:

> Original pgbench reported 39, 37 and 33 TPS. Having my patch applied it
> reported 40, 38 and 33 TPS. Inserting a "\usleep 1" after the update to
> accounts of a default equivalent script changed those numbers to 40, 37 and
> 33. I interpret that as "does not change observed performance".

Tell you what: put your work into a patch, send it to the list, and I'll
test that it doesn't degrade results for you. If your pgbench results are
in the <40 TPS range even with that low of a scale, you're not in a
position to tell whether it has a negative performance impact. That
select statement you're fiddling with can turn into a bottleneck at high
client loads, and from your description I can't tell if you've made that
worse, but you'll never see it unless you're pushing, say,
>1000 TPS and >50 clients. Also: 3 pgbench results at one client load
is quite a bit short of proving no impact on performance; I'll queue up 50
or so, which is where I start to trust results from that unruly tool.

This is actually a feature I'd be kind of interested to have, because it
would allow you to pass two (or more) script files to pgbench and adjust
the transaction mix. What happens when you do that right now is that
inevitably all the clients get blocked at once on whatever the hardest to
execute transaction is, and the results are kind of deceptive as a result.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Am Donnerstag, 5. Juli 2007 21:12 schrieb Jan Wieck:
> To test some changes in Slony I needed a
>
> \usleep [microseconds|:variable]
>
> in pgbench's scripting language to be able to have hundreds of
> concurrent running transactions without totally swamping the system. I
> was wondering if anyone would object to permanently adding this to the
> pgbench code?

Or maybe a \sleep command that takes units, if it's not too much work.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 7/6/2007 10:44 AM, Peter Eisentraut wrote:
> Am Donnerstag, 5. Juli 2007 21:12 schrieb Jan Wieck:
>> To test some changes in Slony I needed a
>>
>> \usleep [microseconds|:variable]
>>
>> in pgbench's scripting language to be able to have hundreds of
>> concurrent running transactions without totally swamping the system. I
>> was wondering if anyone would object to permanently adding this to the
>> pgbench code?
>
> Or maybe a \sleep command that takes units, if it's not too much work.
>

You mean as a second, optional argument? Good idea.

us = microseconds
ms = milliseconds
s = seconds (default)

\sleep {value|:variable} [us|ms|s]

Is that okay with everyone?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck wrote:
> On 7/6/2007 10:44 AM, Peter Eisentraut wrote:
>> Am Donnerstag, 5. Juli 2007 21:12 schrieb Jan Wieck:
>>> To test some changes in Slony I needed a
>>>
>>> \usleep [microseconds|:variable]
>>>
>>> in pgbench's scripting language to be able to have hundreds of
>>> concurrent running transactions without totally swamping the system. I
>>> was wondering if anyone would object to permanently adding this to the
>>> pgbench code?
>>
>> Or maybe a \sleep command that takes units, if it's not too much work.
>>
>
> You mean as a second, optional argument? Good idea.
>
> us = microseconds
> ms = milliseconds
> s = seconds (default)
>
> \sleep {value|:variable} [us|ms|s]
>
> Is that okay with everyone?

I won't object, but is it really worth the trouble?

Can you do microsecond precision sleeps, and on what platforms? How much
overhead is there? IIRC, on Linux the minimum time you can sleep depends
on CONFIG_HZ, and the default was 10 ms until recently.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On 7/6/2007 1:32 PM, Heikki Linnakangas wrote:
> Jan Wieck wrote:
>> On 7/6/2007 10:44 AM, Peter Eisentraut wrote:
>>> Am Donnerstag, 5. Juli 2007 21:12 schrieb Jan Wieck:
>>>> To test some changes in Slony I needed a
>>>>
>>>> \usleep [microseconds|:variable]
>>>>
>>>> in pgbench's scripting language to be able to have hundreds of
>>>> concurrent running transactions without totally swamping the system. I
>>>> was wondering if anyone would object to permanently adding this to the
>>>> pgbench code?
>>>
>>> Or maybe a \sleep command that takes units, if it's not too much work.
>>>
>>
>> You mean as a second, optional argument? Good idea.
>>
>> us = microseconds
>> ms = milliseconds
>> s = seconds (default)
>>
>> \sleep {value|:variable} [us|ms|s]
>>
>> Is that okay with everyone?
>
> I won't object, but is it really worth the trouble?
>
> Can you do microsecond precision sleeps, and on what platforms? How much
> overhead is there? IIRC, on Linux the minimum time you can sleep depends
> on CONFIG_HZ, and the default was 10 ms until recently.
>

It is what you tell select(2) in the struct timeval. Why limit it to
some arbitrary precision?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> Jan Wieck wrote:
>> You mean as a second, optional argument? Good idea.
>>
>> us = microseconds
>> ms = milliseconds
>> s = seconds (default)
>>
>> \sleep {value|:variable} [us|ms|s]
>>
>> Is that okay with everyone?

> I won't object, but is it really worth the trouble?

If we don't get it right now, we'll be stuck with the API later.
+1 for the definition suggested above.

regards, tom lane

Jan Wieck wrote:
> You mean as a second, optional argument? Good idea.

I think it should only be one argument, with the units being part of the
variable value, if any.

> us = microseconds
> ms = milliseconds
> s = seconds (default)
>
> \sleep {value|:variable} [us|ms|s]
--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Jan Wieck wrote:
>> You mean as a second, optional argument? Good idea.

> I think it should only be one argument, with the units being part of the
> variable value, if any.

No, because that makes it hard to do things like "sleep for a small
random number of microseconds". If pgbench had string concatenation
ability in its metalanguage then what you suggest would be sensible,
but let us not go there.

regards, tom lane

On 7/7/2007 11:40 AM, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Jan Wieck wrote:
>>> You mean as a second, optional argument? Good idea.
>
>> I think it should only be one argument, with the units being part of the
>> variable value, if any.
>
> No, because that makes it hard to do things like "sleep for a small
> random number of microseconds". If pgbench had string concatenation
> ability in its metalanguage then what you suggest would be sensible,
> but let us not go there.

That is exactly the reason why I made it an optional argument. The usage
example in the README file shows how to actually do a random sleep
between 1000 and 2500 ms.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #