pgbench throttling latency limit

Add --limit to limit latency under throttling

Under throttling, transactions are scheduled for execution at certain times.
Transactions may be far behind schedule and the system may catch up with the
load later. This option allows to change this behavior by skipping
transactions which are too far behind schedule, and count those as skipped.

The idea is to help simulate a latency-constrained environment such as a
database used by a web server.

Find attached a new version:
- fix dropped percent computation in the final report
- simplify progress report code

--
Fabien.

Hi Fabien,

On Sun, Aug 24, 2014 at 9:16 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Find attached a new version:
- fix dropped percent computation in the final report
- simplify progress report code

I have reviewed this patch.

Is the patch in a patch format which has context?
Yes.
Does it apply cleanly to the current git master?
Yes.
Does it include reasonable tests, necessary doc patches, etc?
Yes.

Does the patch actually implement that?
Yes.
Do we want that?
I think we do, yes.
Do we already have it?
No.
Are there dangers?
None that I can see.

Does the feature work as advertised?
Almost, see below.
Are there corner cases the author has failed to consider?
None that I can see.
Are there any assertion failures or crashes?
No.

I can't make the -L option work at all. If I do this:
./pgbench -R 100 -L 1
I get:
pgbench: invalid option -- L
Which appears to be caused by the fact that the call to getopt_long()
has not been updated to reflect the new parameter.

Also this part:
+          "  -L, --limit=NUM          under throttling (--rate), skip
transactions that\n"
+          "                           far behind schedule in ms
(default: do not skip)\n"
I would suggest rewording this to something like "skip transactions
that are more than NUM milliseconds behind schedule (default: do not
skip)".

Marking Waiting for Author until these small issues have been fixed.

Thanks,

♜

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Rukh,

I have reviewed this patch.

Thanks!

[...] I get: pgbench: invalid option -- L
Which appears to be caused by the fact that the call to getopt_long()
has not been updated to reflect the new parameter.

Indeed, I only tested/used it with the --limit= syntax.

Also this part:
+          "  -L, --limit=NUM          under throttling (--rate), skip
transactions that\n"
+          "                           far behind schedule in ms
(default: do not skip)\n"
I would suggest rewording this to something like "skip transactions
that are more than NUM milliseconds behind schedule (default: do not
skip)".

Done, with milliseconds written as "ms" to keep it short.

Marking Waiting for Author until these small issues have been fixed.

Please find attached a new version which fixes these two points.

--
Fabien.

Marking Waiting for Author until these small issues have been fixed.

I've put it back to "Needs review". Feel free to set it to "Ready" if it
is ok for you.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi Fabien,

On Tue, Aug 26, 2014 at 04:07 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Please find attached a new version which fixes these two points.

Indeed it does. Marking the patch ready for a committer.

Thanks,

♜

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 08/27/2014 03:47 AM, Rukh Meski wrote:

Hi Fabien,

On Tue, Aug 26, 2014 at 04:07 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Please find attached a new version which fixes these two points.

Indeed it does. Marking the patch ready for a committer.

I find the definition of the latency limit a bit strange. It's a limit
on how late a transaction can *start* compared to it's scheduled
starting time, not how long a query is allowed to last. How do figure
out what it should be set to?

That model might make some sense if you think e.g. of a web application,
where the web server has a timeout for how long it waits to get a
database connection from a pool, but once a query is started, the
transaction is considered a succeess no matter how long it takes. The
latency limit would be that timeout. But I think a more useful model is
that when the user clicks a button, he waits at most X seconds for the
result. If that deadline is exceeded, the web server will give a 404, or
the user will simply get bored and go away, and the transaction is
considered a failure.

So I think a more useful model is that new queries arrive at a given
rate, and each query is expected to finish in X milliseconds from its
arrival time (i.e the time the query is scheduled to begin, not the time
it was sent to the server) or it's counted as failed. If a transaction
cannot even be started by that deadline, because the connection is still
busy with the previous query, it's counted as failed without even
sending it to the server.

With that definition, it makes sense to specify the latency limit even
without --rate. In that case, it's simply a limit on how long each
query's execution is allowed to last until it's considered as failed.
IOW, each query's scheduled start time is when the previous query ends.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Heikki,

I find the definition of the latency limit a bit strange. It's a limit on how
late a transaction can *start* compared to it's scheduled starting time, not
how long a query is allowed to last.

Yes. This is what can be done easily with pgbench under throttling. Note
that if transactions take long it is recorded (average, stddev...) so it
appears elsewhere.

How do figure out what it should be set to?

It is really just a simple tool to measure unresponsiveness under
throttling, which I'm testing and complaining about in another thread.

The underlying model I have in mind would some timeout from an
application, say a web server, or a pooling process which is handling a
queue of requests...

Now, if I describe that as "lag limit" instead if "latency limit", maybe
it is clearer and better?

That model might make some sense if you think e.g. of a web application,
[...]

Yep, that is what I had in mind, but the primary objective is really to
check whether pg is responsive or not.

So I think a more useful model is that new queries arrive at a given
rate, and each query is expected to finish in X milliseconds from its
arrival time (i.e the time the query is scheduled to begin, not the time
it was sent to the server) or it's counted as failed. If a transaction
cannot even be started by that deadline, because the connection is still
busy with the previous query, it's counted as failed without even
sending it to the server. With that definition, it makes sense to
specify the latency limit even without --rate.

Yep. But that is not what I'm doing here. It would be interesting as well.
It would be another patch.

In that case, it's simply a limit on how long each query's
execution is allowed to last until it's considered as failed. IOW, each
query's scheduled start time is when the previous query ends.

Not under --rate... that is the point of throttling! Under throttling,
the latency should really be computed wrt to the schedule start time and
not the actual start time which may be 10 seconds afterwards when things
are going bad... Also, there is the question of whether the "failed query"
is executed or not. Here I'm not executing them, in effect they were
aborted by the application. With your suggestion they would be executed
anyway but considered failed.

So what you are suggesting is another (interesting) functionnality, that
could indeed be named "latency limit" (count slow above a threshold
queries), what I'm doing here is "lag limit" (scheduled query could not
start on time and are skipped, this is really specific to --rate).

In the updated patch attached, I changed the explanations, documentation
and name to "lag limit" instead of "latency limit" to clarify this point.
It was really a misnommer.

--
Fabien.

On 08/27/2014 12:41 PM, Fabien COELHO wrote:

Hello Heikki,

I find the definition of the latency limit a bit strange. It's a limit on how
late a transaction can *start* compared to it's scheduled starting time, not
how long a query is allowed to last.

Yes. This is what can be done easily with pgbench under throttling. Note
that if transactions take long it is recorded (average, stddev...) so it
appears elsewhere.

How do figure out what it should be set to?

It is really just a simple tool to measure unresponsiveness under
throttling, which I'm testing and complaining about in another thread.

Ok, but wouldn't the definition I gave be just as useful for that
purpose, and more useful in general?

You didn't really answer my question: How do you figure out what to set
it to? With a latency limit on when the query should finish, as opposed
to how late it can start, it's a lot easier to give a number. For
example, your requirements might state that a user must always get a
response to a click on a web page in 200 ms, so you set the limit to 200 ms.

So I think a more useful model is that new queries arrive at a given
rate, and each query is expected to finish in X milliseconds from its
arrival time (i.e the time the query is scheduled to begin, not the time
it was sent to the server) or it's counted as failed. If a transaction
cannot even be started by that deadline, because the connection is still
busy with the previous query, it's counted as failed without even
sending it to the server. With that definition, it makes sense to
specify the latency limit even without --rate.

Yep. But that is not what I'm doing here. It would be interesting as well.
It would be another patch.

Why is your patch more interesting than what I described? I'm pretty
sure we don't need both.

In that case, it's simply a limit on how long each query's
execution is allowed to last until it's considered as failed. IOW, each
query's scheduled start time is when the previous query ends.

Not under --rate... that is the point of throttling!

Right, I got that. With "in that case", I meant when you're not throttling.

Under throttling,
the latency should really be computed wrt to the schedule start time and
not the actual start time which may be 10 seconds afterwards when things
are going bad... Also, there is the question of whether the "failed query"
is executed or not. Here I'm not executing them, in effect they were
aborted by the application. With your suggestion they would be executed
anyway but considered failed.

I was thinking that if a query is already late when the connection
becomes free to execute it, it would not be executed. It would be
skipped, just as in your patch.

So what you are suggesting is another (interesting) functionnality, that
could indeed be named "latency limit" (count slow above a threshold
queries), what I'm doing here is "lag limit" (scheduled query could not
start on time and are skipped, this is really specific to --rate).

Ok, but *why* are you doing a "lag limit", and not a "latency limit"?
Under what circumstances is the lag limit a more useful setting?

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Heikki,

[...]
With a latency limit on when the query should finish, as opposed to how
late it can start, it's a lot easier to give a number. For example, your
requirements might state that a user must always get a response to a click on
a web page in 200 ms, so you set the limit to 200 ms.

Yep. See below for the details.

[...] Why is your patch more interesting than what I described?

It is more interesting because it exists, it is short and simple, it
works, and it is useful right now to test pg responsiveness and also to
model some timeout behavior on the client side?

I'm pretty sure we don't need both.

Why not? Testing performance is tricky enough, the tool must be flexible.

I'm pretty sure that I'm interested in testing pg responsiveness right
now, so I did the simpler one I need for that purpose. It somehow models
an application/pooler queue management timeout, that would anyway proceed
with what is already started.

[...]

I was thinking that if a query is already late when the connection becomes
free to execute it, it would not be executed. It would be skipped, just as in
your patch.

As for an actual "latency limit" under throttling, this is significantly
more tricky and invasive to implement... ISTM that it would mean:

- if the tx is not stated an the latency is already consummed, SKIP++.

- if the tx is after its schedule start time but under latency, then
start it, and maybe inject a "SET TIMEOUT...".

- if a tx is being processed but reaches its latency limit (after
schedule start time), abort it coldly, ROLLBACK++ (well if the tx is
really started, there could also be shell commands and \set stuff in a
pgbench script, which mean started is not really started, so it would
be INTERRUPT++ if no BEGIN was sent).

- if a tx is finished but the final commit returned after the latency
deadline, you cannot abort it anymore but it is late nevertheless,
LATE++.

This is doable but far beyond my current needs. Moreover, I'm not sure
that such a patch would pass because of invasiveness and complexity, so it
could be a total loss of time.

Ok, but *why* are you doing a "lag limit", and not a "latency limit"?

Because it is much simpler (see above) and is enough for testing pg
responsiveness issue, which is my current objective, and models some
client timeout behavior.

Under what circumstances is the lag limit a more useful setting?

It is not "more" useful" per se, it is what I'm using to test pg
unresponsivness with a simple to define and interpret measure wrt
throttling.

If I would do "latency limit" under throttling, it would be (1) more time
to develop, more complex, more invasive in the code (see above, + also the
implementation when not under throttling), (2) more complex to interpret,
with at least 5 possible outcomes (skipped, interrupted, committed on
time, committed but late, aborted), (3) this added information would not
be useful to me.

I've submitted this "simple" lag limit version because being able to
measure quickly and simply (un)responsiveness seems like a good idea,
especially given the current state of things.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 08/27/2014 02:37 PM, Fabien COELHO wrote:

As for an actual "latency limit" under throttling, this is significantly
more tricky and invasive to implement... ISTM that it would mean:

- if the tx is not stated an the latency is already consummed, SKIP++.

- if the tx is after its schedule start time but under latency, then
start it, and maybe inject a "SET TIMEOUT...".

- if a tx is being processed but reaches its latency limit (after
schedule start time), abort it coldly, ROLLBACK++ (well if the tx is
really started, there could also be shell commands and \set stuff in a
pgbench script, which mean started is not really started, so it would
be INTERRUPT++ if no BEGIN was sent).

- if a tx is finished but the final commit returned after the latency
deadline, you cannot abort it anymore but it is late nevertheless,
LATE++.

Yeah, something like that. I don't think it would be necessary to set
statement_timeout, you can inject that in your script or postgresql.conf
if you want. I don't think aborting a transaction that's already started
is necessary either. You could count it as LATE, but let it finish first.

This is doable but far beyond my current needs. Moreover, I'm not sure
that such a patch would pass because of invasiveness and complexity, so it
could be a total loss of time.

Ok, but *why* are you doing a "lag limit", and not a "latency limit"?

Because it is much simpler (see above) and is enough for testing pg
responsiveness issue, which is my current objective, and models some
client timeout behavior.

Under what circumstances is the lag limit a more useful setting?

It is not "more" useful" per se, it is what I'm using to test pg
unresponsivness with a simple to define and interpret measure wrt
throttling.

If I would do "latency limit" under throttling, it would be (1) more time
to develop, more complex, more invasive in the code (see above, + also the
implementation when not under throttling), (2) more complex to interpret,
with at least 5 possible outcomes (skipped, interrupted, committed on
time, committed but late, aborted), (3) this added information would not
be useful to me.

I've submitted this "simple" lag limit version because being able to
measure quickly and simply (un)responsiveness seems like a good idea,
especially given the current state of things.

Ok, fair enough. I don't think doing a "latency limit" would be
significantly harder, but I can't force you. I'll mark this as Returned
with Feedback then.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

As for an actual "latency limit" under throttling, this is significantly
more tricky and invasive to implement... ISTM that it would mean:
[...]

Yeah, something like that. I don't think it would be necessary to set
statement_timeout, you can inject that in your script or postgresql.conf if
you want. I don't think aborting a transaction that's already started is
necessary either. You could count it as LATE, but let it finish first.

If you remove all difficult cases from the spec, it is obviously much
simpler to implement:-) It seems that your simplified version of "latency
limit" would be just to distinguish LATE from ONTIME among the committed
ones, compared to the current version, and not to actually limit the
latency, which is the tricky part.

I've submitted this "simple" lag limit version because being able to
measure quickly and simply (un)responsiveness seems like a good idea,
especially given the current state of things.

Ok, fair enough. I don't think doing a "latency limit" would be significantly
harder, but I can't force you. I'll mark this as Returned with Feedback then.

Hmmm. I can distinguish just the two cases. Rather mark it as "waiting on
author", I may give it a go.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 08/27/2014 06:08 PM, Fabien COELHO wrote:

I've submitted this "simple" lag limit version because being able to
measure quickly and simply (un)responsiveness seems like a good idea,
especially given the current state of things.

Ok, fair enough. I don't think doing a "latency limit" would be significantly
harder, but I can't force you. I'll mark this as Returned with Feedback then.

Hmmm. I can distinguish just the two cases. Rather mark it as "waiting on
author", I may give it a go.

Feel free to mark it as such if you think you can get a new version
posted in the next few days.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[...]

Yeah, something like that. I don't think it would be necessary to set
statement_timeout, you can inject that in your script or postgresql.conf if
you want. I don't think aborting a transaction that's already started is
necessary either. You could count it as LATE, but let it finish first.

I've implemented something along these simplified lines. The latency is
not limited as such, but slow (over the limit) queries are counted and
reported.

--
Fabien.

On 08/27/2014 08:05 PM, Fabien COELHO wrote:

[...]

Yeah, something like that. I don't think it would be necessary to set
statement_timeout, you can inject that in your script or postgresql.conf if
you want. I don't think aborting a transaction that's already started is
necessary either. You could count it as LATE, but let it finish first.

I've implemented something along these simplified lines. The latency is
not limited as such, but slow (over the limit) queries are counted and
reported.

Ok, thanks.

This now begs the question:

In --rate mode, shouldn't the reported transaction latency also be
calculated from the *scheduled* start time, not the time the transaction
actually started? Otherwise we're using two different definitions of
"latency", one for the purpose of the limit, and another for reporting.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Heikki,

This now begs the question:

In --rate mode, shouldn't the reported transaction latency also be calculated
from the *scheduled* start time, not the time the transaction actually
started? Otherwise we're using two different definitions of "latency", one
for the purpose of the limit, and another for reporting.

It could. Find a small patch **on top of v5** which does that. I've tried
to update the documentation accordingly as well.

Note that the information is already there as the average lag time is
reported, ISTM that:

avg latency2 ~ avg lag + avg latency1

so it is just a matter of choice, both are ok somehow. I would be fine
with both.

--
Fabien.

Hello Heikki,

[...] I would be fine with both.

After giving it some thought, ISTM better to choose consistency over
intuition, and have latency under throttling always defined wrt the
scheduled start time and not the actual start time, even if having a
latency of 10000 ms for an OLTP load might seem surprising to some.

The other one can be computed by substracting the average lag time.

I attached a v6 which is a consolidate patch of v5 + the small update for
the latency definition.

--
Fabien.

Hi,

I generally want to say that having a feature like this feels *very*
helpful to me. Lots of pg development hasn't really paid attention to
anything but the final pgbench results...

On 2014-08-29 19:48:43 +0200, Fabien COELHO wrote:

+	if (latency_limit)
+		printf("number of transactions above the %.1f ms latency limit: " INT64_FORMAT "\n",
+			   latency_limit / 1000.0, latency_late);
+

Any reason not to report a percentage here?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

+	if (latency_limit)
+		printf("number of transactions above the %.1f ms latency limit: " INT64_FORMAT "\n",
+			   latency_limit / 1000.0, latency_late);
+

Any reason not to report a percentage here?

Yes: I did not thought of it.

Here is a v7, with a percent. I also added a paragraph in the documenation
about how the latency is computed under throttling, and I tried to reorder
the reported stuff so that it is more logical.

--
Fabien.

On 08/27/2014 04:08 AM, Heikki Linnakangas wrote:

That model might make some sense if you think e.g. of a web application,
where the web server has a timeout for how long it waits to get a
database connection from a pool, but once a query is started, the
transaction is considered a succeess no matter how long it takes. The
latency limit would be that timeout. But I think a more useful model is
that when the user clicks a button, he waits at most X seconds for the
result. If that deadline is exceeded, the web server will give a 404, or
the user will simply get bored and go away, and the transaction is
considered a failure.

Correct, the whole TPC-B model better fits an application where client
requests enter a queue at the specified TPS rate and that queue is
processed.

While we are at it,

Note that in the original TPC-B specification the transaction duration
measured is the time from receiving the client request (in current
pgbench under throttling that is for when the transaction is scheduled)
and when the request is answered. This is the client visible response
time, which has nothing to do with the database latency.

As per TPC-B, the entire test is only valid if 90% of all client
response times are within 2 seconds.

It would be useful if pgbench would

A) measure and report that client response time in the per transaction
log files and

B) report at the end what percentage of transactions finished within
a specified response time constraint (default 2 seconds).

Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers