Re: 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.

--
Fabien.

> 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(at)cri(dot)ensmp(dot)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,

♜

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.

Hi Fabien,

On Tue, Aug 26, 2014 at 04:07 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>
> Please find attached a new version which fixes these two points.

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

Thanks,

♜

On 08/27/2014 03:47 AM, Rukh Meski wrote:
> Hi Fabien,
>
> On Tue, Aug 26, 2014 at 04:07 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)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

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

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.

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

>> 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.

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

>> [...]
>
> 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

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

>> + 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

>> 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.

Ok. This correspond to the definition used in the current patch. However
ISTM that the tpc-b bench is "as fast as possible", there is no underlying
schedule as with the throttled pgbench.

> 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

I never used the per transaction log file. I think that it may already be
the case that it contains this information when not throttled. When under
throttling, I do not know.

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

What is currently reported is the complement (% of transactions completed
over the time limit).

Note that despite pg appaling latency performance, in may stay well over
the 90% limit, or even 100%: when things are going well a lot of
transaction run in about ms, while when things are going bad transactions
would take a long time (although possibly under or about 1s anyway), *but*
very few transactions are passed, the throughput is very small. The fact
that during 15 seconds only 30 transactions are processed is a detail that
does not show up in the metric.

--
Fabien.

On 09/05/2014 10:12 AM, Fabien COELHO wrote:
> Note that despite pg appaling latency performance, in may stay well over
> the 90% limit, or even 100%: when things are going well a lot of
> transaction run in about ms, while when things are going bad transactions
> would take a long time (although possibly under or about 1s anyway), *but*
> very few transactions are passed, the throughput is very small. The fact
> that during 15 seconds only 30 transactions are processed is a detail that
> does not show up in the metric.

I haven't used the real pgbench for a long time. I will have to look at
your patch and see what the current version actually does or does not.

What I have been using is a Python version of pgbench that I wrote for
myself when I started learning that language. That one does record both
values, the DB transaction latency and the client response time (time
from the request being entered into the Queue until transaction commit).
When I look at those results it is possible to have an utterly failing
run, with <60% of client response times being within 2 seconds, but all
the DB transactions are still in milliseconds.

As said, I'll have to take a look at it. Since I am on vacation next
week, getting ready for my first day at EnterpriseDB, this may actually
happen.

Jan

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

On 09/05/2014 06:38 PM, Jan Wieck wrote:
> On 09/05/2014 10:12 AM, Fabien COELHO wrote:
>> Note that despite pg appaling latency performance, in may stay well over
>> the 90% limit, or even 100%: when things are going well a lot of
>> transaction run in about ms, while when things are going bad transactions
>> would take a long time (although possibly under or about 1s anyway), *but*
>> very few transactions are passed, the throughput is very small. The fact
>> that during 15 seconds only 30 transactions are processed is a detail that
>> does not show up in the metric.

Yeah, it makes much more sense to measure the latency from the
"scheduled" time than the actual time.

> I haven't used the real pgbench for a long time. I will have to look at
> your patch and see what the current version actually does or does not.
>
> What I have been using is a Python version of pgbench that I wrote for
> myself when I started learning that language. That one does record both
> values, the DB transaction latency and the client response time (time
> from the request being entered into the Queue until transaction commit).
> When I look at those results it is possible to have an utterly failing
> run, with <60% of client response times being within 2 seconds, but all
> the DB transactions are still in milliseconds.

I think we have to reconsider what we're reporting in 9.4, when --rate
is enabled, even though it's already very late in the release cycle.
It's a bad idea to change the definition of latency between 9.4 and 9.5,
so let's get it right in 9.4.

> As said, I'll have to take a look at it. Since I am on vacation next
> week, getting ready for my first day at EnterpriseDB, this may actually
> happen.

Oh, congrats! :-)

- Heikki

On 09/09/2014 01:49 PM, Heikki Linnakangas wrote:
> I think we have to reconsider what we're reporting in 9.4, when --rate
> is enabled, even though it's already very late in the release cycle.
> It's a bad idea to change the definition of latency between 9.4 and 9.5,
> so let's get it right in 9.4.

As per the attached patch. I think we should commit this to 9.4. Any
objections?

The text this patch adds to the documentation needs some rewording,
though. As does this existing paragraph:

> High rate limit schedule lag values, that is lag values that are
> large compared to the actual transaction latency, indicate that
> something is amiss in the throttling process. High schedule lag can
> highlight a subtle problem there even if the target rate limit is met
> in the end. One possible cause of schedule lag is insufficient
> pgbench threads to handle all of the clients. To improve that,
> consider reducing the number of clients, increasing the number of
> threads in pgbench, or running pgbench on a separate host. Another
> possibility is that the database is not keeping up with the load at
> some point. When that happens, you will have to reduce the expected
> transaction rate to lower schedule lag.

It took me ages to parse "high rate limit schedule lag values".

- Heikki

Hello Heikki,

>> I think we have to reconsider what we're reporting in 9.4, when --rate
>> is enabled, even though it's already very late in the release cycle.
>> It's a bad idea to change the definition of latency between 9.4 and 9.5,
>> so let's get it right in 9.4.

Indeed.

> As per the attached patch. I think we should commit this to 9.4. Any
> objections?

Ok for me. Some more propositions about the doc below.

> The text this patch adds to the documentation needs some rewording, though.

Probably. Not sure how to improve.

> As does this existing paragraph:
>
>> High rate limit schedule lag values, that is lag values that are
>> large compared to the actual transaction latency, indicate that
>> something is amiss in the throttling process. High schedule lag can
>> highlight a subtle problem there even if the target rate limit is met
>> in the end.

>> One possible cause of schedule lag is insufficient
>> pgbench threads to handle all of the clients. To improve that,
>> consider reducing the number of clients, increasing the number of
>> threads in pgbench, or running pgbench on a separate host. Another
>> possibility is that the database is not keeping up with the load at
>> some point. When that happens, you will have to reduce the expected
>> transaction rate to lower schedule lag.
>
> It took me ages to parse "high rate limit schedule lag values".

Indeed, I'm not proud of that one... Moreover the first sentence becomes
false with the new latency computation, as the lag time is included.

I would suggest:

"When under throttling, the reported lag time measures the delay between
the scheduled start time for the transaction and its actual start time. A
high value, where the lag time represent most of the transaction latency,
may indicate that something is amiss in the throttling process itself,
even if the target rate is met in the end. One possible cause ..."

--
Fabien.

On 09/09/2014 03:35 PM, Fabien COELHO wrote:
>
> Hello Heikki,
>
>>> I think we have to reconsider what we're reporting in 9.4, when --rate
>>> is enabled, even though it's already very late in the release cycle.
>>> It's a bad idea to change the definition of latency between 9.4 and 9.5,
>>> so let's get it right in 9.4.
>
> Indeed.
>
>> As per the attached patch. I think we should commit this to 9.4. Any
>> objections?
>
> Ok for me. Some more propositions about the doc below.

I looked closer at the this, and per Jan's comments, realized that we
don't log the lag time in the per-transaction log file. I think that's a
serious omission; when --rate is used, the schedule lag time is
important information to make sense of the result. I think we have to
apply the attached patch, and backpatch to 9.4. (The documentation on
the log file format needs updating)

Also, this is bizarre:

> /*
> * Use inverse transform sampling to randomly generate a delay, such
> * that the series of delays will approximate a Poisson distribution
> * centered on the throttle_delay time.
> *
> * 10000 implies a 9.2 (-log(1/10000)) to 0.0 (log 1) delay
> * multiplier, and results in a 0.055 % target underestimation bias:
> *
> * SELECT 1.0/AVG(-LN(i/10000.0)) FROM generate_series(1,10000) AS i;
> * = 1.000552717032611116335474
> *
> * If transactions are too slow or a given wait is shorter than a
> * transaction, the next transaction will start right away.
> */
> int64 wait = (int64) (throttle_delay *
> 1.00055271703 * -log(getrand(thread, 1, 10000) / 10000.0));

We're using getrand() to generate a uniformly distributed random value
between 1 and 10000, and then convert it to a double between 0.0 and
1.0. But getrand() is implemented by taking a double between 0.0 and 1.0
and converting it an integer, so we're just truncating the original
floating point number unnecessarily. I think we should add a new
function, getPoissonRand(), that uses pg_erand48() directly. We already
have similiar getGaussianRand() and getExponentialRand() functions.
Barring objections, I'll prepare another patch to do that, and backpatch
to 9.4.
- Heikki

Hi,

I find typo in your patch. Please confirm.

@line 239
- agg->sum2_lag = 0;
+ agg->sum_lag = 0;

And back patch is welcome for me.

Best Regards,
--
Mitsumasa KONDO

Hello Heikki,

> I looked closer at the this, and per Jan's comments, realized that we don't
> log the lag time in the per-transaction log file. I think that's a serious
> omission; when --rate is used, the schedule lag time is important information
> to make sense of the result. I think we have to apply the attached patch, and
> backpatch to 9.4. (The documentation on the log file format needs updating)

Indeed. I think that people do not like it to change. I remember that I
suggested to change timestamps to "xxxx.yyyyyy" instead of the unreadable
"xxxx yyy", and be told not to, because some people have tool which
process the output so the format MUST NOT CHANGE. So my behavior is not to
avoid touching anything in this area.

I'm fine if you do it, though:-) However I have not time to have a precise
look at your patch to cross-check it before Friday.

> Also, this is bizarre:
>
>> int64 wait = (int64) (throttle_delay *
>> 1.00055271703 * -log(getrand(thread, 1, 10000) / 10000.0));
>
> We're using getrand() to generate a uniformly distributed random value
> between 1 and 10000, and then convert it to a double between 0.0 and 1.0.

The reason for this conversion is to have randomness but to still avoid
going to extreme multiplier values. The idea is to avoid a very large
multiplier which would generate (even if it is not often) a 0 tps when 100
tps is required. The 10000 granularity is basically random but the
multiplier stays bounded (max 9.2, so the minimum possible tps would be
around 11 for a target of 100 tps, bar issues from the database for
processing the transactions).

So although this feature can be discussed and amended, it is fully
voluntary. I think that it make sense so I would prefer to keep it as is.
Maybe the comments could be update to be clearer.

--
Fabien.

On 09/10/2014 05:57 PM, Fabien COELHO wrote:
>
> Hello Heikki,
>
>> I looked closer at the this, and per Jan's comments, realized that we don't
>> log the lag time in the per-transaction log file. I think that's a serious
>> omission; when --rate is used, the schedule lag time is important information
>> to make sense of the result. I think we have to apply the attached patch, and
>> backpatch to 9.4. (The documentation on the log file format needs updating)
>
> Indeed. I think that people do not like it to change. I remember that I
> suggested to change timestamps to "xxxx.yyyyyy" instead of the unreadable
> "xxxx yyy", and be told not to, because some people have tool which
> process the output so the format MUST NOT CHANGE. So my behavior is not to
> avoid touching anything in this area.
>
> I'm fine if you do it, though:-) However I have not time to have a precise
> look at your patch to cross-check it before Friday.

This is different from changing "xxx yyy" to "xxx.yyy" in two ways.
First, this adds new information to the log that just isn't there
otherwise, it's not just changing the format for the sake of it. Second,
in this case it's easy to write a parser for the log format so that it
works with the old and new formats. Many such programs would probably
ignore any unexpected extra fields, as a matter of lazy programming,
while changing the separator from space to a dot would surely require
changing every parsing program.

We could leave out the lag fields, though, when --rate is not used.
Without --rate, the lag is always zero anyway. That would keep the file
format unchanged, when you don't use the new --rate feature. I'm not
sure if that would be better or worse for programs that might want to
parse the information.

>> Also, this is bizarre:
>>
>>> int64 wait = (int64) (throttle_delay *
>>> 1.00055271703 * -log(getrand(thread, 1, 10000) / 10000.0));
>>
>> We're using getrand() to generate a uniformly distributed random value
>> between 1 and 10000, and then convert it to a double between 0.0 and 1.0.
>
> The reason for this conversion is to have randomness but to still avoid
> going to extreme multiplier values. The idea is to avoid a very large
> multiplier which would generate (even if it is not often) a 0 tps when 100
> tps is required. The 10000 granularity is basically random but the
> multiplier stays bounded (max 9.2, so the minimum possible tps would be
> around 11 for a target of 100 tps, bar issues from the database for
> processing the transactions).
>
> So although this feature can be discussed and amended, it is fully
> voluntary. I think that it make sense so I would prefer to keep it as is.
> Maybe the comments could be update to be clearer.

Ok, yeah, the comments indeed didn't mention anything about that. I
don't think such clamping is necessary. With that 9.2x clamp on the
multiplier, the probability that any given transaction hits it is about
1/10000. And a delay 9.2 times the average is still quite reasonable,
IMHO. The maximum delay on my laptop, when pg_erand48() returns DBL_MIN,
seems to be about 700 x the average, which is still reasonable if you
run a decent number of transactions. And of course, the probability of
hitting such an extreme value is miniscule, so if you're just doing a
few quick test runs with a small total number of transactions, you won't
hit that.

- Heikki

On 09/10/2014 11:28 AM, Heikki Linnakangas wrote:
> On 09/10/2014 05:57 PM, Fabien COELHO wrote:
>>
>> Hello Heikki,
>>
>>> I looked closer at the this, and per Jan's comments, realized that we don't
>>> log the lag time in the per-transaction log file. I think that's a serious
>>> omission; when --rate is used, the schedule lag time is important information
>>> to make sense of the result. I think we have to apply the attached patch, and
>>> backpatch to 9.4. (The documentation on the log file format needs updating)
>>
>> Indeed. I think that people do not like it to change. I remember that I
>> suggested to change timestamps to "xxxx.yyyyyy" instead of the unreadable
>> "xxxx yyy", and be told not to, because some people have tool which
>> process the output so the format MUST NOT CHANGE. So my behavior is not to
>> avoid touching anything in this area.
>>
>> I'm fine if you do it, though:-) However I have not time to have a precise
>> look at your patch to cross-check it before Friday.
>
> This is different from changing "xxx yyy" to "xxx.yyy" in two ways.
> First, this adds new information to the log that just isn't there
> otherwise, it's not just changing the format for the sake of it. Second,
> in this case it's easy to write a parser for the log format so that it
> works with the old and new formats. Many such programs would probably
> ignore any unexpected extra fields, as a matter of lazy programming,
> while changing the separator from space to a dot would surely require
> changing every parsing program.
>
> We could leave out the lag fields, though, when --rate is not used.
> Without --rate, the lag is always zero anyway. That would keep the file
> format unchanged, when you don't use the new --rate feature. I'm not
> sure if that would be better or worse for programs that might want to
> parse the information.

We could also leave the default output format as is and introduce
another option with a % style format string.

Jan

>
>>> Also, this is bizarre:
>>>
>>>> int64 wait = (int64) (throttle_delay *
>>>> 1.00055271703 * -log(getrand(thread, 1, 10000) / 10000.0));
>>>
>>> We're using getrand() to generate a uniformly distributed random value
>>> between 1 and 10000, and then convert it to a double between 0.0 and 1.0.
>>
>> The reason for this conversion is to have randomness but to still avoid
>> going to extreme multiplier values. The idea is to avoid a very large
>> multiplier which would generate (even if it is not often) a 0 tps when 100
>> tps is required. The 10000 granularity is basically random but the
>> multiplier stays bounded (max 9.2, so the minimum possible tps would be
>> around 11 for a target of 100 tps, bar issues from the database for
>> processing the transactions).
>>
>> So although this feature can be discussed and amended, it is fully
>> voluntary. I think that it make sense so I would prefer to keep it as is.
>> Maybe the comments could be update to be clearer.
>
> Ok, yeah, the comments indeed didn't mention anything about that. I
> don't think such clamping is necessary. With that 9.2x clamp on the
> multiplier, the probability that any given transaction hits it is about
> 1/10000. And a delay 9.2 times the average is still quite reasonable,
> IMHO. The maximum delay on my laptop, when pg_erand48() returns DBL_MIN,
> seems to be about 700 x the average, which is still reasonable if you
> run a decent number of transactions. And of course, the probability of
> hitting such an extreme value is miniscule, so if you're just doing a
> few quick test runs with a small total number of transactions, you won't
> hit that.
>
> - Heikki
>

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

On 09/10/2014 05:47 PM, Mitsumasa KONDO wrote:
> Hi,
>
> I find typo in your patch. Please confirm.
>
> @line 239
> - agg->sum2_lag = 0;
> + agg->sum_lag = 0;

Ah thanks, cood catch!

> And back patch is welcome for me.

I've committed and backpatched this, as well as a patch to refactor the
way the Poisson delay is computed.

I kept the log file format unchanged when --rate is not used, so it now
has a different number of fields depending on whether --rate is used or not.

Please review the changes I made one more time, to double-check that I
mess up something.

- Heikki

On 08/30/2014 07:16 PM, 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?
>
> 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.

Now that I've finished the detour and committed and backpatched the
changes to the way latency is calculated, we can get back to this patch.
It needs to be rebased.

How should skipped transactions should be taken into account in the log
file output, with and without aggregation? I assume we'll want to have
some trace of skipped transactions in the logs.

- Heikki

Hello Heikki,

> Now that I've finished the detour and committed and backpatched the changes
> to the way latency is calculated, we can get back to this patch. It needs to
> be rebased.

Before rebasing, I think that there are a few small problems with the
modification applied to switch from an integer range to double.

(1) ISTM that the + 0.5 which remains in the PoissonRand computation comes
from the previous integer approach and is not needed here. If I'm not
mistaken the formula should be plain:

-log(uniform) * center

(2) I'm not sure of the name "center", I think that "lambda" or
"mean" would be more appropriate.

(3) I wish that the maximum implied multiplier could be explicitely
documented in the source code. From pg_rand48 source code, I think
that it is 33.27106466687737

--
Fabien.

> (3) I wish that the maximum implied multiplier could be explicitely
> documented in the source code. From pg_rand48 source code, I think
> that it is 33.27106466687737

Small possibly buggy code attached, to show how I computed the above
figure.

--
Fabien.

Hello Heikki

> Now that I've finished the detour and committed and backpatched the changes
> to the way latency is calculated, we can get back to this patch. It needs to
> be rebased.

Here is the rebase, which seems ok.

See also the small issues raised aboud getPoissonRand in another email.

--
Fabien.

> How should skipped transactions should be taken into account in the log file
> output, with and without aggregation? I assume we'll want to have some trace
> of skipped transactions in the logs.

The problem with this point is that how to report something "not done" is
unclear, especially as the logic of the log is one line per performed
transaction.

Obviously we can log something, but as the transaction are not performed
the format would be different, which break the expectation of a simple and
homogeneous log file format that people like to process directly.

So bar any great idea, I would suggest not to log skipped transactions and
to wait for someone to need to have access to this detailed information
and for whom the final report is not enough.

--
Fabien.

On 09/11/2014 03:36 PM, Fabien COELHO wrote:
>
> Hello Heikki,
>
>> Now that I've finished the detour and committed and backpatched the changes
>> to the way latency is calculated, we can get back to this patch. It needs to
>> be rebased.
>
> Before rebasing, I think that there are a few small problems with the
> modification applied to switch from an integer range to double.
>
> (1) ISTM that the + 0.5 which remains in the PoissonRand computation comes
> from the previous integer approach and is not needed here. If I'm not
> mistaken the formula should be plain:
>
> -log(uniform) * center

No. The +0.5 is to round the result to the nearest integer, instead of
truncating it down.

> (2) I'm not sure of the name "center", I think that "lambda" or
> "mean" would be more appropriate.

(shrug), I guess. The comment says that it's the value the average of a
series values is centered on, so "center" doesn't seem too bad. I guess
the mathematically accurate term would be "expected value".

> (3) I wish that the maximum implied multiplier could be explicitely
> documented in the source code. From pg_rand48 source code, I think
> that it is 33.27106466687737

Oh, ok. That's an even smaller multiplier than I got just by feeding
DBL_MIN to the formula. I don't think that's worth worrying about. That
might change if the implementation of pg_erand48() is changed, so I'm a
bit reluctant to state it explicitly.

- Heikki

On 09/11/2014 05:16 PM, Fabien COELHO wrote:
>
>> How should skipped transactions should be taken into account in the log file
>> output, with and without aggregation? I assume we'll want to have some trace
>> of skipped transactions in the logs.
>
> The problem with this point is that how to report something "not done" is
> unclear, especially as the logic of the log is one line per performed
> transaction.
>
> Obviously we can log something, but as the transaction are not performed
> the format would be different, which break the expectation of a simple and
> homogeneous log file format that people like to process directly.
>
> So bar any great idea, I would suggest not to log skipped transactions and
> to wait for someone to need to have access to this detailed information
> and for whom the final report is not enough.

We have to come up with something. The point of the log file is that it
contains all the information you need to build your own graphs, when
pgbench's built-in reporting features are not enough. If it doesn't
contain detailed information about skipped transactions, a report based
on the log file would be inaccurate, or at least misleading.

How about printing a line in the log for every skipped transaction, with
the string "skipped" in place of the latency. The "completion time" can
show the time when the transaction was skipped, and the lag can show the
difference between the scheduled time and the time it was skipped. Or
put another way, print a line as if the transaction completed
immediately, but with the "skipped" string in the latency field.

The "skipped" string will trip a program that doesn't expect that, but
since this is a new feature that you have to enable manually, that's OK.

The output would look something like this (modified from the manual's
example by hand, so the numbers don't add up):

0 199 2241 0 1175850568 995598 1020
0 200 2465 0 1175850568 998079 1010
0 201 skipped 1175850569 608 3011
0 202 skipped 1175850569 608 2400
0 203 skipped 1175850569 608 1000
0 204 2513 0 1175850569 608 500
0 205 2038 0 1175850569 2663 500

- Heikki

>> (1) ISTM that the + 0.5 which remains in the PoissonRand computation comes
>> from the previous integer approach and is not needed here. If I'm not
>> mistaken the formula should be plain:
>>
>> -log(uniform) * center
>
> No. The +0.5 is to round the result to the nearest integer, instead of
> truncating it down.

Hmmm... probably ok. I'll have to think about it a bit.

In that case, it seems much clearer to do: "round(-log(uniform) * xxx)"
instead of relying on the truncation of the cast.

>> (2) I'm not sure of the name "center", I think that "lambda" or
>> "mean" would be more appropriate.
>
> (shrug), I guess. The comment says that it's the value the average of a
> series values is centered on, so "center" doesn't seem too bad. I guess the
> mathematically accurate term would be "expected value".

The word "center" does not appear once of the wikipedia page about
"Poisson distribution". Its "mean" is called "lambda" (or rather λ:-) all
over the place. I find "expected value" rather too generic, but it is
better than "center".

>> (3) I wish that the maximum implied multiplier could be explicitely
>> documented in the source code. From pg_rand48 source code, I think
>> that it is 33.27106466687737
>
> Oh, ok. That's an even smaller multiplier than I got just by feeding DBL_MIN
> to the formula. I don't think that's worth worrying about. That might change
> if the implementation of pg_erand48() is changed, so I'm a bit reluctant to
> state it explicitly.

I think that it is an important information, so it deserves to appear.

If pg_erand48 implementation changes, it should not be called "48",
because the max value and the above multiplier limit is completely linked
to the "16*3=48" structure of the random construction.

If the code change then the comments need be updated, that is life.

--
Fabien.

> The output would look something like this (modified from the manual's example
> by hand, so the numbers don't add up):
>
> 0 199 2241 0 1175850568 995598 1020
> 0 200 2465 0 1175850568 998079 1010
> 0 201 skipped 1175850569 608 3011
> 0 202 skipped 1175850569 608 2400
> 0 203 skipped 1175850569 608 1000
> 0 204 2513 0 1175850569 608 500
> 0 205 2038 0 1175850569 2663 500

My 0.02€: ISTM that the number of columns should stay the same whether it
is skipped or not, so the "file_no" should be kept. Maybe to keep it a
number would make sense (-1) or just a sign (-) which means "no value"
with gnuplot for instance. Or "skipped".

Basically I would be fine with that, but as I do not use the log file
feature I'm not sure that my opinion should count.

Note that there are also potential issues with the aggregate logging and
the sampling stuff.

--
Fabien.

On 9/10/14, 10:57 AM, Fabien COELHO wrote:
> Indeed. I think that people do not like it to change. I remember that
> I suggested to change timestamps to "xxxx.yyyyyy" instead of the
> unreadable "xxxx yyy", and be told not to, because some people have
> tool which process the output so the format MUST NOT CHANGE. So my
> behavior is not to avoid touching anything in this area.

That somewhat hysterical version of events isn't what I said. Heikki has
the right idea for backpatching, so let me expand on that rationale,
with an eye toward whether 9.5 is the right time to deal with this.

Not all software out there will process epoch timestamps with
milliseconds added as a fraction at the end. Being able to read an
epoch time in seconds as an integer is a well defined standard; the
fraction part is not.

Here's an example of the problem, from a Mac OS X system:

$ date -j -f "%a %b %d %T %Z %Y" "`date`" "+%s"
1410544903
$ date -r 1410544903
Fri Sep 12 14:01:43 EDT 2014
$ date -r 1410544903.532
usage: date [-jnu] [-d dst] [-r seconds] [-t west] [-v[+|-]val[ymwdHMS]] ...
[-f fmt date | [[[mm]dd]HH]MM[[cc]yy][.ss]] [+format]

The current file format allows any random shell script to use a tool
like cut to pull out the second resolution timestamp column as an epoch
integer field, then pass it through even a utility as simple as date to
reformat that. And for a lot of people, second resolution is perfectly
fine anyway.

The change you propose will make that job harder for some people, in
order to make the job you're interested in easier. I picked the
simplest possible example, but there are more. Whether epoch timestamps
can have millisecond parts depends on your time library in Java, in
Python some behavior depends on whether you have 2.6 or earlier, I don't
think gnuplot handles milllisecond ones at all yet; the list goes on and
on. Some people will just have to apply a second split for timestamp
string pgbench outputs, at the period and use the left side, where right
now they can just split the whole thing on a space.

What you want to do is actually fine with me--and as far as I know, I'm
the producer of the most popular pgbench latency parsing script
around--but it will be a new sort of headache. I just wanted the
benefit to outweigh that. Breaking the existing scripts and burning
compatibility with simple utilities like date was not worth the tiny
improvement you wanted in your personal workflow. That's just not how
we do things in PostgreSQL.

If there's a good case that the whole format needs to be changed anyway,
like adding a new field, then we might as well switch to fractional
epoch timestamps too now though. When I added timestamps to the latency
log in 8.3, parsers that handled milliseconds were even more rare.
Today it's still inconsistent, but the workarounds are good enough to me
now. There's a lot more people using things like Python instead of bash
pipelines here in 2014 too.

--
Greg Smith greg(dot)smith(at)crunchydatasolutions(dot)com
Chief PostgreSQL Evangelist - http://crunchydatasolutions.com/

On Fri, Sep 12, 2014 at 2:27 PM, Gregory Smith <gregsmithpgsql(at)gmail(dot)com> wrote:
> If there's a good case that the whole format needs to be changed anyway,
> like adding a new field, then we might as well switch to fractional epoch
> timestamps too now though. When I added timestamps to the latency log in
> 8.3, parsers that handled milliseconds were even more rare. Today it's
> still inconsistent, but the workarounds are good enough to me now. There's
> a lot more people using things like Python instead of bash pipelines here in
> 2014 too.

+1. s/\..*// is not an onerous requirement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 09/12/2014 08:59 PM, Fabien COELHO wrote:
>
>> The output would look something like this (modified from the manual's example
>> by hand, so the numbers don't add up):
>>
>> 0 199 2241 0 1175850568 995598 1020
>> 0 200 2465 0 1175850568 998079 1010
>> 0 201 skipped 1175850569 608 3011
>> 0 202 skipped 1175850569 608 2400
>> 0 203 skipped 1175850569 608 1000
>> 0 204 2513 0 1175850569 608 500
>> 0 205 2038 0 1175850569 2663 500
>
> My 0.02€: ISTM that the number of columns should stay the same whether it
> is skipped or not, so the "file_no" should be kept.

Oh, sorry, I totally agree. I left file_no out by mistake.

> Maybe to keep it a
> number would make sense (-1) or just a sign (-) which means "no value"
> with gnuplot for instance. Or "skipped".
>
> Basically I would be fine with that, but as I do not use the log file
> feature I'm not sure that my opinion should count.
>
> Note that there are also potential issues with the aggregate logging and
> the sampling stuff.

Yep.

- Heikki

> [about logging...]

Here is an attempt at updating the log features, including the aggregate
and sampling stuff, with skipped transactions under throttling.

I moved the logging stuff into a function which is called when a
transaction is skipped or finished.

From a log file format perspective, I think that "-" would be better than
"skipped".

--
Fabien.

On Sat, Sep 13, 2014 at 4:25 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>> [about logging...]
>
> Here is an attempt at updating the log features, including the aggregate and
> sampling stuff, with skipped transactions under throttling.
>
> I moved the logging stuff into a function which is called when a transaction
> is skipped or finished.
>
> From a log file format perspective, I think that "-" would be better than
> "skipped".

I like skipped. That seems a lot more clear, and less likely to get
parsed as a numeric value by a careless regex like [+-]\d*

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 09/13/2014 11:25 AM, Fabien COELHO wrote:
>
>> [about logging...]
>
> Here is an attempt at updating the log features, including the aggregate
> and sampling stuff, with skipped transactions under throttling.
>
> I moved the logging stuff into a function which is called when a
> transaction is skipped or finished.

Makes sense.

I spent some time on this, and this is what I ended up with. Notable
changes:

* I split this into two patches for easier review. The first patch
contains the refactoring of the logging stuff, and the second patch
contains the new functionality.

* I renamed many of the variables, to be more consistent with existing
similar variables

* progress reporting was broken with !PTHREAD_FORK_EMULATION. Need to
collect the number of skipped xacts from all threads.

* I renamed the long option to --latency-limit. --limit is too generic.

Please have a look. I have not looked at the docs changes yet.

One thing that needs some thinking and changing is the progress
reporting. It currently looks like this:

progress: 1.0 s, 4863.0 tps, lat 3.491 ms stddev 2.487, lag 1.809 ms, 99
skipped
progress: 2.0 s, 5042.8 tps, lat 3.265 ms stddev 2.264, lag 1.584 ms, 16
skipped
progress: 3.0 s, 4926.1 tps, lat 2.731 ms stddev 2.371, lag 1.196 ms, 45
skipped
progress: 4.0 s, 4963.9 tps, lat 1.904 ms stddev 1.212, lag 0.429 ms, 0
skipped
progress: 5.0 s, 4971.2 tps, lat 2.592 ms stddev 1.722, lag 0.975 ms, 0
skipped

The absolute number of skipped transactions doesn't make much sense when
all the other numbers are averages, and tps is a 1/s value. If you don't
know the total number of transactions executed, the absolute number is
meaningless. (Although you can calculate the absolute number of
transactions executed by multiplying the TPS value with the interval). I
think a percentage would be better here.

Should we also print the number of late transactions here? I think that
would be an even more important detail than the number of skipped
transactions. It might be better to print only the percentage of late
transactions, including skipped ones. Or both, but it's difficult to
cram everything on a single line. This needs some further thinking..

- Heikki

On Mon, Sep 15, 2014 at 6:34 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> Please have a look. I have not looked at the docs changes yet.
>
> One thing that needs some thinking and changing is the progress reporting.
> It currently looks like this:
>
> progress: 1.0 s, 4863.0 tps, lat 3.491 ms stddev 2.487, lag 1.809 ms, 99
> skipped
> progress: 2.0 s, 5042.8 tps, lat 3.265 ms stddev 2.264, lag 1.584 ms, 16
> skipped
> progress: 3.0 s, 4926.1 tps, lat 2.731 ms stddev 2.371, lag 1.196 ms, 45
> skipped
> progress: 4.0 s, 4963.9 tps, lat 1.904 ms stddev 1.212, lag 0.429 ms, 0
> skipped
> progress: 5.0 s, 4971.2 tps, lat 2.592 ms stddev 1.722, lag 0.975 ms, 0
> skipped
>
> The absolute number of skipped transactions doesn't make much sense when all
> the other numbers are averages, and tps is a 1/s value. If you don't know
> the total number of transactions executed, the absolute number is
> meaningless. (Although you can calculate the absolute number of transactions
> executed by multiplying the TPS value with the interval). I think a
> percentage would be better here.
>
> Should we also print the number of late transactions here? I think that
> would be an even more important detail than the number of skipped
> transactions. It might be better to print only the percentage of late
> transactions, including skipped ones. Or both, but it's difficult to cram
> everything on a single line. This needs some further thinking..

I'm not sure I like the idea of printing a percentage. It might be
unclear what the denominator was if somebody feels the urge to work
back to the actual number of skipped transactions. I mean, I guess
it's probably just the value you passed to -R, so maybe that's easy
enough, but then why bother dividing in the first place? The user can
do that easily enough if they want the data that way.

I agree with you that it would be good to get some statistics on
late/skipped transactions, but it's not obvious what people will want.
Late transactions, straight up? Late by more than a threshold value?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> I'm not sure I like the idea of printing a percentage. It might be
> unclear what the denominator was if somebody feels the urge to work
> back to the actual number of skipped transactions. I mean, I guess
> it's probably just the value you passed to -R, so maybe that's easy
> enough, but then why bother dividing in the first place? The user can
> do that easily enough if they want the data that way.

Indeed "skipped" and "late" per second may have an unclear denominator. If
you divide by the time, the unit would be "tps", but 120 tps performance
including 20 late tps, plus 10 skipped tps... I do not think it is that
clear. Reporting "tps" for transaction *not* performed looks strange.

Maybe late transactions could be given as a percentage of all processed
transactions in the interval. But for skipped the percentage of what? The
only number that would make sense is the total number of transactions
schedule in the interval, but that would mean that the denominator for
late would be different than the denominator for skipped, which is
basically uncomprehensible.

> I agree with you that it would be good to get some statistics on
> late/skipped transactions, but it's not obvious what people will want.
> Late transactions, straight up? Late by more than a threshold value?

Yes.

Under throttling transaction are given a schedule start time. When the
transactions can actually start:

(1) if it is already more late (before even starting) than the latency
limit (a threshold), it is *NOT* started, but counted "skipped"

(2) otherwise it is started. When it finishes, it may be
(2a) out of the latency limit (scheduled time + limit)
=> it is counted as "late"
(2b) within the latency limit
=> all is well

--
Fabien.

On 09/15/2014 08:46 PM, Fabien COELHO wrote:
>
>> I'm not sure I like the idea of printing a percentage. It might be
>> unclear what the denominator was if somebody feels the urge to work
>> back to the actual number of skipped transactions. I mean, I guess
>> it's probably just the value you passed to -R, so maybe that's easy
>> enough, but then why bother dividing in the first place? The user can
>> do that easily enough if they want the data that way.
>
> Indeed "skipped" and "late" per second may have an unclear denominator. If
> you divide by the time, the unit would be "tps", but 120 tps performance
> including 20 late tps, plus 10 skipped tps... I do not think it is that
> clear. Reporting "tps" for transaction *not* performed looks strange.
>
> Maybe late transactions could be given as a percentage of all processed
> transactions in the interval. But for skipped the percentage of what? The
> only number that would make sense is the total number of transactions
> schedule in the interval, but that would mean that the denominator for
> late would be different than the denominator for skipped, which is
> basically uncomprehensible.

Hmm. I guess the absolute number makes sense, if you expect that there
are normally zero skipped transactions, or at least a very small number.
It's more like a "good or no good" indicator. Ok, I'm fine with that.

The version I'm now working on prints output like this:

> $ ./pgbench -T10 -P1 --rate=1600 --latency-limit=10
> starting vacuum...end.
> progress: 1.0 s, 1579.0 tps, lat 2.973 ms stddev 2.493, lag 2.414 ms, 4 skipped
> progress: 2.0 s, 1570.0 tps, lat 2.140 ms stddev 1.783, lag 1.599 ms, 0 skipped
> progress: 3.0 s, 1663.0 tps, lat 2.372 ms stddev 1.742, lag 1.843 ms, 4 skipped
> progress: 4.0 s, 1603.2 tps, lat 2.435 ms stddev 2.247, lag 1.902 ms, 13 skipped
> progress: 5.0 s, 1540.9 tps, lat 1.845 ms stddev 1.270, lag 1.303 ms, 0 skipped
> progress: 6.0 s, 1588.0 tps, lat 1.630 ms stddev 1.003, lag 1.097 ms, 0 skipped
> progress: 7.0 s, 1577.0 tps, lat 2.071 ms stddev 1.445, lag 1.517 ms, 0 skipped
> progress: 8.0 s, 1669.9 tps, lat 2.375 ms stddev 1.917, lag 1.846 ms, 0 skipped
> progress: 9.0 s, 1636.0 tps, lat 2.801 ms stddev 2.354, lag 2.250 ms, 5 skipped
> progress: 10.0 s, 1606.1 tps, lat 2.751 ms stddev 2.117, lag 2.197 ms, 2 skipped
> transaction type: TPC-B (sort of)
> scaling factor: 5
> query mode: simple
> number of clients: 1
> number of threads: 1
> duration: 10 s
> number of transactions actually processed: 16034
> number of transactions skipped: 28 (0.174 %)
> number of transactions above the 10.0 ms latency limit: 70 (0.436 %)
> latency average: 2.343 ms
> latency stddev: 1.940 ms
> rate limit schedule lag: avg 1.801 (max 9.994) ms
> tps = 1603.370819 (including connections establishing)
> tps = 1603.619536 (excluding connections establishing)

Those progress lines are 79 or 80 characters wide, so they *just* fit in
a 80-char terminal. Of course, if any of the printed numbers were
higher, it would not fit. I don't see how to usefully make it more
terse, though. I think we can live with this - these days it shouldn't
be a huge problem to enlare the terminal to make the output fit.

Here are new patches, again the first one is just refactoring, and the
second one contains this feature. I'm planning to commit the first one
shortly, and the second one later after people have had a chance to look
at it.

Greg: As the author of pgbench-tools, what do you think of this patch?
The log file format, in particular.

- Heikki

Hello Heikki,

> Here are new patches, again the first one is just refactoring, and the second
> one contains this feature. I'm planning to commit the first one shortly, and
> the second one later after people have had a chance to look at it.

I looked at it. It looks ok, but for a few spurious spacing changes here
and there. No big deal.

I tested it, everything I tested behaved as expected, so it is ok for me.

--
Fabien.

On 10/05/2014 10:43 AM, Fabien COELHO wrote:
>
> Hello Heikki,
>
>> Here are new patches, again the first one is just refactoring, and the second
>> one contains this feature. I'm planning to commit the first one shortly, and
>> the second one later after people have had a chance to look at it.
>
> I looked at it. It looks ok, but for a few spurious spacing changes here
> and there. No big deal.
>
> I tested it, everything I tested behaved as expected, so it is ok for me.

Thanks!

I committed the refactoring patch earlier, and just went through the
second patch again. I wordsmithed the documentation and comments, and
fixed the documentation on the log format. I also fixed the logging of
skipped transactions so that the schedule lag is reported correctly for
them.

One thing bothers me with the log format. Here's an example:

> 0 81 4621 0 1412881037 912698 3005
> 0 82 6173 0 1412881037 914578 4304
> 0 83 skipped 0 1412881037 914578 5217
> 0 83 skipped 0 1412881037 914578 5099
> 0 83 4722 0 1412881037 916203 3108
> 0 84 4142 0 1412881037 918023 2333
> 0 85 2465 0 1412881037 919759 740

Note how the transaction counter is not incremented for skipped
transactions. That's understandable, since we're not including skipped
transactions in the number of transactions executed, but it means that
the skipped transactions don't have a unique ID. That's annoying.

Here's a new version of the patch. I'll sleep over it before committing,
but I think it's fine now, except maybe for the unique ID thing.

- Heikki

> One thing bothers me with the log format. Here's an example:
>
>> 0 81 4621 0 1412881037 912698 3005
>> 0 82 6173 0 1412881037 914578 4304
>> 0 83 skipped 0 1412881037 914578 5217
>> 0 83 skipped 0 1412881037 914578 5099
>> 0 83 4722 0 1412881037 916203 3108
>> 0 84 4142 0 1412881037 918023 2333
>> 0 85 2465 0 1412881037 919759 740
>
> Note how the transaction counter is not incremented for skipped transactions.
> That's understandable, since we're not including skipped transactions in the
> number of transactions executed, but it means that the skipped transactions
> don't have a unique ID. That's annoying.

Indeed. As transactions were not done, it does not make much sense to
identify them. Otherwise it should report "intended" transactions and
"performed" transactions, which would not help clarify the matter much.

My idea of "skipped" transactions, which are not transactions as such, is
just a health quality measurement for both the throttling process and the
database latency, so I would really let it as it is.

> Here's a new version of the patch. I'll sleep over it before committing, but
> I think it's fine now, except maybe for the unique ID thing.

I saw a typo in a comment: "lateny" -> "latency". Otherwise it looks ok,
and the documentation seems indeed clearer than before.

--
Fabien.

On 10/09/2014 10:39 PM, Fabien COELHO wrote:
>> One thing bothers me with the log format. Here's an example:
>>
>>> 0 81 4621 0 1412881037 912698 3005
>>> 0 82 6173 0 1412881037 914578 4304
>>> 0 83 skipped 0 1412881037 914578 5217
>>> 0 83 skipped 0 1412881037 914578 5099
>>> 0 83 4722 0 1412881037 916203 3108
>>> 0 84 4142 0 1412881037 918023 2333
>>> 0 85 2465 0 1412881037 919759 740
>>
>> Note how the transaction counter is not incremented for skipped transactions.
>> That's understandable, since we're not including skipped transactions in the
>> number of transactions executed, but it means that the skipped transactions
>> don't have a unique ID. That's annoying.
>
> Indeed. As transactions were not done, it does not make much sense to
> identify them. Otherwise it should report "intended" transactions and
> "performed" transactions, which would not help clarify the matter much.
>
> My idea of "skipped" transactions, which are not transactions as such, is
> just a health quality measurement for both the throttling process and the
> database latency, so I would really let it as it is.

Hmm. I wonder if this is going to be a problem for programs that might
try to load the log file into a database table. No using transaction ID
as a unique key. Then again, you'll have to somehow deal with "skipped"
anyway.

>> Here's a new version of the patch. I'll sleep over it before committing, but
>> I think it's fine now, except maybe for the unique ID thing.
>
> I saw a typo in a comment: "lateny" -> "latency". Otherwise it looks ok,
> and the documentation seems indeed clearer than before.

Ok, committed after a few more typo-fixes.

Greg Smith, I'd still appreciate it if you could take a look at this, to
check how this will work for pgbench-tools.

- Heikki

On 10/13/14, 1:54 PM, Heikki Linnakangas wrote:
> Greg Smith, I'd still appreciate it if you could take a look at this,
> to check how this will work for pgbench-tools.

I'll do a QA pass on the committed version looking for issues, and
update the toolchain I publish to be compatible with it along the way too.

--
Greg Smith greg(dot)smith(at)crunchydatasolutions(dot)com
Chief PostgreSQL Evangelist - http://crunchydatasolutions.com/

On 2014-08-14 15:01:53 +0200, Fabien COELHO wrote:
>
> 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.

I was just trying to run tests with this, but as far as I can see it
doesn't really work:

pgbench postgres -M prepared -c 72 -j 72 -P 5 -T 3600 -R40000 -L100
...
progress: 240.0 s, 40191.8 tps, lat 1.250 ms stddev 0.965, lag 0.501 ms, 0 skipped
progress: 245.0 s, 39722.1 tps, lat 1.128 ms stddev 0.946, lag 0.435 ms, 0 skipped
progress: 250.0 s, 40074.5 tps, lat 1.059 ms stddev 0.745, lag 0.391 ms, 0 skipped
progress: 255.0 s, 40001.4 tps, lat 1.038 ms stddev 0.680, lag 0.377 ms, 0 skipped
progress: 260.0 s, 40147.6 tps, lat 1.161 ms stddev 0.950, lag 0.448 ms, 0 skipped
progress: 265.0 s, 39980.1 tps, lat 1.186 ms stddev 0.862, lag 0.457 ms, 0 skipped
progress: 270.0 s, 40090.9 tps, lat 1.292 ms stddev 1.239, lag 0.544 ms, 0 skipped
progress: 275.0 s, 33847.8 tps, lat 26.617 ms stddev 41.681, lag 25.317 ms, 26698 skipped
progress: 280.0 s, 20237.9 tps, lat 96.041 ms stddev 11.983, lag 92.510 ms, 97745 skipped
progress: 285.0 s, 24385.0 tps, lat 94.490 ms stddev 10.865, lag 91.514 ms, 80944 skipped
progress: 290.0 s, 27349.6 tps, lat 92.755 ms stddev 10.905, lag 90.136 ms, 62268 skipped
progress: 295.0 s, 28382.1 tps, lat 92.752 ms stddev 10.238, lag 90.212 ms, 58253 skipped
progress: 300.0 s, 28798.3 tps, lat 92.673 ms stddev 10.506, lag 90.172 ms, 56741 skipped
progress: 305.0 s, 29346.6 tps, lat 91.659 ms stddev 10.982, lag 89.210 ms, 53163 skipped
progress: 310.0 s, 30072.9 tps, lat 91.190 ms stddev 11.071, lag 88.802 ms, 48370 skipped
progress: 315.0 s, 30733.2 tps, lat 90.893 ms stddev 11.312, lag 88.548 ms, 47020 skipped
progress: 320.0 s, 31170.9 tps, lat 89.498 ms stddev 12.132, lag 87.192 ms, 43403 skipped
progress: 325.0 s, 33399.0 tps, lat 85.795 ms stddev 15.196, lag 83.639 ms, 32923 skipped
progress: 330.0 s, 22969.8 tps, lat 91.929 ms stddev 14.762, lag 88.805 ms, 84780 skipped
progress: 335.0 s, 18913.3 tps, lat 95.236 ms stddev 14.523, lag 91.444 ms, 104960 skipped
progress: 340.0 s, 20061.2 tps, lat 95.258 ms stddev 13.284, lag 91.660 ms, 100396 skipped
progress: 345.0 s, 20405.3 tps, lat 94.781 ms stddev 13.794, lag 91.255 ms, 98510 skipped
progress: 350.0 s, 20596.0 tps, lat 94.661 ms stddev 13.345, lag 91.189 ms, 95426 skipped
progress: 355.0 s, 13635.7 tps, lat 96.998 ms stddev 38.039, lag 91.691 ms, 132598 skipped
progress: 360.0 s, 16648.0 tps, lat 95.138 ms stddev 26.329, lag 90.809 ms, 117129 skipped
progress: 365.0 s, 18392.1 tps, lat 94.857 ms stddev 23.917, lag 90.980 ms, 106244 skipped

100k skipped transactions at a rate limit of 40k? That doesn't seem right.

(that's master server/pgbench as of 5be94a9eb15a)

Regards,

Andres

On 2015-10-20 20:55:46 +0200, Andres Freund wrote:
> On 2014-08-14 15:01:53 +0200, Fabien COELHO wrote:
> >
> > 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.
>
> I was just trying to run tests with this, but as far as I can see it
> doesn't really work:
>
> pgbench postgres -M prepared -c 72 -j 72 -P 5 -T 3600 -R40000 -L100

> progress: 365.0 s, 18392.1 tps, lat 94.857 ms stddev 23.917, lag 90.980 ms, 106244 skipped
>
> 100k skipped transactions at a rate limit of 40k? That doesn't seem right.

Argh. It's just because I used -P5. It's a bit confusing that the other
options are per second, and this is per interval...

Andres

Hello Andres,

>> pgbench postgres -M prepared -c 72 -j 72 -P 5 -T 3600 -R40000 -L100
>
>> progress: 365.0 s, 18392.1 tps, lat 94.857 ms stddev 23.917, lag 90.980 ms, 106244 skipped
>>
>> 100k skipped transactions at a rate limit of 40k? That doesn't seem right.
>
> Argh. It's just because I used -P5. It's a bit confusing that the other
> options are per second, and this is per interval...

I agree, but I'm unsure of a fix, beyond what is already done which is to
show units next to the figures...

ISTM that people expect "tps" for performance, even on several seconds.
When it comes to skipped transactions, a count seemed more natural. I
really just see this as an indicator that things are not going smoothly.

Maybe it could be shown as a percentage of scheduled transactions,
possibly with an option?

A mitigation is to always run with -P 1 :-).

--
Fabien.

On 2015/10/22 18:20, Fabien COELHO wrote:
>>
>>> progress: 365.0 s, 18392.1 tps, lat 94.857 ms stddev 23.917, lag 90.980
>>> ms, 106244 skipped
>>>
>>> 100k skipped transactions at a rate limit of 40k? That doesn't seem right.
>>
>> Argh. It's just because I used -P5. It's a bit confusing that the other
>> options are per second, and this is per interval...
>
> I agree, but I'm unsure of a fix, beyond what is already done which is to
> show units next to the figures...
>
> ISTM that people expect "tps" for performance, even on several seconds.
> When it comes to skipped transactions, a count seemed more natural. I
> really just see this as an indicator that things are not going smoothly.
>
> Maybe it could be shown as a percentage of scheduled transactions,
> possibly with an option?
>
> A mitigation is to always run with -P 1 :-).

Wouldn't printing average (per second) over the interval work?

Thanks,
Amit

>>> Argh. It's just because I used -P5. It's a bit confusing that the other
>>> options are per second, and this is per interval...
>>
>> I agree, but I'm unsure of a fix, beyond what is already done which is to
>> show units next to the figures...
>>
>> ISTM that people expect "tps" for performance, even on several seconds.
>> When it comes to skipped transactions, a count seemed more natural. I
>> really just see this as an indicator that things are not going smoothly.
>>
>> Maybe it could be shown as a percentage of scheduled transactions,
>> possibly with an option?
>>
>> A mitigation is to always run with -P 1 :-).
>
> Wouldn't printing average (per second) over the interval work?

Yes it would. That would be "skipped tps". Why not. The percentage seems
also attractive to me, because it does not matter whether you get big
figures or small figures as it is relative.

--
Fabien.