Re: gaussian distribution pgbench

Hello Fabien-san,

I have checked your v13 patch, and tested the new exponential distribution
generating algorithm. It works fine and less or no overhead than previous
version.
Great work! And I agree with your proposal.

And I'm also interested in your "decile percents" output like under
followings,

> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=20
> ~
> decile percents: 86.5% 11.7% 1.6% 0.2% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
> ~
> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=10
> ~
> decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
> ~
> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=5
> ~
> decile percents: 39.6% 24.0% 14.6% 8.8% 5.4% 3.3% 2.0% 1.2% 0.7% 0.4%
> ~

I think that it is easy to understand exponential distribution when I check
the exponential parameter. I also agree with it. So I create decile
percents output
in gaussian distribution.
Here are the examples.

> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --gaussian=20
> ~
> decile percents: 0.0% 0.0% 0.0% 0.0% 50.0% 50.0% 0.0% 0.0% 0.0% 0.0%
> ~
> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --gaussian=10
> ~
> decile percents: 0.0% 0.0% 0.0% 2.3% 47.7% 47.7% 2.3% 0.0% 0.0% 0.0%
> ~
> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --gaussian=5
> ~
> decile percents: 0.0% 0.1% 2.1% 13.6% 34.1% 34.1% 13.6% 2.1% 0.1% 0.0%

I think that it is easier than before. Sum of decile percents is just 100%.

However, I don't prefer "highest/lowest percentage" because it will be
confused
with decile percentage for users, and anyone cannot understand this
digits.

Here is example when sets exponential=5,
> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=5
> ~
> decile percents: 39.6% 24.0% 14.6% 8.8% 5.4% 3.3% 2.0% 1.2% 0.7% 0.4%
> highest/lowest percent of the range: 4.9% 0.0%
> ~

I cannot understand "4.9%, 0.0%" when I see the first time.
Then, I checked the source code, I understood it:( It's not good design...
#Why this parameter use 100?
So I'd like to remove it if you like. It will be more simple.

Attached patch is fixed version, please confirm it.
#Of course, World Cup is being held now. I'm not hurry at all.

Best regards,
--
Mitsumasa KONDO

Hello Mitsumasa-san,

> And I'm also interested in your "decile percents" output like under
> followings,
> decile percents: 39.6% 24.0% 14.6% 8.8% 5.4% 3.3% 2.0% 1.2% 0.7% 0.4%

Sure, I'm really fine with that.

> I think that it is easier than before. Sum of decile percents is just 100%.

That's a good property:-)

> However, I don't prefer "highest/lowest percentage" because it will be
> confused with decile percentage for users, and anyone cannot understand
> this digits. I cannot understand "4.9%, 0.0%" when I see the first time.
> Then, I checked the source code, I understood it:( It's not good
> design... #Why this parameter use 100?

What else? People have ten fingers and like powers of 10, and are used to
percents?

> So I'd like to remove it if you like. It will be more simple.

I think that for the exponential distribution it helps, especially for
high threshold, to have the lowest/highest percent density. For low
thresholds, the decile is also definitely useful. So I'm fine with both
outputs as you have put them.

I have just updated the wording so that it may be clearer:

decile percents: 69.9% 21.0% 6.3% 1.9% 0.6% 0.2% 0.1% 0.0% 0.0% 0.0%
probability of fist/last percent of the range: 11.3% 0.0%

> Attached patch is fixed version, please confirm it.

Attached a v15 which just fixes a typo and the above wording update. I'm
validating it for committers.

> #Of course, World Cup is being held now. I'm not hurry at all.

I'm not a soccer kind of person, so it does not influence my
availibility.:-)

Suggested commit message:

Add drawing random integers with a Gaussian or truncated exponentional
distributions to pgbench.

Test variants with these distributions are also provided and triggered
with options "--gaussian=..." and "--exponential=...".

Have a nice day/night,

--
Fabien.

> I have just updated the wording so that it may be clearer:

Oops, I have sent the wrong patch, without the wording fix. Here is the
real updated version, which I tested.

> probability of fist/last percent of the range: 11.3% 0.0%

--
Fabien.

On 02/07/14 21:05, Fabien COELHO wrote:
>
> Hello Mitsumasa-san,
>
>> And I'm also interested in your "decile percents" output like under
>> followings,
>> decile percents: 39.6% 24.0% 14.6% 8.8% 5.4% 3.3% 2.0% 1.2% 0.7% 0.4%
>
> Sure, I'm really fine with that.
>
>> I think that it is easier than before. Sum of decile percents is just
>> 100%.
>
> That's a good property:-)
>
>> However, I don't prefer "highest/lowest percentage" because it will
>> be confused with decile percentage for users, and anyone cannot
>> understand this digits. I cannot understand "4.9%, 0.0%" when I see
>> the first time. Then, I checked the source code, I understood it:(
>> It's not good design... #Why this parameter use 100?
>
> What else? People have ten fingers and like powers of 10, and are used
> to percents?
>
>> So I'd like to remove it if you like. It will be more simple.
>
> I think that for the exponential distribution it helps, especially for
> high threshold, to have the lowest/highest percent density. For low
> thresholds, the decile is also definitely useful. So I'm fine with
> both outputs as you have put them.
>
> I have just updated the wording so that it may be clearer:
>
> decile percents: 69.9% 21.0% 6.3% 1.9% 0.6% 0.2% 0.1% 0.0% 0.0% 0.0%
> probability of fist/last percent of the range: 11.3% 0.0%
>
>> Attached patch is fixed version, please confirm it.
>
> Attached a v15 which just fixes a typo and the above wording update.
> I'm validating it for committers.
>
>> #Of course, World Cup is being held now. I'm not hurry at all.
>
> I'm not a soccer kind of person, so it does not influence my
> availibility.:-)
>
>
> Suggested commit message:
>
> Add drawing random integers with a Gaussian or truncated exponentional
> distributions to pgbench.
>
> Test variants with these distributions are also provided and triggered
> with options "--gaussian=..." and "--exponential=...".
>
>
> Have a nice day/night,
>
>
>
I would suggest that probabilities should NEVER be expressed in
percentages! As a percentage probability looks weird, and is never used
for serious statistical work - in my experience at least.

I think probabilities should be expressed in the range 0 ... 1 - i.e.
0.35 rather than 35%.

Cheers,
Gavin

Hello Gavin,

>> decile percents: 69.9% 21.0% 6.3% 1.9% 0.6% 0.2% 0.1% 0.0% 0.0% 0.0%
>> probability of fist/last percent of the range: 11.3% 0.0%

> I would suggest that probabilities should NEVER be expressed in percentages!
> As a percentage probability looks weird, and is never used for serious
> statistical work - in my experience at least.
>
> I think probabilities should be expressed in the range 0 ... 1 - i.e. 0.35
> rather than 35%.

I could agree about the mathematics, but ISTM that "11.5%" is more
readable and intuitive than "0.115".

I could change "probability" and replace it with "frequency" or maybe
"occurence", what would you think about that?

--
Fabien.

On 03/07/14 20:58, Fabien COELHO wrote:
>
> Hello Gavin,
>
>>> decile percents: 69.9% 21.0% 6.3% 1.9% 0.6% 0.2% 0.1% 0.0% 0.0% 0.0%
>>> probability of fist/last percent of the range: 11.3% 0.0%
>
>> I would suggest that probabilities should NEVER be expressed in
>> percentages! As a percentage probability looks weird, and is never
>> used for serious statistical work - in my experience at least.
>>
>> I think probabilities should be expressed in the range 0 ... 1 - i.e.
>> 0.35 rather than 35%.
>
> I could agree about the mathematics, but ISTM that "11.5%" is more
> readable and intuitive than "0.115".
>
> I could change "probability" and replace it with "frequency" or maybe
> "occurence", what would you think about that?
>

You may well be hitting a situation, where you meet opposition whatever
you do! :-)

"frequency" implies a positive integer (though "relative frequency"
might be okay) - and if you use "occurrence", someone else is bound to
complain...

Though, I'd opt for "relative frequency", if you can't use values in the
range 0 ... 1 for probabilities, if %'s are used - so long as it does
not generate a flame war.

I suspect it may not be worth the grief to change.

Cheers,
Gavin

On Wed, Jul 2, 2014 at 6:05 PM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>
> Hello Mitsumasa-san,
>
>> And I'm also interested in your "decile percents" output like under
>> followings,
>> decile percents: 39.6% 24.0% 14.6% 8.8% 5.4% 3.3% 2.0% 1.2% 0.7% 0.4%
>
>
> Sure, I'm really fine with that.
>
>
>> I think that it is easier than before. Sum of decile percents is just
>> 100%.
>
>
> That's a good property:-)
>
>> However, I don't prefer "highest/lowest percentage" because it will be
>> confused with decile percentage for users, and anyone cannot understand this
>> digits. I cannot understand "4.9%, 0.0%" when I see the first time. Then, I
>> checked the source code, I understood it:( It's not good design... #Why this
>> parameter use 100?
>
>
> What else? People have ten fingers and like powers of 10, and are used to
> percents?
>
>
>> So I'd like to remove it if you like. It will be more simple.
>
>
> I think that for the exponential distribution it helps, especially for high
> threshold, to have the lowest/highest percent density. For low thresholds,
> the decile is also definitely useful. So I'm fine with both outputs as you
> have put them.
>
> I have just updated the wording so that it may be clearer:
>
> decile percents: 69.9% 21.0% 6.3% 1.9% 0.6% 0.2% 0.1% 0.0% 0.0% 0.0%
> probability of fist/last percent of the range: 11.3% 0.0%
>
>
>> Attached patch is fixed version, please confirm it.
>
>
> Attached a v15 which just fixes a typo and the above wording update. I'm
> validating it for committers.
>
>
>> #Of course, World Cup is being held now. I'm not hurry at all.
>
>
> I'm not a soccer kind of person, so it does not influence my
> availibility.:-)
>
>
> Suggested commit message:
>
> Add drawing random integers with a Gaussian or truncated exponentional
> distributions to pgbench.
>
> Test variants with these distributions are also provided and triggered
> with options "--gaussian=..." and "--exponential=...".

IIRC we've not reached consensus about whether we should support
such options in pgbench. Several hackers disagreed to support them.
OTOH, we've almost reached the consensus that supporting gaussian
and exponential options in \setrandom. So I think that you should
separate those two features into two patches, and we should apply
the \setrandom one first. Then we can discuss whether the other patch
should be applied or not.

Regards,

--
Fujii Masao

On 2014-07-03 21:27:53 +0900, Fujii Masao wrote:
> > Add drawing random integers with a Gaussian or truncated exponentional
> > distributions to pgbench.
> >
> > Test variants with these distributions are also provided and triggered
> > with options "--gaussian=..." and "--exponential=...".
>
> IIRC we've not reached consensus about whether we should support
> such options in pgbench. Several hackers disagreed to support them.

Yea. I certainly disagree with the patch in it's current state because
it copies the same 15 lines several times with a two word
difference. Independent of whether we want those options, I don't think
that's going to fly.

> OTOH, we've almost reached the consensus that supporting gaussian
> and exponential options in \setrandom. So I think that you should
> separate those two features into two patches, and we should apply
> the \setrandom one first. Then we can discuss whether the other patch
> should be applied or not.

Sounds like a good plan.

Greetings,

Andres Freund

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

> Yea. I certainly disagree with the patch in it's current state because
> it copies the same 15 lines several times with a two word difference.
> Independent of whether we want those options, I don't think that's going
> to fly.

I liked a simple static string for the different variants, which means
replication. Factorizing out the (large) common part will mean malloc &
sprintf. Well, why not.

>> OTOH, we've almost reached the consensus that supporting gaussian
>> and exponential options in \setrandom. So I think that you should
>> separate those two features into two patches, and we should apply
>> the \setrandom one first. Then we can discuss whether the other patch
>> should be applied or not.

> Sounds like a good plan.

Sigh. I'll do that as it seems to be a blocker...

The caveat that I have is that without these options there is:

(1) no return about the actual distributions in the final summary, which
depend on the threshold value, and

(2) no included mean to test the feature, so the first patch is less
meaningful if the feature cannot be used simply and require a custom
script.

--
Fabien.

On 2014-07-04 11:59:23 +0200, Fabien COELHO wrote:
>
> >Yea. I certainly disagree with the patch in it's current state because it
> >copies the same 15 lines several times with a two word difference.
> >Independent of whether we want those options, I don't think that's going
> >to fly.
>
> I liked a simple static string for the different variants, which means
> replication. Factorizing out the (large) common part will mean malloc &
> sprintf. Well, why not.

It sucks from a maintenance POV. And I don't see the overhead of malloc
being relevant here...

> >>OTOH, we've almost reached the consensus that supporting gaussian
> >>and exponential options in \setrandom. So I think that you should
> >>separate those two features into two patches, and we should apply
> >>the \setrandom one first. Then we can discuss whether the other patch
> >>should be applied or not.
>
> >Sounds like a good plan.
>
> Sigh. I'll do that as it seems to be a blocker...

I think we also need documentation about the actual mathematical
behaviour of the randomness generators.

> + <para>
> + With the gaussian option, the larger the <replaceable>threshold</>,
> + the more frequently values close to the middle of the interval are drawn,
> + and the less frequently values close to the <replaceable>min</> and
> + <replaceable>max</> bounds.
> + In other worlds, the larger the <replaceable>threshold</>,
> + the narrower the access range around the middle.
> + the smaller the threshold, the smoother the access pattern
> + distribution. The minimum threshold is 2.0 for performance.
> + </para>

The only way to actually understand the distribution here is to create a
table, insert random values, and then look at the result. That's not a
good thing.

> The caveat that I have is that without these options there is:
>
> (1) no return about the actual distributions in the final summary, which
> depend on the threshold value, and
>
> (2) no included mean to test the feature, so the first patch is less
> meaningful if the feature cannot be used simply and require a custom script.

I personally agree that we likely want that as an additional
feature. Even if just because it makes the results easier to compare.

Greetings,

Andres Freund

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

Hi,

2014-07-04 19:05 GMT+09:00 Andres Freund <andres(at)2ndquadrant(dot)com>:

> On 2014-07-04 11:59:23 +0200, Fabien COELHO wrote:
> >
> > >Yea. I certainly disagree with the patch in it's current state because
> it
> > >copies the same 15 lines several times with a two word difference.
> > >Independent of whether we want those options, I don't think that's going
> > >to fly.
> >
> > I liked a simple static string for the different variants, which means
> > replication. Factorizing out the (large) common part will mean malloc &
> > sprintf. Well, why not.
>
> It sucks from a maintenance POV. And I don't see the overhead of malloc
> being relevant here...
>
> > >>OTOH, we've almost reached the consensus that supporting gaussian
> > >>and exponential options in \setrandom. So I think that you should
> > >>separate those two features into two patches, and we should apply
> > >>the \setrandom one first. Then we can discuss whether the other patch
> > >>should be applied or not.
> >
> > >Sounds like a good plan.
> >
> > Sigh. I'll do that as it seems to be a blocker...
>
I still agree with Fabien-san. I cannot understand why our logical proposal
isn't accepted...

I think we also need documentation about the actual mathematical
> behaviour of the randomness generators.
> > + <para>
> > + With the gaussian option, the larger the
> <replaceable>threshold</>,
> > + the more frequently values close to the middle of the interval
> are drawn,
> > + and the less frequently values close to the <replaceable>min</>
> and
> > + <replaceable>max</> bounds.
> > + In other worlds, the larger the <replaceable>threshold</>,
> > + the narrower the access range around the middle.
> > + the smaller the threshold, the smoother the access pattern
> > + distribution. The minimum threshold is 2.0 for performance.
> > + </para>
>
> The only way to actually understand the distribution here is to create a
> table, insert random values, and then look at the result. That's not a
> good thing.
>
That's right. Therefore, we create command line option to easy to
understand parametrized Gaussian distribution.
When you want to know the parameter of distribution, you can use command
line option like under followings.

[nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=10
starting vacuum...end.
transaction type: Exponential distribution TPC-B (sort of)
scaling factor: 1
exponential threshold: 10.00000
decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
highest/lowest percent of the range: 9.5% 0.0%

[nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=5
starting vacuum...end.
transaction type: Exponential distribution TPC-B (sort of)
scaling factor: 1
exponential threshold: 5.00000
decile percents: 39.6% 24.0% 14.6% 8.8% 5.4% 3.3% 2.0% 1.2% 0.7% 0.4%
highest/lowest percent of the range: 4.9% 0.0%

If you have a better method than our method, please share us.

> > The caveat that I have is that without these options there is:
> >
> > (1) no return about the actual distributions in the final summary, which
> > depend on the threshold value, and
> >
> > (2) no included mean to test the feature, so the first patch is less
> > meaningful if the feature cannot be used simply and require a custom
> script.
>
> I personally agree that we likely want that as an additional
> feature. Even if just because it makes the results easier to compare.
>
If we can do positive and logical discussion, I will agree with the
proposal about separate patches.
However, I think that most opposite hacker decided by his feelings...
Actuary, he didn't answer to our proposal about understanding the
parametrized distribution...
So I also think it is blocker. Command line feature is also needed.
Besides, is there a other good method? Please share us.

Best regards,
--
Mitsumasa KONDO

On Sun, Jul 13, 2014 at 2:27 AM, Mitsumasa KONDO
<kondo(dot)mitsumasa(at)gmail(dot)com> wrote:
> I still agree with Fabien-san. I cannot understand why our logical proposal
> isn't accepted...

Well, I think the feedback has been pretty clear, honestly. Here's
what I'm unhappy about: I can't understand what these options are
actually doing.

And this isn't helping me a bit:

> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=10
> starting vacuum...end.
> transaction type: Exponential distribution TPC-B (sort of)
> scaling factor: 1
> exponential threshold: 10.00000
>
> decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
> highest/lowest percent of the range: 9.5% 0.0%

I don't have a clue what that means. None.

Here is an example of an explanation that would make sense to me.
This is not the actual behavior of your patch, I'm quite sure, so this
is just an example of the *kind* of explanation that I think is
needed:

The --exponential option causes pgbench to select lower-numbered
account IDs exponentially more frequently than higher-numbered account
IDs. The argument to --exponential controls the strength of the
preference for lower-numbered account IDs, with a smaller value
indicating a stronger preference. Specifically, it is the percentage
of the total number of account IDs which will receive half the total
accesses. For example, with --exponential=10, half the accesses will
be to the smallest 10 percent of the account IDs; half the remaining
accesses will be to the next-smallest 10 percent of account IDs, and
so on. --exponential=50 therefore represents a completely flat
distribution; larger values are not allowed.

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

Hello Robert,

> Well, I think the feedback has been pretty clear, honestly. Here's
> what I'm unhappy about: I can't understand what these options are
> actually doing.

We can try to improve the documentation, once more!

However, ISTM that it is not the purpose of pgbench documentation to be a
primer about what is an exponential or gaussian distribution, so the idea
would yet be to have a relatively compact explanation, and that the
interested but clueless reader would document h..self from wikipedia or a
text book or a friend or a math teacher (who could be a friend as well:-).

>> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=10
>> starting vacuum...end.
>> transaction type: Exponential distribution TPC-B (sort of)
>> scaling factor: 1
>> exponential threshold: 10.00000
>>
>> decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
>> highest/lowest percent of the range: 9.5% 0.0%
>
> I don't have a clue what that means. None.

Maybe we could add in front of the decile/percent

"distribution of increasing account key values selected by pgbench:"

> Here is an example of an explanation that would make sense to me.
> This is not the actual behavior of your patch, I'm quite sure, so this
> is just an example of the *kind* of explanation that I think is
> needed:

This is more or less the approximate behavior of the patch, but for 1% of
the range, not 50%. However I'm not sure that the current documentation is
so bad.

> The --exponential option causes pgbench to select lower-numbered
> account IDs exponentially more frequently than higher-numbered account
> IDs. The argument to --exponential controls the strength of the
> preference for lower-numbered account IDs, with a smaller value
> indicating a stronger preference. Specifically, it is the percentage
> of the total number of account IDs which will receive half the total
> accesses. For example, with --exponential=10, half the accesses will
> be to the smallest 10 percent of the account IDs; half the remaining
> accesses will be to the next-smallest 10 percent of account IDs, and
> so on. --exponential=50 therefore represents a completely flat
> distribution; larger values are not allowed.

--
Fabien.

pgbench with gaussian & exponential, part 1 of 2.

This patch is a subset of the previous patch which only adds the two
new \setrandom gaussian and exponantial variants, but not the
adapted pgbench test cases, as suggested by Fujii Masao.
There is no new code nor code changes.

The corresponding documentation has been yet again extended wrt
to the initial patch, so that what is achieved is hopefully unambiguous
(there are two mathematical formula, tasty!), in answer to Andres Freund
comments, and partly to Robert Haas comments as well.

This patch also provides several sql/pgbench scripts and a README, so
that the feature can be tested. I do not know whether these scripts
should make it to postgresql. I would say yes, otherwise there is no way
to test...

part 2 which provide adapted pgbench test cases will come later.

--
Fabien.

On Wed, Jul 16, 2014 at 12:57 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>> Well, I think the feedback has been pretty clear, honestly. Here's
>> what I'm unhappy about: I can't understand what these options are
>> actually doing.
>
> We can try to improve the documentation, once more!
>
> However, ISTM that it is not the purpose of pgbench documentation to be a
> primer about what is an exponential or gaussian distribution, so the idea
> would yet be to have a relatively compact explanation, and that the
> interested but clueless reader would document h..self from wikipedia or a
> text book or a friend or a math teacher (who could be a friend as well:-).

Well, I think it's a balance. I agree that the pgbench documentation
shouldn't try to substitute for a text book or a math teacher, but I
also think that you shouldn't necessarily need to refer to a text book
or a math teacher in order to figure out how to use pgbench. Saying
"it's complicated, so we don't have to explain it" would be a cop out;
we need to *make* it simple. And if there's no way to do that, then
IMHO we should reject the patch in favor of some future patch that
implements something that will be easy for users to understand.

>>> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=10
>>> starting vacuum...end.
>>> transaction type: Exponential distribution TPC-B (sort of)
>>> scaling factor: 1
>>> exponential threshold: 10.00000
>>>
>>> decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
>>> highest/lowest percent of the range: 9.5% 0.0%
>>
>> I don't have a clue what that means. None.
>
> Maybe we could add in front of the decile/percent
>
> "distribution of increasing account key values selected by pgbench:"

I still wouldn't know what that meant. And it misses the point
anyway: if the documentation is good, this will be unnecessary. If
the documentation is bad, a printout that tries to illustrate it by
example is not an acceptable substitute.

>> Here is an example of an explanation that would make sense to me.
>> This is not the actual behavior of your patch, I'm quite sure, so this
>> is just an example of the *kind* of explanation that I think is
>> needed:
>
> This is more or less the approximate behavior of the patch, but for 1% of
> the range, not 50%. However I'm not sure that the current documentation is
> so bad.

I think it isn't, because in the system I described, a larger value
indicates a flatter distribution, but in the documentation, a smaller
value indicates a flatter distribution. That having been said, I
agree the current documentation for the exponential distribution is
not too bad. But this part does not make sense:

+ A crude approximation of the distribution is that the most frequent 1%
+ values are drawn <replaceable>threshold</>% of the time.
+ The closer to 0.0 the threshold, the flatter (more uniform) the access
+ distribution.

Given the first statement, I'd expect the lowest possible threshold to
be 0.01, not 0.

The documentation for the Gaussian distribution is in somewhat worse
shape. Unlike the documentation for exponential, it makes no attempt
at all to give the user a clear idea what the distribution actually
looks like. The closest it comes is this:

+ In other worlds, the larger the <replaceable>threshold</>,
+ the narrower the access range around the middle.

But that's not really very close - there's no way for a user to judge
what impact the threshold parameter actually has except to try it.
Unlike the discussion of exponential, which contains a fairly-precise
mathematical characterization of the behavior, the Gaussian stuff has
nothing except a hand-wavy explanation that a higher threshold skews
the distribution more. (Also, the English expression is "in other
words" not "in other worlds" - but in fact the phrase has no business
in that sentence at all, because it is not reiterating the contents of
the previous sentence in different language, but rather making a new
point entirely. And the following sentence does not start with a
capital letter, though maybe that's because it was intended to be
incorporated into this sentence somehow.)

I think that you also need to consider which instances of the words
"gaussian" and "exponential" are referring to the option and which are
referring to the abstract mathematical concept. When you're talking
about the option, you should use all lower-case (as you've done) but
with <literal> tags or similar. When you're referring to the
mathematical distribution, Gaussian should be capitalized.

BTW, I agree with both Heikki's suggestion that we make these options
to setrandom only and not expose command-line options for them, and
with Andres's critique that the documentation of those options is far
too repetitive.

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

>> However, ISTM that it is not the purpose of pgbench documentation to be a
>> primer about what is an exponential or gaussian distribution, so the idea
>> would yet be to have a relatively compact explanation, and that the
>> interested but clueless reader would document h..self from wikipedia or a
>> text book or a friend or a math teacher (who could be a friend as well:-).
>
> Well, I think it's a balance. I agree that the pgbench documentation
> shouldn't try to substitute for a text book or a math teacher, but I
> also think that you shouldn't necessarily need to refer to a text book
> or a math teacher in order to figure out how to use pgbench. Saying
> "it's complicated, so we don't have to explain it" would be a cop out;
> we need to *make* it simple. And if there's no way to do that, then
> IMHO we should reject the patch in favor of some future patch that
> implements something that will be easy for users to understand.
>
>>>> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=10
>>>> starting vacuum...end.
>>>> transaction type: Exponential distribution TPC-B (sort of)
>>>> scaling factor: 1
>>>> exponential threshold: 10.00000
>>>>
>>>> decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
>>>> highest/lowest percent of the range: 9.5% 0.0%
>>>
>>> I don't have a clue what that means. None.
>>
>> Maybe we could add in front of the decile/percent
>>
>> "distribution of increasing account key values selected by pgbench:"
>
> I still wouldn't know what that meant. And it misses the point
> anyway: if the documentation is good, this will be unnecessary. If
> the documentation is bad, a printout that tries to illustrate it by
> example is not an acceptable substitute.

The decile description is quite classic when discussing statistics.

>>> Here is an example of an explanation that would make sense to me.
>>> This is not the actual behavior of your patch, I'm quite sure, so this
>>> is just an example of the *kind* of explanation that I think is
>>> needed:
>>
>> This is more or less the approximate behavior of the patch, but for 1% of
>> the range, not 50%. However I'm not sure that the current documentation is
>> so bad.
>
> I think it isn't, because in the system I described, a larger value
> indicates a flatter distribution, but in the documentation, a smaller
> value indicates a flatter distribution.

Ok. But the general thrust was ok.

> That having been said, I agree the current documentation for the
> exponential distribution is not too bad. But this part does not make
> sense:
>
> + A crude approximation of the distribution is that the most frequent 1%
> + values are drawn <replaceable>threshold</>% of the time.

I'm trying to be nice to the reader by providing an intuitive
information. I do not seem to succeed:-) I'm attempting to say that when
you draw from a range, say 1 to 1000, the first 1%, i.e. values 1 to 10,
are draw about "threshold"% of the time.

If I draw from one hundred values:

\setrandom x 1 100 exponential 10.0

The 1 will be drawn about 10% of the time, and the 99 next values will
share the remaining 90%.

> + The closer to 0.0 the threshold, the flatter (more uniform) the access
> + distribution.
>
> Given the first statement, I'd expect the lowest possible threshold to
> be 0.01, not 0.

This is in the sense of "epsilon", small number close to 0 but different
from 0. The lowest possible threshold is the smallest
strictly positive representable with a "double".

> The documentation for the Gaussian distribution is in somewhat worse
> shape. Unlike the documentation for exponential, it makes no attempt
> at all to give the user a clear idea what the distribution actually
> looks like. The closest it comes is this:
>
> + In other worlds, the larger the <replaceable>threshold</>,
> + the narrower the access range around the middle.
>
> But that's not really very close - there's no way for a user to judge
> what impact the threshold parameter actually has except to try it.
> Unlike the discussion of exponential, which contains a fairly-precise
> mathematical characterization of the behavior,

I have now added a precise formula for Gaussian. When you see the formula,
maybe you still would want see the decile to have an intuition.

I think that we assumed that the reader would know that a gaussian
distribution is the classic bell-shaped distribution, and if not .?he
would not be interested anyway.

> the Gaussian stuff has
> nothing except a hand-wavy explanation that a higher threshold skews
> the distribution more. (Also, the English expression is "in other
> words" not "in other worlds" - but in fact the phrase has no business
> in that sentence at all, because it is not reiterating the contents of
> the previous sentence in different language, but rather making a new
> point entirely. And the following sentence does not start with a
> capital letter, though maybe that's because it was intended to be
> incorporated into this sentence somehow.)
>
> I think that you also need to consider which instances of the words
> "gaussian" and "exponential" are referring to the option and which are
> referring to the abstract mathematical concept. When you're talking
> about the option, you should use all lower-case (as you've done) but
> with <literal> tags or similar. When you're referring to the
> mathematical distribution, Gaussian should be capitalized.
>
> BTW, I agree with both Heikki's suggestion that we make these options
> to setrandom only and not expose command-line options for them, and
> with Andres's critique that the documentation of those options is far
> too repetitive.

I'll have yet another ago at trying to improve the documentation, esp the
gaussian part. However you must allow that these are Mathematics, and the
user who wants to use these distribution will be expected to understand
what they are somehow beforehand.

Moreover, I cannot make it precise, intuitive and very short.

--
Fabien.

2014-07-18 5:13 GMT+09:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> However, ISTM that it is not the purpose of pgbench documentation to be a
>>> primer about what is an exponential or gaussian distribution, so the idea
>>> would yet be to have a relatively compact explanation, and that the
>>> interested but clueless reader would document h..self from wikipedia or a
>>> text book or a friend or a math teacher (who could be a friend as
>>> well:-).
>>>
>>
>> Well, I think it's a balance. I agree that the pgbench documentation
>> shouldn't try to substitute for a text book or a math teacher, but I
>> also think that you shouldn't necessarily need to refer to a text book
>> or a math teacher in order to figure out how to use pgbench. Saying
>> "it's complicated, so we don't have to explain it" would be a cop out;
>> we need to *make* it simple. And if there's no way to do that, then
>> IMHO we should reject the patch in favor of some future patch that
>> implements something that will be easy for users to understand.
>>
>> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=10
>>>>> starting vacuum...end.
>>>>> transaction type: Exponential distribution TPC-B (sort of)
>>>>> scaling factor: 1
>>>>> exponential threshold: 10.00000
>>>>>
>>>>> decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
>>>>> highest/lowest percent of the range: 9.5% 0.0%
>>>>>
>>>>
>>>> I don't have a clue what that means. None.
>>>>
>>>
>>> Maybe we could add in front of the decile/percent
>>>
>>> "distribution of increasing account key values selected by pgbench:"
>>>
>>
>> I still wouldn't know what that meant. And it misses the point
>> anyway: if the documentation is good, this will be unnecessary. If
>> the documentation is bad, a printout that tries to illustrate it by
>> example is not an acceptable substitute.
>>
>
> The decile description is quite classic when discussing statistics.

Yeah, maybe, I and Fabien-san don't believe that he doesn't know the decile
percentage.
However, I think more description about decile is needed.

For example, when we set the number of transaction 10,000 (-t 10000),
range of aid is 100,000,
and --exponential is 10, decile percents is under following as you know.

decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
highest/lowest percent of the range: 9.5% 0.0%

They mean that,
#number of access in range of aid (from decile percents):
1 to 10,000 => 6,320 times
10,001 to 20,000 => 2,330 times
20,001 to 30,000 => 860 times
...
90,001 to 10,0000 => 0 times

#number of access in range of aid (from highest/lowest percent of the
range):
1 to 1,000 => 950 times
...
99,001 to 10,0000 => 0 times

that's all.

Their information is easy to understand distribution of access probability,
isn't it?
Maybe I and Fabien-san have a knowledge of mathematics, so we think decile
percentage is common sense.
But if it isn't common sense, I agree with adding about these explanation
in the documents.

Best regards,
--
Mitsumasa KONDO

> For example, when we set the number of transaction 10,000 (-t 10000),
> range of aid is 100,000,
> and --exponential is 10, decile percents is under following as you know.
>
> decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
> highest/lowest percent of the range: 9.5% 0.0%
>
> They mean that,
> #number of access in range of aid (from decile percents):
> 1 to 10,000 => 6,320 times
> 10,001 to 20,000 => 2,330 times
> 20,001 to 30,000 => 860 times
> ...
> 90,001 to 10,0000 => 0 times
>
> #number of access in range of aid (from highest/lowest percent of the
> range):
> 1 to 1,000 => 950 times
> ...
> 99,001 to 10,0000 => 0 times
>
> that's all.
>
> Their information is easy to understand distribution of access probability,
> isn't it?
> Maybe I and Fabien-san have a knowledge of mathematics, so we think decile
> percentage is common sense.
> But if it isn't common sense, I agree with adding about these explanation
> in the documents.

What we are talking about is the "summary" at the end of the run, which is
expected to be compact, hence the terse few lines.

I'm not sure how to make it explicit without extending the summary too
much, so it would not be a summary anymore:-)

My initial assumption is that anyone interested enough in changing the
default uniform distribution for a test would know about decile, but that
seems to be optimistic.

Maybe it would be okay to keep a terse summary but to expand the
documentation to explain what it means, as you suggested above...

--
Fabien.

Please find attached 2 patches, which are a split of the patch discussed
in this thread.

(A) add gaussian & exponential options to pgbench \setrandom
the patch includes sql test files.

There is no change in the *code* from previous already reviewed
submissions, so I do not think that it needs another review on that
account.

However I have (yet again) reworked the *documentation* (for Andres Freund
& Robert Haas), in particular both descriptions now follow the same
structure (introduction, formula, intuition, rule of thumb and
constraint). I have differentiated the concept and the option by putting
the later in <literal> tags, and added a link to the corresponding
wikipedia pages.

Please bear in mind that:
1. English is not my native language.
2. this is not easy reading... this is maths, to read slowly:-)
3. word smithing contributions are welcome.

I assume somehow that a user interested in gaussian & exponential
distributions must know a little bit about probabilities...

(B) add pgbench test variants with gauss & exponential.

I have reworked the patch so as to avoid copy pasting the 3 test cases, as
requested by Andres Freund, thus this is new, although quite simple, code.
I have also added explanations in the documentation about how to interpret
the "decile" outputs, so as to hopefully address Robert Haas comments.

--
Fabien.

> Please find attached 2 patches, which are a split of the patch discussed in
> this thread.

Please find attached a very minor improvement to apply a code (variable
name) simplification directly in patch A so as to avoid a change in patch
B. The cumulated patch is the same as previous.

> (A) add gaussian & exponential options to pgbench \setrandom
> the patch includes sql test files.
>
> There is no change in the *code* from previous already reviewed submissions,
> so I do not think that it needs another review on that account.
>
> However I have (yet again) reworked the *documentation* (for Andres Freund &
> Robert Haas), in particular both descriptions now follow the same structure
> (introduction, formula, intuition, rule of thumb and constraint). I have
> differentiated the concept and the option by putting the later in <literal>
> tags, and added a link to the corresponding wikipedia pages.
>
>
> Please bear in mind that:
> 1. English is not my native language.
> 2. this is not easy reading... this is maths, to read slowly:-)
> 3. word smithing contributions are welcome.
>
> I assume somehow that a user interested in gaussian & exponential
> distributions must know a little bit about probabilities...
>
>
>
> (B) add pgbench test variants with gauss & exponential.
>
> I have reworked the patch so as to avoid copy pasting the 3 test cases, as
> requested by Andres Freund, thus this is new, although quite simple, code. I
> have also added explanations in the documentation about how to interpret the
> "decile" outputs, so as to hopefully address Robert Haas comments.

--
Fabien.

On Thu, Jul 17, 2014 at 12:09 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
> pgbench with gaussian & exponential, part 1 of 2.
>
> This patch is a subset of the previous patch which only adds the two
> new \setrandom gaussian and exponantial variants, but not the
> adapted pgbench test cases, as suggested by Fujii Masao.
> There is no new code nor code changes.
>
> The corresponding documentation has been yet again extended wrt
> to the initial patch, so that what is achieved is hopefully unambiguous
> (there are two mathematical formula, tasty!), in answer to Andres Freund
> comments, and partly to Robert Haas comments as well.
>
> This patch also provides several sql/pgbench scripts and a README, so
> that the feature can be tested. I do not know whether these scripts
> should make it to postgresql. I would say yes, otherwise there is no way
> to test...
>
> part 2 which provide adapted pgbench test cases will come later.

Some review comments:

1. I suggest that getExponentialrand and getGaussianrand be renamed to
getExponentialRand and getGaussianRand.

2. I suggest that the code be changed so that the branch currently
labeled as /* uniform with extra argument */ become a hard error
instead of a warning.

3. Similarly, I suggest that the use of gaussian or uniform be an
error when argc < 6 OR argc > 6. I also suggest that the
parenthesized distribution type be dropped from the error message in
all cases.

4. This question mark seems like it should be a period:

+ * value fails the test? To be on the safe side, let
us try over.

5. With regards to the following paragraph:

<para>
+ The default random distribution is uniform, that is all values in the
+ range are drawn with equal probability. The gaussian and exponential
+ options allow to change this default. The mandatory
+ <replaceable>threshold</> double value controls the actual distribution
+ with gaussian or exponential.
+ </para>

This paragraph needs a bit of copy-editing. Here's an attempt: "By
default, all values in the range are drawn with equal probability.
The <literal>gaussian</> and <literal>exponential</> options modify
this behavior; each requires a mandatory threshold which determines
the precise shape of the distribution." The following paragraph
should be changed to begin with "For a Gaussian distribution" and the
one after "For an exponential distribution".

6. Overall, I think the documentation here looks much better now, but
I suggest adding one or two example to the Gaussian section. Like
this: for example, if threshold is 2.0, 68% of the values will fall in
the middle third of the interval; with a threshold of 3.0, 99.7% of
the values will fall in the middle third of the interval. These
numbers are fabricated, and the middle third of the interval might not
be the best part to talk about, but you get the idea (I hope).

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

Hello Robert,

> Some review comments:

Thanks a lot for your return.

Please find attached two new parts of the patch (A for setrandom
extension, B for pgbench embedded test case extension).

> 1. I suggest that getExponentialrand and getGaussianrand be renamed to
> getExponentialRand and getGaussianRand.

Done.

It was named like that because "getrand" was used for the uniform case.

> 2. I suggest that the code be changed so that the branch currently
> labeled as /* uniform with extra argument */ become a hard error
> instead of a warning.
>
> 3. Similarly, I suggest that the use of gaussian or uniform be an
> error when argc < 6 OR argc > 6. I also suggest that the
> parenthesized distribution type be dropped from the error message in
> all cases.

I wish to agree, but my interpretation of the previous code is that they
were ignored before, so ISTM that we are stuck with keeping the same
unfortunate behavior.

> 4. This question mark seems like it should be a period:
>
> + * value fails the test? To be on the safe side, let us try over.

Indeed.

> 5. With regards to the following paragraph:
>
> <para>
> + The default random distribution is uniform, that is all values in the
> + range are drawn with equal probability. The gaussian and exponential
> + options allow to change this default. The mandatory
> + <replaceable>threshold</> double value controls the actual distribution
> + with gaussian or exponential.
> + </para>
>
> This paragraph needs a bit of copy-editing. Here's an attempt: "By
> default, all values in the range are drawn with equal probability.
> The <literal>gaussian</> and <literal>exponential</> options modify
> this behavior; each requires a mandatory threshold which determines
> the precise shape of the distribution." The following paragraph
> should be changed to begin with "For a Gaussian distribution" and the
> one after "For an exponential distribution".

Ok. I've kept "uniform" in the first sentence, because this is both
an option name and it is significant in term of probabilities.

> 6. Overall, I think the documentation here looks much better now, but
> I suggest adding one or two example to the Gaussian section. Like
> this: for example, if threshold is 2.0, 68% of the values will fall in
> the middle third of the interval; with a threshold of 3.0, 99.7% of
> the values will fall in the middle third of the interval. These
> numbers are fabricated, and the middle third of the interval might not
> be the best part to talk about, but you get the idea (I hope).

Done with threshold value 4.0 so I have a "middle quarter" and a "middle
half".

--
Fabien.

Hi,

Thank you for your grate documentation and fix working!!!
It becomes very helpful for understanding our feature.

I add two feature in gauss_B_4.patch.

1) Add gaussianProbability() function
It is same as exponentialProbability(). And the feature is as same as
before.

2) Add result of "max/min percent of the range"
It is almost same as --exponential option's result. However, max percent of
the range is center of distribution
and min percent of the range is most side of distribution.
Here is the output example,

+ pgbench_account's aid selected with a truncated gaussian distribution

+ standard deviation threshold: 5.00000

+ decile percents: 0.0% 0.1% 2.1% 13.6% 34.1% 34.1% 13.6% 2.1% 0.1% 0.0%

+ probability of max/min percent of the range: 4.0% 0.0%

And I add the explanation about this in the document.

I'm very appreciate for your works!!!

Best regards,

--

Mitsumasa KONDO

> Thank you for your grate documentation and fix working!!!
> It becomes very helpful for understanding our feature.

Hopefully it will help make it, or part of it, pass through.

> I add two feature in gauss_B_4.patch.
>
> 1) Add gaussianProbability() function
> It is same as exponentialProbability(). And the feature is as same as
> before.

Ok, that is better for readability and easy reuse.

> 2) Add result of "max/min percent of the range"
> It is almost same as --exponential option's result. However, max percent of
> the range is center of distribution
> and min percent of the range is most side of distribution.
> Here is the output example,

Ok, good that make it homogeneous with the exponential case.

> + pgbench_account's aid selected with a truncated gaussian distribution
> + standard deviation threshold: 5.00000
> + decile percents: 0.0% 0.1% 2.1% 13.6% 34.1% 34.1% 13.6% 2.1% 0.1% 0.0%
> + probability of max/min percent of the range: 4.0% 0.0%

> And I add the explanation about this in the document.

This is a definite improvement. I tested these minor changes and
everything seems ok.

Attached is a very small update. One word removed from the doc, and one
redundant declaration removed from the code.

I also have a problem with assert & Assert. I finally figured out that
Assert is not compiled in by default, thus it is generally ignored. So it
is more for debugging purposes when activated than for guarding against
some unexpected user errors.

--
Fabien.

Fabien COELHO wrote:

> I also have a problem with assert & Assert. I finally figured out
> that Assert is not compiled in by default, thus it is generally
> ignored. So it is more for debugging purposes when activated than
> for guarding against some unexpected user errors.

Yes, Assert() is for debugging during development. If you need to deal
with user error, use regular if () and exit() as appropriate (ereport()
in the backend). We mostly avoid assert() in our own code.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Thanks for your modify the patch! I confirmed that It seems to be fine.

I think that our latest patch fill all community comment.
So it is really ready for committer now.

Best regards,
--
Mitsumasa KONDO

On 07/17/2014 11:13 PM, Fabien COELHO wrote:
>
>>> However, ISTM that it is not the purpose of pgbench documentation to be a
>>> primer about what is an exponential or gaussian distribution, so the idea
>>> would yet be to have a relatively compact explanation, and that the
>>> interested but clueless reader would document h..self from wikipedia or a
>>> text book or a friend or a math teacher (who could be a friend as well:-).
>>
>> Well, I think it's a balance. I agree that the pgbench documentation
>> shouldn't try to substitute for a text book or a math teacher, but I
>> also think that you shouldn't necessarily need to refer to a text book
>> or a math teacher in order to figure out how to use pgbench. Saying
>> "it's complicated, so we don't have to explain it" would be a cop out;
>> we need to *make* it simple. And if there's no way to do that, then
>> IMHO we should reject the patch in favor of some future patch that
>> implements something that will be easy for users to understand.
>>
>>>>> [nttcom(at)localhost postgresql]$ contrib/pgbench/pgbench --exponential=10
>>>>> starting vacuum...end.
>>>>> transaction type: Exponential distribution TPC-B (sort of)
>>>>> scaling factor: 1
>>>>> exponential threshold: 10.00000
>>>>>
>>>>> decile percents: 63.2% 23.3% 8.6% 3.1% 1.2% 0.4% 0.2% 0.1% 0.0% 0.0%
>>>>> highest/lowest percent of the range: 9.5% 0.0%
>>>>
>>>> I don't have a clue what that means. None.
>>>
>>> Maybe we could add in front of the decile/percent
>>>
>>> "distribution of increasing account key values selected by pgbench:"
>>
>> I still wouldn't know what that meant. And it misses the point
>> anyway: if the documentation is good, this will be unnecessary. If
>> the documentation is bad, a printout that tries to illustrate it by
>> example is not an acceptable substitute.
>
> The decile description is quite classic when discussing statistics.

IMHO we should include a diagram for each distribution. A diagram would
be much more easy to understand than a decile or verbal explanation.

The only problem is that the build infrastructure doesn't currently
support including images in the docs. That's been discussed before, and
I think we even used to have a couple of images there a long time ago.
Now would be a good time to bite the bullet and add the support.
We got fairly close to a consensus on how to do it in this thread:
www.postgresql.org/message-id/flat/20120712181636(dot)GC11063(at)momjian(dot)us(dot)
The biggest problem was choosing an editor that has a fairly stable file
format, so that we don't get huge diffs every time someone moves a line
in a diagram. One work-around for that is to use graphviz and/or gnuplot
as the source format, instead of a graphical editor.

- Heikki

On Wed, Jul 23, 2014 at 12:39 PM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>> 3. Similarly, I suggest that the use of gaussian or uniform be an
>> error when argc < 6 OR argc > 6. I also suggest that the
>> parenthesized distribution type be dropped from the error message in
>> all cases.
>
> I wish to agree, but my interpretation of the previous code is that they
> were ignored before, so ISTM that we are stuck with keeping the same
> unfortunate behavior.

I don't agree. I'm not in a huge hurry to fix all the places where
pgbench currently lacks error checks just because I don't have enough
to do (hint: I do have enough to do), but when we're adding more
complicated syntax in one particular place, bringing the error checks
in that portion of the code up to scratch is an eminently sensible
thing to do, and we should do it.

Also, please stop changing the title of this thread every other post.
It breaks threading for me (and anyone else using gmail), and that
makes the thread hard to follow.

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

Hello Robert,

>> I wish to agree, but my interpretation of the previous code is that
>> they were ignored before, so ISTM that we are stuck with keeping the
>> same unfortunate behavior.
>
> I don't agree. I'm not in a huge hurry to fix all the places where
> pgbench currently lacks error checks just because I don't have enough to
> do (hint: I do have enough to do), but when we're adding more
> complicated syntax in one particular place, bringing the error checks in
> that portion of the code up to scratch is an eminently sensible thing to
> do, and we should do it.

Ok. I'm in favor of that anyway. It is just that was afraid that changing
behavior, however poor the said behavior, could be a blocker.

> Also, please stop changing the title of this thread every other post.
> It breaks threading for me (and anyone else using gmail), and that
> makes the thread hard to follow.

Sorry. It does not break my mailer which relies on internal headers, but
I'll try to be compatible with this gmail "features" in the future.

--
Fabien.

Hello Robert,

>>> 3. Similarly, I suggest that the use of gaussian or uniform be an
>>> error when argc < 6 OR argc > 6. I also suggest that the
>>> parenthesized distribution type be dropped from the error message in
>>> all cases.
>>
>> I wish to agree, but my interpretation of the previous code is that they
>> were ignored before, so ISTM that we are stuck with keeping the same
>> unfortunate behavior.
>
> I don't agree.

Attached B patch does turn incorrect setrandom syntax into errors instead
of ignoring extra parameters.

First A patch is repeated to help commitfest references.

--
Fabien.

> Attached B patch does turn incorrect setrandom syntax into errors instead of
> ignoring extra parameters.
>
> First A patch is repeated to help commitfest references.

Oops, I applied the change on the wrong part:-(

Here is the change on part A which checks setrandom syntax, and B for
completeness.

--
Fabien.

On Tue, Jul 29, 2014 at 4:41 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>> Attached B patch does turn incorrect setrandom syntax into errors instead
>> of ignoring extra parameters.
>>
>> First A patch is repeated to help commitfest references.
>
> Oops, I applied the change on the wrong part:-(
>
> Here is the change on part A which checks setrandom syntax, and B for
> completeness.

I've committed the changes to pgbench.c and the documentation changes
with some further wordsmithing. I don't think including the other
changes in patch A is a good idea, nor am I in favor of patch B. But
thanks for your and Kondo-san's hard work on this; I think this will
be quite useful.

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

Hello Robert,

> I've committed the changes to pgbench.c and the documentation changes
> with some further wordsmithing.

Ok, thanks a lot for your reviews and your help with improving the
documentation.

> I don't think including the other changes in patch A is a good idea,

Fine. It was mostly for testing and checking purposes.

> nor am I in favor of patch B.

Yep. Would providing these as additional contrib files be more acceptable?
Something like "tpc-b-gauss.sql"... Otherwise there is no example
available to show the feature.

Thanks again,

--
Fabien.

Hi,

2014-07-31 5:18 GMT+09:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:
>
> I've committed the changes to pgbench.c and the documentation changes
>> with some further wordsmithing.
>>
>
> Ok, thanks a lot for your reviews and your help with improving the
> documentation.

Yeah, thanks for all relative members.

> I don't think including the other changes in patch A is a good idea,
>>
>
> Fine. It was mostly for testing and checking purposes.

Hmm... It doesn't have harm for pgbench source code. And, in general,
checking script is useful for avoiding bug.

nor am I in favor of patch B.
>>
>
> Yep.

No, patch B is still needed. Please tell me the reason. I don't like
deciding by someones feeling,
and it needs logical reason. Our documentation is better than the past. I
think it can easy to understand decile probability.
This part of the discussion is needed to continue...

Would providing these as additional contrib files be more acceptable?
> Something like "tpc-b-gauss.sql"... Otherwise there is no example available
> to show the feature.
>
I agree the test script and including command line options. It's not harm,
and it's useful.

Best regards,
--
Mitsumasa KONDO

On Wed, Jul 30, 2014 at 4:18 PM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>> nor am I in favor of patch B.
>
> Yep. Would providing these as additional contrib files be more acceptable?
> Something like "tpc-b-gauss.sql"... Otherwise there is no example available
> to show the feature.

To be honest, it just feels like clutter to me. If we added examples
for every feature that is as significant as this one is, we'd end up
with twice the installation footprint, and most of it would be stuff
nobody ever looked at. I think the documentation is good enough that
people will be able to understand how to use this feature, which is
good enough for me.

One thing that might still be worth doing is including the standard
pgbench scripts in the pgbench documentation. Then we could say
things like "and you could also modify these". Right now I tend to
end up cut-and-pasting from the source code, which is fine if you're a
hacker but not so user-friendly.

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

On Wed, Jul 30, 2014 at 9:00 PM, Mitsumasa KONDO
<kondo(dot)mitsumasa(at)gmail(dot)com> wrote:
> Hmm... It doesn't have harm for pgbench source code. And, in general,
> checking script is useful for avoiding bug.

Not if nobody runs it, or if people run it but don't know what the
output should look like. I think anyone who knows enough to find bugs
by running these scripts probably doesn't need the scripts.

> No, patch B is still needed. Please tell me the reason. I don't like
> deciding by someones feeling,
> and it needs logical reason. Our documentation is better than the past. I
> think it can easy to understand decile probability.
> This part of the discussion is needed to continue...
>
>> Would providing these as additional contrib files be more acceptable?
>> Something like "tpc-b-gauss.sql"... Otherwise there is no example available
>> to show the feature.
>
> I agree the test script and including command line options. It's not harm,
> and it's useful.

As to all of this, I simply don't agree that the stuff has enough
value to justify including it. Now, of course, that is subjective:
one person may think it has enough value, while another person may
think that it does not have enough value. So it just comes down to a
question of opinion, and we make those judgements of opinion all the
time. If we included everything that everyone who works on the code
wants included, we'd end up with a bloated mess of stuff that nobody
cares about; indeed, we have a significant amount of stuff in the
source code that IMHO looks like somebody's debugging leftovers that
should have been removed before commit. I don't want to add more
unless there is clear and convincing evidence that a significant
number of people want it, and that is not the case here.

Now, if we get a few reports from people saying, hey, I was doing some
benchmarking with pgbench, and I found the new gaussian feature to be
really excellent, but it sucked that there was no command-line option
for it, we can go back and add one. No problem! But in the meantime,
we've added the core of the feature without cluttering up the list of
command-line options with things that may or may not prove to be
useful.

One of the concerns that I have about the proposal of simply slapping
a gaussian or exponential modifier onto \setrandom aid 1 :naccounts is
that, while it will allow you to make part of the relation hot and
another part of the relation cold, you really can't get any more
fine-grained than that. If you use exponential, all the hot accounts
will be near the beginning of the relation, and if you use gaussian,
they'll all be in the middle. I'm not sure exactly will happen after
some updating has happened; I'm guessing some of the keys will still
be in their original location and others will have been pushed to the
end of the relation following relation-extension. But there's no way,
with those command line options, to for example have 5 hot spots
distributed uniformly through the relation; or even to have the end of
the relation rather than the beginning or the middle as the hot spot.
You can do those things with the newly-enhanced \setrand *and a custom
script* but not with just a command-line option. So that makes me
think that people who find these new facilities useful might not get
all that much use out of the command-line option anyway; and we can't
have a command-line option for every behavior anyone ever wants.

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

Hello Robert,

[...]

> One of the concerns that I have about the proposal of simply slapping a
> gaussian or exponential modifier onto \setrandom aid 1 :naccounts is
> that, while it will allow you to make part of the relation hot and
> another part of the relation cold, you really can't get any more
> fine-grained than that. If you use exponential, all the hot accounts
> will be near the beginning of the relation, and if you use gaussian,
> they'll all be in the middle.

That is a very good remark. Although I thought of it, I do not have a very
good solution yet:-)

From a testing perspective, if we assume that keys have no semantics, a
reasonable assumption is that the distribution of access for actual
realistic workloads is probably exponential (of gaussian, anyway hardly
uniform), but without direct correlation between key values.

In order to simulate that, we would have to apply a fixed (pseudo-)random
permutation to the exponential-drawn key values. This is a non trivial
problem. The version zero of solving it is to do nothing... it is the
current status;-) Version one is "k' = 1 + (a * k + b) modulo n" with "a"
prime with respect to "n", "n" being the number of keys. This is nearly
possible, but for the modulo operator which is currently missing, and that
I'm planning to submit for this very reason, but probably another time.

> I'm not sure exactly will happen after some updating has happened; I'm
> guessing some of the keys will still be in their original location and
> others will have been pushed to the end of the relation following
> relation-extension.

This is a not too bad side. What matters most in the long term is not the
key value correlation, but the actual storage correlation, i.e. whether
two tuples required are in the same page or not. At the beginning of a
simulation, with close key numbers being picked up with an exponential
distribution, the correlation is more that what would be expected.
However, once a significant amount of the table has been updated, this
initial artificial correlation is going to fade, and the test should
become more realistic.

--
Fabien.

On Thu, Jul 31, 2014 at 10:01 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>> One of the concerns that I have about the proposal of simply slapping a
>> gaussian or exponential modifier onto \setrandom aid 1 :naccounts is that,
>> while it will allow you to make part of the relation hot and another part of
>> the relation cold, you really can't get any more fine-grained than that. If
>> you use exponential, all the hot accounts will be near the beginning of the
>> relation, and if you use gaussian, they'll all be in the middle.
>
> That is a very good remark. Although I thought of it, I do not have a very
> good solution yet:-)
>
> From a testing perspective, if we assume that keys have no semantics, a
> reasonable assumption is that the distribution of access for actual
> realistic workloads is probably exponential (of gaussian, anyway hardly
> uniform), but without direct correlation between key values.
>
> In order to simulate that, we would have to apply a fixed (pseudo-)random
> permutation to the exponential-drawn key values. This is a non trivial
> problem. The version zero of solving it is to do nothing... it is the
> current status;-) Version one is "k' = 1 + (a * k + b) modulo n" with "a"
> prime with respect to "n", "n" being the number of keys. This is nearly
> possible, but for the modulo operator which is currently missing, and that
> I'm planning to submit for this very reason, but probably another time.

That's pretty crude, although I don't object to a modulo operator. It
would be nice to be able to use a truly random permutation, which is
not hard to generate but probably requires O(n) storage, likely a
problem for large scale factors. Maybe somebody who knows more math
than I do (like you, probably!) can come up with something more
clever.

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

Hello,

>> Version one is "k' = 1 + (a * k + b) modulo n" with "a" prime with
>> respect to "n", "n" being the number of keys. This is nearly possible,
>> but for the modulo operator which is currently missing, and that I'm
>> planning to submit for this very reason, but probably another time.
>
> That's pretty crude,

Yep. It is very simple, it is much better than nothing, and for a database
test is may be "good enough".

> although I don't object to a modulo operator. It would be nice to be
> able to use a truly random permutation, which is not hard to generate
> but probably requires O(n) storage, likely a problem for large scale
> factors.

That is indeed the actual issue in my mind. I was thinking of permutations
with a formula, which are not so easy to find and may end-up looking like
"(a*k+b)%n" anyway. I had the same issue for generating random data for a
schema (see http://www.coelho.net/datafiller.html).

> Maybe somebody who knows more math than I do (like you, probably!) can
> come up with something more clever.

I can certainly suggest other formula, but that does not mean beautiful
code, thus would probably be rejected. I'll see.

An alternative to this whole process may be to hash/modulo a non uniform
random value.

id = 1 + hash(some-random()) % n

But the hashing changes the distribution as it adds collisions, so I have
to think about how to be able to control the distribution in that case,
and what hash function to use.

--
Fabien.

Hi,

2014-08-01 16:26 GMT+09:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>

>
> Maybe somebody who knows more math than I do (like you, probably!) can
>> come up with something more clever.
>>
>
> I can certainly suggest other formula, but that does not mean beautiful
> code, thus would probably be rejected. I'll see.
>
> An alternative to this whole process may be to hash/modulo a non uniform
> random value.
>
> id = 1 + hash(some-random()) % n
>
> But the hashing changes the distribution as it adds collisions, so I have
> to think about how to be able to control the distribution in that case, and
> what hash function to use.

I think that we have to consider and select reproducible method, because
benchmark is always needed robust and reproducible result. And if we
realize this idea, we might need more accurate random generator that is
like Mersenne twister algorithm. erand48 algorithm is slow and not
accurate very much.

By the way, I don't know relativeness of this topic and command line
option... Well whatever...

Regards,
--
Mitsumasa KONDO