Re: gaussian distribution pgbench

Hello,

I create gaussinan distribution pgbench patch that can access records with
gaussian frequency. And I submit this commit fest.

* Purpose this patch
In the general transaction situation, clients access for all records equally is
hard to happen. I think gaussian distribution access patterns are most of
transaction petterns in general. My patch realizes neary this access pattern.

I think that not only it can simulate a general access pattern as an effect of
this patch, but also it is useful for new development features such as effective
use and free of shared_buffers, the readahead optimization in the OS, and the
speed-up of the tuple level lock.

* Usage
It is easy to use, only put -g with standard deviation threshold parameter.
If we set larger standard deviation threshold, pgbench access patern limited
more specific records. Min standard deviation threshold is 2.

Execution example command is here.
> [mitsu-ko(at)localhost postgresql]$ bin/pgbench -g 10 -c 16 -j 8 -T 300
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> standard deviation threshold: 10.00000
> access probability of top 20%, 10% and 5% records: 0.95450 0.68269 0.38292
> query mode: simple
> number of clients: 16
> number of threads: 8
> duration: 300 s
> number of transactions actually processed: 566367
> tps = 1887.821409 (including connections establishing)
> tps = 1887.949390 (excluding connections establishing)

"access probability" indicates top N access probability in this benchmark.
If we set larger standard deviation threshold parameter, it become more large.

Attached png files which are "gausian_2.png" and "gaussian_10.png" indicate
gaussian distribution access patern by my patch. "no_gaussian.png" is not with -g
option (normal). I think my patch realize gaussian distribution access patern.

* Approach
It replaces uniform random number generator to gaussian distribution random
number generator using by box-muller tansform method. Then, I use standard
deviation threshold parameter for mapping a normal distribution access pattern in
each record and normalization. It is linear mappping method that is a floating
point to an integer value.

* other
I also create another patches that can get more accurate benchmark result in
pgbench, and will submit them this commit fest. They are like that I submitted
checkpoint patch in the past. They are all right, too!

Any question?

Best regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

KONDO Mitsumasa <kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> wrote:

> I create gaussinan distribution pgbench patch that can access
> records with gaussian frequency. And I submit this commit fest.

Thanks!

I have moved this to the Open CommitFest, though.

https://commitfest.postgresql.org/action/commitfest_view/open

You had accidentally added to the CF In Progress.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hello Mitsumasa,

> In the general transaction situation, clients access for all records equally is
> hard to happen. I think gaussian distribution access patterns are most of
> transaction petterns in general. My patch realizes neary this access pattern.

That is great! I was just looking for something like that!

I have not looked at the patch yet, but from the plots you sent, it seems
that it is a gaussian distribution over the keys. However this pattern
induces stronger cache effects which are maybe not too realistic, because
neighboring keys in the middle are more likely to be chosen.

It seems to me that this is not desirable.

Have you considered adding a "randomization" layer, that is once you have
a key in [1 .. n] centered around n/2, then you perform a pseudo-random
transformation into the same domain so that key values are scattered over
the whole domain?

--
Fabien.

On 9/20/13 2:42 AM, KONDO Mitsumasa wrote:
> I create gaussinan distribution pgbench patch that can access records with
> gaussian frequency. And I submit this commit fest.

This patch no longer applies.

Sorry for my delay reply.
Since I have had vacation last week, I replyed from gmail.
However, it was stalled post to pgsql-hackers:-(

(2013/09/21 6:05), Kevin Grittner wrote:
> You had accidentally added to the CF In Progress.
Oh, I had completely mistook this CF schedule :-)
Maybe, Horiguchi-san is same situation...

However, because of your moving, I become first submitter in next CF.
Thank you for moving !
--
Mitsumasa KONDO
NTT Open Source Software Center

Sorry for my delay reply.
Since I have had vacation last week, I replied from gmail.
However, it was stalled post to pgsql-hackers:-(

(2013/09/21 7:54), Fabien COELHO wrote:
> However this pattern induces stronger cache effects which are maybe not too realistic,
> because neighboring keys in the middle are more likely to be chosen.
I think that your opinion is right. However, in effect, it is a
paseudo-benchmark, so that I think that such a simple mechanism is also necessary.

> Have you considered adding a "randomization" layer, that is once you have a key in [1 .. > n] centered around n/2, then you perform a pseudo-random transformation into the same > domain so that key values are scattered over the whole domain?
Yes. I also consider this patch. It can realize by adding linear mapping array
which is created by random generator. However, current erand48 algorithm is not
high accuracy and fossil algorithm, I do not know whether it works well. If we
realize it, we may need more accurate random generator algorithm which is like
Mersenne Twister.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

(2013/09/27 5:29), Peter Eisentraut wrote:
> This patch no longer applies.
I will try to create this patch in next commit fest.
If you have nice idea, please send me!

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

On 30.09.2013 07:12, KONDO Mitsumasa wrote:
> (2013/09/27 5:29), Peter Eisentraut wrote:
>> This patch no longer applies.
> I will try to create this patch in next commit fest.
> If you have nice idea, please send me!

A few thoughts on this:

1. DBT-2 uses a non-uniform distribution. You can use that instead of
pgbench.

2. Do we really want to add everything and the kitchen sink to pgbench?
Every addition is small when considered alone, but we'll soon end with a
monster. So I'm inclined to reject this patch on those grounds.

3. That said, this could be handy. But it would be even more handy if
you could get Gaussian random numbers with \setrandom, so that you could
use this with custom scripts. And once you implement that, do we
actually need the -g flag anymore? If you want TPC-B transactions with
gaussian distribution, you can write a custom script to do that. The
documentation includes a full script that corresponds to the built-in
TPC-B script.

So what I'd actually like to see is \setgaussian, for use in custom scripts.

- Heikki

> 3. That said, this could be handy. But it would be even more handy if you
> could get Gaussian random numbers with \setrandom, so that you could use this
> with custom scripts. And once you implement that, do we actually need the -g
> flag anymore? If you want TPC-B transactions with gaussian distribution, you
> can write a custom script to do that. The documentation includes a full
> script that corresponds to the built-in TPC-B script.
>
> So what I'd actually like to see is \setgaussian, for use in custom scripts.

Indeed, great idea! That looks pretty elegant! It would be something like:

\setgauss var min max sigma

I'm not sure whether sigma should be relative to max-min, or absolute.
I would say relative is better...

A concerned I raised is that what one should really want is a "pseudo
randomized" (discretized) gaussian, i.e. you want the probability of each
value along a gaussian distribution, *but* no direct frequency correlation
between neighbors. Otherwise, you may have unwanted/unrealistic positive
cache effects. Maybe this could be achieved by an independent built-in,
say either:

\randomize var min max [parameter ?]
\randomize var min max val [parameter]

Which would mean take variable var which must be in [min,max], and apply a
pseudo-random transformation which results is also in [min,max].

From a probabilistic point of view, it seems to me that a randomized
(discretized) exponential would be more significant to model a server
load.

\setexp var min max lambda...

--
Fabien.

On Thu, Nov 21, 2013 at 9:13 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> So what I'd actually like to see is \setgaussian, for use in custom scripts.

+1. I'd really like to be able to run a benchmark with a Gaussian and
uniform distribution side-by-side for comparative purposes - we need
to know that we're not optimizing one at the expense of the other.
Sure, DBT-2 gets you a non-uniform distribution, but it has serious
baggage from it being a tool primarily intended for measuring the
relative performance of different database systems. pgbench would be
pretty worthless for measuring the relative strengths and weaknesses
of different database systems, but it is not bad at informing the
optimization efforts of hackers. pgbench is a defacto standard for
that kind of thing, so we should make it incrementally better for that
kind of thing. No standard industry benchmark is likely to replace it
for this purpose, because such optimizations require relatively narrow
focus.

Sometimes I want to maximally pessimize the number of FPIs generated.
Other times I do not. Getting a sense of how something affects a
variety of distributions would be very valuable, not least since
normal distributions abound in nature.

--
Peter Geoghegan

On 20/12/13 09:36, Peter Geoghegan wrote:
> On Thu, Nov 21, 2013 at 9:13 AM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>> So what I'd actually like to see is \setgaussian, for use in custom scripts.
> +1. I'd really like to be able to run a benchmark with a Gaussian and
> uniform distribution side-by-side for comparative purposes - we need
> to know that we're not optimizing one at the expense of the other.
> Sure, DBT-2 gets you a non-uniform distribution, but it has serious
> baggage from it being a tool primarily intended for measuring the
> relative performance of different database systems. pgbench would be
> pretty worthless for measuring the relative strengths and weaknesses
> of different database systems, but it is not bad at informing the
> optimization efforts of hackers. pgbench is a defacto standard for
> that kind of thing, so we should make it incrementally better for that
> kind of thing. No standard industry benchmark is likely to replace it
> for this purpose, because such optimizations require relatively narrow
> focus.
>
> Sometimes I want to maximally pessimize the number of FPIs generated.
> Other times I do not. Getting a sense of how something affects a
> variety of distributions would be very valuable, not least since
> normal distributions abound in nature.
>
>
Curious, wouldn't the common usage pattern tend to favour a skewed
distribution, such as the Poisson Distribution (it has been over 40
years since I studied this area, so there may be better candidates).

Just that gut feeling & experience tends to make me think that the
"Normal" distribution may often not be the best for database access
simulation.

Cheers,
Gavin

On 12/19/13 5:52 PM, Gavin Flower wrote:
> Curious, wouldn't the common usage pattern tend to favour a skewed
> distribution, such as the Poisson Distribution (it has been over 40
> years since I studied this area, so there may be better candidates).
>

Some people like database load testing with a "Pareto principle"
distribution, where 80% of the activity hammers 20% of the rows such
that locking becomes important. (That's one specific form of Pareto
distribution) The standard pgbench load indirectly gets you quite a bit
of that due to all the contention on the branches table. Targeting all
of that at a single table can be more realistic.

My last round of reviewing a pgbench change left me pretty worn out with
wanting to extend that code much further. Adding in some new
probability distributions would be fine though, that's a narrow change.
We shouldn't get too excited about pgbench remaining a great tool for
too much longer though. pgbench is fast approaching a wall nowadays,
where it's hard for any single client server to fully overload today's
larger server. You basically need a second large server to generate
load, whereas what people really want is a bunch of coordinated small
clients. (That sort of wall was in early versions too, it just got
pushed upward a lot by the multi-worker changes in 9.0 coming around the
same time desktop core counts really skyrocketed)

pgbench started as a clone of a now abandoned Java project called
JDBCBench. I've been seriously considering a move back toward that
direction lately. Nowadays spinning up ten machines to run load
generation is trivial. The idea of extending pgbench's C code to
support multiple clients running at the same time and collating all of
their results is not a project I'd be excited about. It should remain a
perfectly fine tool for PostgreSQL developers to find code hotspots, but
that's only so useful.

(At this point someone normally points out Tsung solved all of those
problems years ago if you'd only give it a chance. I think it's kind of
telling that work on sysbench is rewriting the whole thing so you can
use Lua for your test scripts.)

Hi,

I revise my gaussian pgbench patch which wss requested from community.

* Changes
- Support custom script.
- "\setgaussian" is generating gaussian distribute random number.
- ex) \setgaussian [var] [min] [max] [stddev_threshold]
- We can use mixture model in multiple custom scripts.
- Delete short option "-g", and add long options ”--gaussian"
- Refactoring getrand() interface
> - getrand(TState *thread, int64 min, int64 max)
> + getrand(TState *thread, int64 min, int64 max, DistType dist_type, double value1)
- We can easy to add other random distribution algorithms. Please see detail
design in attached patch.

Febien COELHO wrote:
>> From a probabilistic point of view, it seems to me that a randomized
> (discretized) exponential would be more significant to model a server load.
>
> \setexp var min max lambda...
I can create randomized exponential distribution under following. It is very easy.
double rand_exp( double lambda ){
return -log(Uniform(0,1))/lambda;
}
If community wants this, I will add this function in my patch.

Gavin Flower wrote:
> Curious, wouldn't the common usage pattern tend to favour a skewed distribution,
> such as the Poisson Distribution (it has been over 40 years since I studied
> this area, so there may be better candidates).
The difference between Poisson distribution and Gaussian distribution is discrete
or not.
In my gaussian algorithm, first generating continuos gaussian distribution, next
projection to integer values which are each record, it will be discrete value.
Therefore, it will be almost simular with Poisson distribution. And when we set
larger standard deviations(higher 10), it will be created better approximation of
Poisson distribution.

Attached sql files are for custom scripts which are different distribution. It
realize mixture distribuion benchmark. And attached graph is the result.
[example command]
$pgbench -f file1.sql file2.sql

If you have more some comment, please send me.
Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Hello,

> I revise my gaussian pgbench patch which wss requested from community.

With a lot of delay for which I apologise, please find hereafter the
review.

Gaussian Pgbench v3 patch by Mitsumasa KONDO review

* The purpose of the patch is to allow a pgbench script to draw from normally
distributed integer values instead of uniformly distributed.

This is a valuable contribution to enable pgbench to generate more realistic
loads, which is seldom uniform in practice.

However, ISTM that other distributions such an exponantial one would make
more sense, and also the values should be further randomized so that
neighboring values are not more likely to be drawn. The latest point is non
trivial.

* Compilation

The patch applies and compiles against current head. It works as expected,
although there is few feedback from the script to show that.

* Mathematical soundness

We want to derive a discrete normal distribution from a uniform one.
Well, normal distributions are for continuous variables... Anyway, this is
done by computing a continuous normal distribution which is then projected
onto integers. I'm basically fine with that.

The system uses a Box-Muller transform (1958) to do this transformation.
The Ziggurat method seems to be prefered for this purpose, *but* it would
require precalculated tables which depends on the target values. So I'm
fine with the Box-Muller transform for pgbench.

The BM method uses 2 uniformly distributed numbers to derive 2 normally
distributed numbers. The implementation computes one of these, and loops
over till one match a threshold criterion.

More explanations, at least in comments, are needed about this threshold
and its meaning. It is required to be more than 2. I guess is that it allows
to limit the number of iterations of the while loop, but in what proportion
is unclear. The documentation does not also help the user to understand
this value and its meaning.

What I think it is: it is the deviation for the FURTHEST point around the
mean, that is the actual deviation associated to the "min" and "max" target
values. The 2 minimum value induces that there is a least 4 stddev lengths
between min & max, with the most likely mean in the middle.

If the threshold test fails, one of the 2 uniform number is redrawn, a new
candidate value is tested. I'm not at ease about why only 1 value is redrawn
and not both, some explanations would be welcome. Also, on the other hand,
why not test the other possible value (with cos) if the first one fails?

Also, as suggested above, I would like some explanations about how much this
while loop may iterate without success, say with the expected average number
of iterations with its explanation in a comment.

* Implementation

Random values :
double rand1 = 1.0 - rand; // instead of the LONG_MAX computation & limits.h
rand2 should be in (0, 1], but it is in [0, 1), use "1.0 - ..." as well?!

What is called "stdev*" in getrand() is really the chosen deviation from
the target mean, so it would make more sense to name it "dev".

I do not think that the getrand refactoring was such a good idea. I'm sorry
if I may have suggested that in a previous comment.
The new getrand possibly ignores its parameters, hmmmm. ISTM that it would
be much simpler in the code to have a separate and clean "getrand_normal"
or "getrand_gauss" called for "\setgaussian", and that's it. This would
allow to get rid of DistType and all of getrand changes in the code.

There are heavy constants computations (sqrt(log()) within the while
loop which would be moved out of the loop.

ISTM that the while condition would be easier to read as:

while ( dev < - threshold || threshold < dev )

Maybe the \\setgaussian argument handling may be transformed into a function,
so that it could be used easily later for some other distribution (say some
setexp:-)

* Options

ISTM that the test options would be better if made orthogonal, i.e. not to
have three --gaussian* options. I would suggest to have only one
--gaussian=NUM which would trigger gaussian tests with this threshold,
and --gaussian=3.5 --select-only would use the select-only variant,
and so on.

* Typos

gausian -> gaussian
patern -> pattern

* Conclusion :

- this is a valuable patch to help create more realistic load and make pgbench
a more useful tool. I'm greatly in favor of having such a functionality.

- it seems to me that the patch should be further improved before being
committed, in particular I would suggest:

(1) improve the explanations in the code and in the documentation, especially
about what is the "deviation threshold" and its precise link to generated
values.

(2) simplify the code with a separate gaussian getrand, and simpler or
more efficient code here and there, see comments above.

(3) use only one option to trigger gaussian tests.

(bonus) \setexp would be a nice:-)

--
Fabien.

Hi Febien,

Thank you very much for your very detail and useful comments!
I read your comment, I agree most of your advice:)

Attached patch is fixed for your comment. That are...
- Remove redundant long-option.
- We can use "--gaussian=NUM -S" or "--gaussian=NUMN -N" options.
- Add sentence in document
- Separate two random generate function which are uniform and gaussian.
- getGaussianrand() is created.
- Fix ranged random number more strictly, ex. (0,1) or [0,1).
- Please see comment of source code in detail:).
- Fix typo.
- Use cos() and sin() function when we generate gaussian random number.
- Add fast sqrt calculation algorithm.
- Reuse sqrt result and pre generate random number for reducing calculation cost.
- Experience of this method is under following. It will be little-bit faster
than non-reuse method. And distribution of gaussian is still good.

* Settings
shared_buffers = 1024MB

* Test script
pgbench -i -s 1
pgbench --gaussian=2 -T 30 -S -c8 -j4 -n
pgbench --gaussian=2 -T 30 -S -c8 -j4 -n
pgbench --gaussian=2 -T 30 -S -c8 -j4 -n

* Result
method | try1 | try2 | try3 |
--------------------------------------------|
reuse method | 44189 | 44453 | 44013 |
non-reuse method | 43567 | 43635 | 43508 |

(2014/02/09 21:32), Fabien COELHO wrote:
> This is a valuable contribution to enable pgbench to generate more realistic
> loads, which is seldom uniform in practice.
Thanks!

> However, ISTM that other distributions such an exponantial one would make
> more sense,
I can easy to create exponential distribution. Here, I assume exponential
distribution that is f(x) = lambda * exp^(-lambda * x) in general.
What do you think under following interface?

custom script: \setexp [varname] min max threshold
command : --exponential=NUM(threshold)

I don't want to use lambda variable for simple implementation. So lambda is
always 1. Because it can enough to control distribution by threshold. Threshold
parameter is f(x) value. And using created distribution projects to 'aid' by same
method. If you think OK, I will impliment under followings tomorrow, and also
create parseing part of this function...

do
{
rand = 1.0 - pg_erand48(thread->random_state);
rand = -log(rand);
}while( rand > exp_threshold)

return rand / exp_threshold;

> and also the values should be further randomized so that
> neighboring values are not more likely to be drawn. The latest point is non
> trivial.
That's right, but I worry about gaussian randomness and benchmark reproducibility
might be disappeared when we re-randomized access pattern, because Postgres
storage method manages records by each pages and it is difficult to realize
access randomness in whole pages, not record. If we solve this problem, we have
to need algorithm for smart shuffule projection function that is still having
gaussian randomized. I think it will be difficult, and it have to impement in
another patch in the future.

> * Mathematical soundness
>
> We want to derive a discrete normal distribution from a uniform one.
> Well, normal distributions are for continuous variables... Anyway, this is
> done by computing a continuous normal distribution which is then projected
> onto integers. I'm basically fine with that.
>
> The system uses a Box-Muller transform (1958) to do this transformation.
> The Ziggurat method seems to be prefered for this purpose, *but* it would
> require precalculated tables which depends on the target values. So I'm
> fine with the Box-Muller transform for pgbench.
Yes, that's right. I selected simple and relatively faster algorithm, that is
Box-Muller transform.

> The BM method uses 2 uniformly distributed numbers to derive 2 normally
> distributed numbers. The implementation computes one of these, and loops
> over till one match a threshold criterion.
>
> More explanations, at least in comments, are needed about this threshold
> and its meaning. It is required to be more than 2. I guess is that it allows
> to limit the number of iterations of the while loop,
Yes. This loop could not almost go on, because min stdev_threshold is 2.
The possibility of retry-loop is under 4 percent. It might not be problem.

> but in what proportion
> is unclear. The documentation does not also help the user to understand
> this value and its meaning.
Yes, it is huristic method. So I added the comments in document.

> What I think it is: it is the deviation for the FURTHEST point around the
> mean, that is the actual deviation associated to the "min" and "max" target
> values. The 2 minimum value induces that there is a least 4 stddev lengths
> between min & max, with the most likely mean in the middle.
Correct!

> If the threshold test fails, one of the 2 uniform number is redrawn, a new
> candidate value is tested. I'm not at ease about why only 1 value is redrawn
> and not both, some explanations would be welcome. Also, on the other hand,
> why not test the other possible value (with cos) if the first one fails?
Yes, I think so too. So I fixed this partan and it will be better. Past
implementations are not good:(

> Also, as suggested above, I would like some explanations about how much this
> while loop may iterate without success, say with the expected average number
> of iterations with its explanation in a comment.
I add my comments in source code.

> * Implementation
>
> Random values :
> double rand1 = 1.0 - rand; // instead of the LONG_MAX computation & limits.h
> rand2 should be in (0, 1], but it is in [0, 1), use "1.0 - ..." as well?!
It's more smart method. I change to this method.

> What is called "stdev*" in getrand() is really the chosen deviation from
> the target mean, so it would make more sense to name it "dev".
Hmm, I like stdev*. Short variable makes us more confuse:( And it's not big problem.

> I do not think that the getrand refactoring was such a good idea. I'm sorry
> if I may have suggested that in a previous comment.
> The new getrand possibly ignores its parameters, hmmmm. ISTM that it would
> be much simpler in the code to have a separate and clean "getrand_normal"
> or "getrand_gauss" called for "\setgaussian", and that's it. This would
> allow to get rid of DistType and all of getrand changes in the code.
I separate two function that are getrand() and getGaussianrand(), it becomes more
clear I think.

> There are heavy constants computations (sqrt(log()) within the while
> loop which would be moved out of the loop.
>
> ISTM that the while condition would be easier to read as:
>
> while ( dev < - threshold || threshold < dev )
OK, fixed.

>
> Maybe the \\setgaussian argument handling may be transformed into a function,
> so that it could be used easily later for some other distribution (say some
> setexp:-)

> * Options
>
> ISTM that the test options would be better if made orthogonal, i.e. not to
> have three --gaussian* options. I would suggest to have only one
> --gaussian=NUM which would trigger gaussian tests with this threshold,
> and --gaussian=3.5 --select-only would use the select-only variant,
> and so on.
Agreed. Fixed.

> * Typos
>
> gausian -> gaussian
> patern -> pattern
Oh, fixed.

> * Conclusion :
>
> - this is a valuable patch to help create more realistic load and make pgbench
> a more useful tool. I'm greatly in favor of having such a functionality.
>
> - it seems to me that the patch should be further improved before being
> committed, in particular I would suggest:
>
> (1) improve the explanations in the code and in the documentation, especially
> about what is the "deviation threshold" and its precise link to generated
> values.
>
> (2) simplify the code with a separate gaussian getrand, and simpler or
> more efficient code here and there, see comments above.
>
> (3) use only one option to trigger gaussian tests.
>
> (bonus) \setexp would be a nice:-)
Thank you for your comments. They make my patch more polished:)
I think my patch is fixed for supporting all your comments, but it might not be fixed
as you think. And if you notice other part, please send me.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Sorry, previos attached patch has small bug.
Please use latest one.

> 134 - return min + (int64) (max - min + 1) * rand;
> 134 + return min + (int64)((max - min + 1) * rand);

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

I add exponential distribution random generator (and little bit
refactoring:) ).
I use inverse transform method to create its distribution. It's very
simple method that is
created by - log (rand()). We can control slope of distribution using
threshold parameter.
It is same as gaussian threshold.

usage example
pgbench --exponential=NUM -S

Attached graph is created with exponential threshold = 5. We can see
exponential
distribution in the graphs. It supports -S, -N options and custom script.
So we set
"¥setexponential [var] [min] [max] [threshold]" in a transaction pattern
file,
it appear distribution we want.

We have no time to fix its very much... But I think almost part of patch
have been completed.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Gaussian Pgbench v6 patch by Mitsumasa KONDO review & patch v7.

* The purpose of the patch is to allow a pgbench script to draw from normally
distributed or exponentially distributed integer values instead of uniformly
distributed.

This is a valuable contribution to enable pgbench to generate more realistic
loads, which is seldom uniform in practice.

* Changes

I have updated the patch (v7) based on Mitsumasa latest v6:
- some code simplifications & formula changes.
- I've added explicit looping probability computations in comments
to show the (low) looping probability of the iterative search.
- I've tried to clarify the sgml documentation.
- I've removed the 5.0 default value as it was not used anymore.
- I've renamed some variables to match the naming style around.

* Compilation

The patch applies and compiles against current head. It works as expected,
although there is few feedback from the script to show that. By looking
at the "aid" distribution in the "pgbench_history" table after a run, I
could check that the aid values are indeed skewed, depending on the parameters.

* Mathematical soundness

I've checked again the mathematical soundness for the methods involved.

After further thoughts, I'm not that sure that there is not a bias induced
by taking the second value based on "cos" when the first based on "sin"
as failed the test. So I removed the cos computation for the gaussian version,
and simplified the code accordingly. This mean that it may be a little
less efficient, but I'm more confident that there is no bias.

* Conclusion

If Mitsumasa-san is okay with the changes I have made, I would suggest
to accept this patch.

--
Fabien.

(2014/02/16 7:38), Fabien COELHO wrote:
> I have updated the patch (v7) based on Mitsumasa latest v6:
> - some code simplifications & formula changes.
> - I've added explicit looping probability computations in comments
> to show the (low) looping probability of the iterative search.
> - I've tried to clarify the sgml documentation.
> - I've removed the 5.0 default value as it was not used anymore.
> - I've renamed some variables to match the naming style around.
Thank you for yor detail review and fix some code! I checked your modification
version,
it seems better than previos version and very helpful for documents.

> * Mathematical soundness
>
> I've checked again the mathematical soundness for the methods involved.
>
> After further thoughts, I'm not that sure that there is not a bias induced
> by taking the second value based on "cos" when the first based on "sin"
> as failed the test. So I removed the cos computation for the gaussian version,
> and simplified the code accordingly. This mean that it may be a little
> less efficient, but I'm more confident that there is no bias.
I tried to confirm which method is better. However, at the end of the day, it is
not a problem because other part of implementations have bigger overhead in
pgbench client. We like simple implementaion so I agree with your modification
version. And I tested this version, there is no overhead in creating gaussian and
exponential random number with minimum threshold that is most overhead situation.

> * Conclusion
>
> If Mitsumasa-san is okay with the changes I have made, I would suggest
> to accept this patch.
Attached patch based on v7 is added output that is possibility of access record
when we use exponential option
in the end of pgbench result. It is caluculated by a definite integral method for
e^-x.
If you check it and think no problem, please mark it ready for commiter.
Ishii-san will review this patch:)

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Gaussian Pgbench v8 patch by Mitsumasa KONDO review & patch v9.

* The purpose of the patch is to allow a pgbench script to draw from normally
distributed or exponentially distributed integer values instead of uniformly
distributed.

This is a valuable contribution to enable pgbench to generate more realistic
loads, which is seldom uniform in practice.

* Very minor change

I have updated the patch (v9) based on Mitsumasa latest v8:
- remove one spurious space in the help message.

* Compilation

The patch applies cleanly and compiles against current head.

* Check

I have checked that the aid values are skewed depending on the
parameters by looking at the "aid" distribution in the "pgbench_history"
table after a run.

* Mathematical soundness

I've checked the mathematical soundness of the methods involved.

I'm fine with casting doubles to integers for having the expected
distribution on integers.

Although there is a retry loop for finding a suitable, the looping
probability is low thanks to the minimum threshold parameter required.

* Conclusion

I suggest to apply this patch which provide a useful and more realistic
testing capability to pgbench.

--
Fabien.

Seems that in the review so far, Fabien has focused mainly in the
mathematical properties of the new random number generation. That seems
perfectly fine, but no comment has been made about the chosen UI for the
feature. Per the few initial messages in the thread, in the patch as
submitted you ask for a gaussian random number by using \setgaussian,
and exponential via \setexp. Is this the right UI? Currently you get
an evenly distributed number with \setrandom. There is nothing that
makes it obvious on \setgaussian by itself that it produces random
numbers. Perhaps we should simply add a new argument to \setrandom,
instead of creating new commands for each distribution? I would guess
that, in the future, we're going to want other distributions as well.

Not sure what it would look like; perhaps
\setrandom foo 1 10 gaussian
or
\setrandom foo 1 10 dist=gaussian
or
\setrandom(gaussian) foo 1 10
or
\setrandom(dist=gaussian) foo 1 10

I think we could easily support

\set distrib gaussian
\setrandom(dist=:distrib) foo 1 10

so that it can be changed for a bunch of commands easily.

Or maybe I'm going overboard, everybody else is happy with \setgaussian,
and should just use that?

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

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> Seems that in the review so far, Fabien has focused mainly in the
> mathematical properties of the new random number generation. That seems
> perfectly fine, but no comment has been made about the chosen UI for the
> feature. Per the few initial messages in the thread, in the patch as
> submitted you ask for a gaussian random number by using \setgaussian,
> and exponential via \setexp. Is this the right UI? Currently you get
> an evenly distributed number with \setrandom. There is nothing that
> makes it obvious on \setgaussian by itself that it produces random
> numbers. Perhaps we should simply add a new argument to \setrandom,
> instead of creating new commands for each distribution? I would guess
> that, in the future, we're going to want other distributions as well.

+1 for an argument to \setrandom instead of separate commands.

> Not sure what it would look like; perhaps
> \setrandom foo 1 10 gaussian

FWIW, I think this style is sufficient; the others seem overcomplicated
for not much gain. I'm not strongly attached to that position though.

regards, tom lane

Hello Alvaro & Tom,

> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>> Seems that in the review so far, Fabien has focused mainly in the
>> mathematical properties of the new random number generation. That seems
>> perfectly fine, but no comment has been made about the chosen UI for the
>> feature.
>> Per the few initial messages in the thread, in the patch as submitted
>> you ask for a gaussian random number by using \setgaussian, and
>> exponential via \setexp. Is this the right UI?

I thought it would be both concise & clear to have that as another form of
\set*.

If I had it designed from the start, I think I may have put only "\set"
with some functions such as "uniform", "gaussian" and so on. but once
there is a set and a setrandom for uniform, this suggested other settings
would have their own set commands as well. Also, the number of expected
arguments is not the same, so it may make the parsing code less obvious.
Finally, this is not a "language" heavily used, so I would emphasize
simpler code over more elegant features, for once.

>> Currently you get an evenly distributed number with \setrandom. There
>> is nothing that makes it obvious on \setgaussian by itself that it
>> produces random numbers.

Well, "gaussian" or "exp" are kind of a clue, at least to my
mathematically-oriented mind.

>> Perhaps we should simply add a new argument to \setrandom, instead of
>> creating new commands for each distribution? I would guess that, in
>> the future, we're going to want other distributions as well.
>
> +1 for an argument to \setrandom instead of separate commands.

>> Not sure what it would look like; perhaps
>> \setrandom foo 1 10 gaussian

There is an additional argument expected. That would make:

\setrandom foo 1 10 [uniform]
\setrandom foo 1 :size gaussian 3.6
\setrandom foo 1 100 exponential 7.2

> FWIW, I think this style is sufficient; the others seem overcomplicated
> for not much gain. I'm not strongly attached to that position though.

If there is a change, I agree that one simple style is enough, especially
as the parsing code is rather low-level already.

So I'm basically fine with the current status of the patch, but I would
be okay with a \setrandom as well.

--
Fabien.

(2014/03/02 22:32), Fabien COELHO wrote:
>> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>>> Seems that in the review so far, Fabien has focused mainly in the
>>> mathematical properties of the new random number generation. That seems
>>> perfectly fine, but no comment has been made about the chosen UI for the
>>> feature.
>>> Per the few initial messages in the thread, in the patch as submitted you ask
>>> for a gaussian random number by using \setgaussian, and exponential via
>>> \setexp. Is this the right UI?
> I thought it would be both concise & clear to have that as another form of \set*.
Yeah, but we got only two or three? concise. So I agree with discussing about UI.

> There is an additional argument expected. That would make:
>
> \setrandom foo 1 10 [uniform]
> \setrandom foo 1 :size gaussian 3.6
> \setrandom foo 1 100 exponential 7.2
It's good design. I think it will become more low overhead at part of parsing in
pgbench, because comparison of strings will be redeced(maybe). And I'd like to
remove [uniform], beacause we have to have compatibility for old scripts, and
random function always gets uniform distribution in common sense of programming.

However, new grammer is little bit long in user script. It seems trade-off that
are visibility of scripts and user writing cost.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

>> \setrandom foo 1 10 [uniform]
>> \setrandom foo 1 :size gaussian 3.6
>> \setrandom foo 1 100 exponential 7.2
> It's good design. I think it will become more low overhead at part of parsing
> in pgbench, because comparison of strings will be redeced(maybe). And I'd
> like to remove [uniform], beacause we have to have compatibility for old
> scripts, and random function always gets uniform distribution in common sense
> of programming.

I just put "uniform" as an optional default, hence the brackets.

Otherwise, what I would have in mind if this would be designed from
scratch:

\set foo 124
\set foo "string value" (?)
\set foo :variable
\set foo 12 + :shift

And then

\set foo uniform 1 10
\set foo gaussian 1 10 4.2
\set foo exponential 1 100 5.2

or maybe functions could be repended with something like "&uniform".
But that would be for another life:-)

> However, new grammer is little bit long in user script. It seems trade-off
> that are visibility of scripts and user writing cost.

Yep.

--
Fabien.

(2014/03/03 16:51), Fabien COELHO wrote:>>> \setrandom foo 1 10 [uniform]
>>> \setrandom foo 1 :size gaussian 3.6
>>> \setrandom foo 1 100 exponential 7.2
>> It's good design. I think it will become more low overhead at part of parsing
>> in pgbench, because comparison of strings will be redeced(maybe). And I'd like
>> to remove [uniform], beacause we have to have compatibility for old scripts,
>> and random function always gets uniform distribution in common sense of
>> programming.
>
> I just put "uniform" as an optional default, hence the brackets.
All right. I was misunderstanding. However, if we select this format, I'd like to
remove it. Because pgbench needs to check counts of argment number. If we allow
brackets, it will not be simple.

> Otherwise, what I would have in mind if this would be designed from scratch:
>
> \set foo 124
> \set foo "string value" (?)
> \set foo :variable
> \set foo 12 + :shift
>
> And then
>
> \set foo uniform 1 10
> \set foo gaussian 1 10 4.2
> \set foo exponential 1 100 5.2
>
> or maybe functions could be repended with something like "&uniform".
> But that would be for another life:-)
I don't agree with that.. They are more overhead in parsing part and more complex
for user.

>> However, new grammer is little bit long in user script. It seems trade-off that
>> are visibility of scripts and user writing cost.
>
> Yep.
OK. I'm not sure which idia is the best. So I wait for comments in community:)

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

> OK. I'm not sure which idia is the best. So I wait for comments in
> community:)

Hmmm. Maybe you can do what Tom voted for, he is the committer:-)

--
Fabien.

(2014/03/04 17:28), Fabien COELHO wrote:
>> OK. I'm not sure which idia is the best. So I wait for comments in community:)
> Hmmm. Maybe you can do what Tom voted for, he is the committer:-)
Yeah, but he might change his mind by our disscuttion. So I wait untill tomorrow,
and if nothing to comment, I will start to fix what Tom voted for.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Hi,

(2014/03/04 17:42), KONDO Mitsumasa wrote:> (2014/03/04 17:28), Fabien COELHO wrote:
>>> OK. I'm not sure which idia is the best. So I wait for comments in community:)
>> Hmmm. Maybe you can do what Tom voted for, he is the committer:-)
> Yeah, but he might change his mind by our disscuttion. So I wait untill tomorrow,
> and if nothing to comment, I will start to fix what Tom voted for.
I create the patch which is fixed UI. If we agree with this interface,
I also start to fix the document.

New "\setrandom" interface is here.
\setrandom var min max [gaussian threshold | exponential threshold]

Attached patch realizes this interface, but it has little bit ugly codeing in
executeStatement() and process_commands().. That is under following.
if(argc == 4)
{
... /* uniform */
}
else if (argv[4]== gaussian or exponential)
{
... /* gaussian or exponential */
}
else
{
... /* uniform with extra argments */
}

It is beacause pgbench custom script allows extra comments or extra argument in
its file. For example, under following cases are no problem case.
\setrandom var min max #hoge --> uniform random
\setrandom var min max #hoge1 #hoge2 --> uniform random
\setrandom var min max gaussian threshold #hoge -->gaussian random

And other cases are classified under following.
\setrandom var min max gaussian #hoge --> uniform
\setrandom var min max max2 gaussian threshold --> uniform
\setrandom var min gaussian #hoge --> ERROR

However, if we wrong grammer in pgbench custom script,
pgbench outputs error log on user terminal. So I think it is especially no problem.

What do you think?

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

(2014/03/07 16:02), KONDO Mitsumasa wrote:
> And other cases are classified under following.
> \setrandom var min max gaussian #hoge --> uniform
Oh, it's wrong... It will be..
\setrandom var min max gaussian #hoge --> ERROR

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Hello Mitsumasa-san,

> New "\setrandom" interface is here.
> \setrandom var min max [gaussian threshold | exponential threshold]

> Attached patch realizes this interface, but it has little bit ugly codeing in
> executeStatement() and process_commands()..

I think it is not too bad. The "ignore extra arguments on the line" is a
little pre-existing mess anyway.

> What do you think?

I'm okay with this UI and its implementation.

--
Fabien.

(2014/03/09 1:49), Fabien COELHO wrote:
>
> Hello Mitsumasa-san,
>
>> New "\setrandom" interface is here.
>> \setrandom var min max [gaussian threshold | exponential threshold]
>
>> Attached patch realizes this interface, but it has little bit ugly codeing in
>> executeStatement() and process_commands()..
>
> I think it is not too bad. The "ignore extra arguments on the line" is a little
> pre-existing mess anyway.
All right.

>> What do you think?
>
> I'm okay with this UI and its implementation.
OK.

Attached patch is updated in the document. I don't like complex sentence,
so I　use <para> tag a lot. If you like this documents, please mark ready for
commiter.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

On Tue, Mar 11, 2014 at 1:49 PM, KONDO Mitsumasa
<kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> (2014/03/09 1:49), Fabien COELHO wrote:
>>
>>
>> Hello Mitsumasa-san,
>>
>>> New "\setrandom" interface is here.
>>> \setrandom var min max [gaussian threshold | exponential threshold]
>>
>>
>>> Attached patch realizes this interface, but it has little bit ugly
>>> codeing in
>>> executeStatement() and process_commands()..
>>
>>
>> I think it is not too bad. The "ignore extra arguments on the line" is a
>> little
>> pre-existing mess anyway.
>
> All right.
>
>
>>> What do you think?
>>
>>
>> I'm okay with this UI and its implementation.
>
> OK.

We should do the same discussion for the UI of command-line option?
The patch adds two options --gaussian and --exponential, but this UI
seems to be a bit inconsistent with the UI for \setrandom. Instead,
we can use something like --distribution=[uniform | gaussian | exponential].

Regards,

--
Fujii Masao

On 03/13/2014 03:17 PM, Fujii Masao wrote:
> On Tue, Mar 11, 2014 at 1:49 PM, KONDO Mitsumasa
> <kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> (2014/03/09 1:49), Fabien COELHO wrote:
>>>
>>> I'm okay with this UI and its implementation.
>>
>> OK.
>
> We should do the same discussion for the UI of command-line option?
> The patch adds two options --gaussian and --exponential, but this UI
> seems to be a bit inconsistent with the UI for \setrandom. Instead,
> we can use something like --distribution=[uniform | gaussian | exponential].

IMHO we should just implement the \setrandom changes, and not add any of
these options to modify the standard test workload. If someone wants to
run TPC-B workload with gaussian or exponential distribution, they can
implement it as a custom script. The docs include the script for the
standard TPC-B workload; just copy-paster that and modify the \setrandom
lines.

- Heikki

On Thu, Mar 13, 2014 at 10:51 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> On 03/13/2014 03:17 PM, Fujii Masao wrote:
>>
>> On Tue, Mar 11, 2014 at 1:49 PM, KONDO Mitsumasa
>> <kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>>
>>> (2014/03/09 1:49), Fabien COELHO wrote:
>>>>
>>>>
>>>> I'm okay with this UI and its implementation.
>>>
>>>
>>> OK.
>>
>>
>> We should do the same discussion for the UI of command-line option?
>> The patch adds two options --gaussian and --exponential, but this UI
>> seems to be a bit inconsistent with the UI for \setrandom. Instead,
>> we can use something like --distribution=[uniform | gaussian |
>> exponential].
>
>
> IMHO we should just implement the \setrandom changes, and not add any of
> these options to modify the standard test workload. If someone wants to run
> TPC-B workload with gaussian or exponential distribution, they can implement
> it as a custom script. The docs include the script for the standard TPC-B
> workload; just copy-paster that and modify the \setrandom lines.

Yeah, I'm OK with this.

Regards,

--
Fujii Masao

> We should do the same discussion for the UI of command-line option? The
> patch adds two options --gaussian and --exponential, but this UI seems
> to be a bit inconsistent with the UI for \setrandom.
> Instead, we can use something like --distribution=[uniform | gaussian |
> exponential].

Hmmm. That is possible, obviously.

Note that it does not need to resort to a custom script, if one can do
something like "--define=exp_threshold=5.6". If so, maybe one simpler
named variable could be used, say "threshold", instead of separate names
for each options.

However there is a catch: currently the option allows to check that the
threshold is large enough so as to avoid loops in the generator. So this
mean moving the check in the generator, and doing it over and over.
Possibly this is a good idea, because otherwise a custom script could
circumvent the check. Well, the current status is that the check can be
avoided with --define...

Also, a shorter possibly additional name, would be nice, maybe something
like: --dist=exp|gauss|uniform? Not sure. I like long options not to be
too long.

--
Fabien.

Hi,

(2014/03/14 4:21), Fabien COELHO wrote:
>
>> We should do the same discussion for the UI of command-line option? The patch
>> adds two options --gaussian and --exponential, but this UI seems to be a bit
>> inconsistent with the UI for \setrandom.
>> Instead, we can use something like --distribution=[uniform | gaussian |
>> exponential].
>
> Hmmm. That is possible, obviously.
>
> Note that it does not need to resort to a custom script, if one can do something
> like "--define=exp_threshold=5.6".
Yeah, threshold paramter should be needed by generating distribution algorithms
in my patch. And it is important that we can control distribution pattern by this
paramter.

> If so, maybe one simpler named variable could
> be used, say "threshold", instead of separate names for each options.
If we separate threshold option, I think it is difficult to understand dependency
of this parameter. Because "threshold" is very general term, and
when we will add other new feature, it is difficult to undestand which parameter
is dependent and be needed.

> However there is a catch: currently the option allows to check that the threshold
> is large enough so as to avoid loops in the generator. So this mean moving the
> check in the generator, and doing it over and over. Possibly this is a good idea,
> because otherwise a custom script could circumvent the check. Well, the current
> status is that the check can be avoided with --define...
>
> Also, a shorter possibly additional name, would be nice, maybe something like:
> --dist=exp|gauss|uniform? Not sure. I like long options not to be too long.
Well, if we run standard benchmark in pgbench, we need not set option because it
is default benmchmark, and it is same as uniform distribution. And if we run
extra benchmarks in pgbench which are like '-S' or '-N', we need to set option.
Because they are non-standard benchmark setting, and it is same as gaussian or
exponential distribution. So present UI keeps consistency and along the pgbench
history.

> I like long options not to be too long.
Yes, I like so too. Present UI is very simple and useful for combination using
such like '-S' and '--gaussian'. So I hope not changing UI.

ex)
pgbench -S --gaussian=5
pgbench -N --exponential=2 --sampling-rate=0.8

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

(2014/03/13 23:00), Fujii Masao wrote:
> On Thu, Mar 13, 2014 at 10:51 PM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>> On 03/13/2014 03:17 PM, Fujii Masao wrote:
>>>
>>> On Tue, Mar 11, 2014 at 1:49 PM, KONDO Mitsumasa
>>> <kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>>>
>>>> (2014/03/09 1:49), Fabien COELHO wrote:
>>>>>
>>>>>
>>>>> I'm okay with this UI and itsaccess probability of top implementation.
>>>>
>>>>
>>>> OK.
>>>
>>>
>>> We should do the same discussion for the UI of command-line option?
>>> The patch adds two options --gaussian and --exponential, but this UI
>>> seems to be a bit inconsistent with the UI for \setrandom. Instead,
>>> we can use something like --distribution=[uniform | gaussian |
>>> exponential].
>>
>>
>> IMHO we should just implement the \setrandom changes, and not add any of
>> these options to modify the standard test workload. If someone wants to run
>> TPC-B workload with gaussian or exponential distribution, they can implement
>> it as a custom script. The docs include the script for the standard TPC-B
>> workload; just copy-paster that and modify the \setrandom lines.
Well, when we set '--gaussian=NUM' or '--exponential=NUM' on command line, we can
see access probability of top N records in result of final output. This out put
is under following,

> [mitsu-ko(at)localhost pgbench]$ ./pgbench --exponential=10 postgres
> starting vacuum...end.
> transaction type: Exponential distribution TPC-B (sort of)
> scaling factor: 1
> exponential threshold: 10.00000
> access probability of top 20%, 10% and 5% records: 0.86466 0.63212 0.39347
> ~
This feature helps user to understand bias of distribution for tuning threshold
parameter.
If this feature is nothing, it is difficult to understand distribution of access
pattern, and it cannot realized on custom script. Because range of distribution
(min, max, and SQL pattern) are unknown on custom script. So I think present UI
is not bad and should not change.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

> Well, when we set '--gaussian=NUM' or '--exponential=NUM' on command line, we
> can see access probability of top N records in result of final output. This
> out put is under following,

Indeed. I had forgotten this point. This is a significant information that
I would not like to loose.

> This feature helps user to understand bias of distribution for tuning
> threshold parameter.
> If this feature is nothing, it is difficult to understand distribution of
> access pattern, and it cannot realized on custom script. Because range of
> distribution (min, max, and SQL pattern) are unknown on custom script. So I
> think present UI is not bad and should not change.

Ok. I agree with this argument.

--
Fabien.

On 03/13/2014 04:00 PM, Fujii Masao wrote:
> On Thu, Mar 13, 2014 at 10:51 PM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>> IMHO we should just implement the \setrandom changes, and not add any of
>> these options to modify the standard test workload. If someone wants to run
>> TPC-B workload with gaussian or exponential distribution, they can implement
>> it as a custom script. The docs include the script for the standard TPC-B
>> workload; just copy-paster that and modify the \setrandom lines.
>
> Yeah, I'm OK with this.

So I took a look at the \setrandom parts of this patch to see if that's
ready for commit, without any of the changes to modify the standard
TPC-B workload. Attached is a patch with just those parts; everyone
please focus on this.

A couple of comments:

* There should be an explicit "\setrandom ... uniform" option too, even
though you get that implicitly if you don't specify the distribution

* What exactly does the "threshold" mean? The docs informally explain
that "the larger the thresold, the more frequent values close to the
middle of the interval are drawn", but that's pretty vague.

* Does min and max really make sense for gaussian and exponential
distributions? For gaussian, I would expect mean and standard deviation
as the parameters, not min/max/threshold.

* How about setting the variable as a float instead of integer? Would
seem more natural to me. At least as an option.

- Heikki

Hello Heikki,

> A couple of comments:
>
> * There should be an explicit "\setrandom ... uniform" option too, even
> though you get that implicitly if you don't specify the distribution

Indeed. I agree. I suggested it, but it got lost.

> * What exactly does the "threshold" mean? The docs informally explain that
> "the larger the thresold, the more frequent values close to the middle of the
> interval are drawn", but that's pretty vague.

There are explanations and computations as comments in the code. If it is
about the documentation, I'm not sure that a very precise mathematical
definition will help a lot of people, and might rather hinder
understanding, so the doc focuses on an intuitive explanation instead.

> * Does min and max really make sense for gaussian and exponential
> distributions? For gaussian, I would expect mean and standard deviation as
> the parameters, not min/max/threshold.

Yes... and no:-) The aim is to draw an integer primary key from a table,
so it must be in a specified range. This is approximated by drawing a
double value with the expected distribution (gaussian or exponential) and
project it carefully onto integers. If it is out of range, there is a loop
and another value is drawn. The minimal threshold constraint (2.0) ensures
that the probability of looping is low.

> * How about setting the variable as a float instead of integer? Would seem
> more natural to me. At least as an option.

Which variable? The values set by setrandom are mostly used for primary
keys. We really want integers in a range.

--
Fabien.

Hi

2014-03-15 15:53 GMT+09:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Hello Heikki,
>
>
> A couple of comments:
>>
>> * There should be an explicit "\setrandom ... uniform" option too, even
>> though you get that implicitly if you don't specify the distribution
>>
>
> Indeed. I agree. I suggested it, but it got lost.

OK. If we keep to the SQL grammar, your saying is right. I will add it.

> * What exactly does the "threshold" mean? The docs informally explain
>> that "the larger the thresold, the more frequent values close to the middle
>> of the interval are drawn", but that's pretty vague.
>>
>
> There are explanations and computations as comments in the code. If it is
> about the documentation, I'm not sure that a very precise mathematical
> definition will help a lot of people, and might rather hinder
> understanding, so the doc focuses on an intuitive explanation instead.

Yeah, I think that we had better to only explain necessary infomation for
using this feature. If we add mathematical theory in docs, it will be too
difficult for user. And it's waste.

* Does min and max really make sense for gaussian and exponential
>> distributions? For gaussian, I would expect mean and standard deviation as
>> the parameters, not min/max/threshold.
>>
>
> Yes... and no:-) The aim is to draw an integer primary key from a table,
> so it must be in a specified range. This is approximated by drawing a
> double value with the expected distribution (gaussian or exponential) and
> project it carefully onto integers. If it is out of range, there is a loop
> and another value is drawn. The minimal threshold constraint (2.0) ensures
> that the probability of looping is low.

I think it is difficult to understand from our text... So I create picture
that will help you to understand it.
Please see it.

>
> * How about setting the variable as a float instead of integer? Would
>> seem more natural to me. At least as an option.
>>
>
> Which variable? The values set by setrandom are mostly used for primary
> keys. We really want integers in a range.

I think he said threshold parameter. Threshold parameter is very sensitive
parameter, so we need to set double in threshold. I think that you can
consent it when you see attached picture.

regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Oh, sorry, I forgot to write URL referring picture.

http://en.wikipedia.org/wiki/Normal_distribution
http://en.wikipedia.org/wiki/Exponential_distribution

regards,
--
Mitsumasa KONDO

2014-03-15 17:50 GMT+09:00 Mitsumasa KONDO <kondo(dot)mitsumasa(at)gmail(dot)com>:

> Hi
>
> 2014-03-15 15:53 GMT+09:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:
>
>
>> Hello Heikki,
>>
>>
>> A couple of comments:
>>>
>>> * There should be an explicit "\setrandom ... uniform" option too, even
>>> though you get that implicitly if you don't specify the distribution
>>>
>>
>> Indeed. I agree. I suggested it, but it got lost.
>
> OK. If we keep to the SQL grammar, your saying is right. I will add it.
>
>
>> * What exactly does the "threshold" mean? The docs informally explain
>>> that "the larger the thresold, the more frequent values close to the middle
>>> of the interval are drawn", but that's pretty vague.
>>>
>>
>> There are explanations and computations as comments in the code. If it is
>> about the documentation, I'm not sure that a very precise mathematical
>> definition will help a lot of people, and might rather hinder
>> understanding, so the doc focuses on an intuitive explanation instead.
>
> Yeah, I think that we had better to only explain necessary infomation for
> using this feature. If we add mathematical theory in docs, it will be too
> difficult for user. And it's waste.
>
>
> * Does min and max really make sense for gaussian and exponential
>>> distributions? For gaussian, I would expect mean and standard deviation as
>>> the parameters, not min/max/threshold.
>>>
>>
>> Yes... and no:-) The aim is to draw an integer primary key from a table,
>> so it must be in a specified range. This is approximated by drawing a
>> double value with the expected distribution (gaussian or exponential) and
>> project it carefully onto integers. If it is out of range, there is a loop
>> and another value is drawn. The minimal threshold constraint (2.0) ensures
>> that the probability of looping is low.
>
> I think it is difficult to understand from our text... So I create picture
> that will help you to understand it.
> Please see it.
>
>
>>
>> * How about setting the variable as a float instead of integer? Would
>>> seem more natural to me. At least as an option.
>>>
>>
>> Which variable? The values set by setrandom are mostly used for primary
>> keys. We really want integers in a range.
>
> I think he said threshold parameter. Threshold parameter is very sensitive
> parameter, so we need to set double in threshold. I think that you can
> consent it when you see attached picture.
>
> regards,
> --
> Mitsumasa KONDO
> NTT Open Source Software Center
>

Nice drawing!

>> * How about setting the variable as a float instead of integer? Would
>>> seem more natural to me. At least as an option.
>>
>> Which variable? The values set by setrandom are mostly used for primary
>> keys. We really want integers in a range.
>
> I think he said threshold parameter. Threshold parameter is very sensitive
> parameter, so we need to set double in threshold. I think that you can
> consent it when you see attached picture.

I'm sure that the threshold must be a double, but I thought it was already
the case, because of atof, the static variables which are declared double,
and the threshold function parameters which are declared double as well,
and the putVariable uses a "%lf" format...

Possibly I'm missing something?

--
Fabien.

2014-03-15 19:04 GMT+09:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Nice drawing!
>
>
> * How about setting the variable as a float instead of integer? Would
>>>
>>>> seem more natural to me. At least as an option.
>>>>
>>>
>>> Which variable? The values set by setrandom are mostly used for primary
>>> keys. We really want integers in a range.
>>>
>>
>> I think he said threshold parameter. Threshold parameter is very sensitive
>> parameter, so we need to set double in threshold. I think that you can
>> consent it when you see attached picture.
>
> Oh, sorry.. It is to Heikki. Not to you...

> I'm sure that the threshold must be a double, but I thought it was already
> the case, because of atof, the static variables which are declared double,
> and the threshold function parameters which are declared double as well,
> and the putVariable uses a "%lf" format...
>
I think it's collect. When we get double argument in scanf(), we can use
%lf format.

> Possibly I'm missing something?

Sorry. I think nothing is missing.

regards,
--
Mitsumasa KONDO

(2014/03/15 15:53), Fabien COELHO wrote:
>
> Hello Heikki,
>
>> A couple of comments:
>>
>> * There should be an explicit "\setrandom ... uniform" option too, even though
>> you get that implicitly if you don't specify the distribution
>
> Indeed. I agree. I suggested it, but it got lost.
>
>> * What exactly does the "threshold" mean? The docs informally explain that "the
>> larger the thresold, the more frequent values close to the middle of the
>> interval are drawn", but that's pretty vague.
>
> There are explanations and computations as comments in the code. If it is about
> the documentation, I'm not sure that a very precise mathematical definition will
> help a lot of people, and might rather hinder understanding, so the doc focuses
> on an intuitive explanation instead.
>
>> * Does min and max really make sense for gaussian and exponential
>> distributions? For gaussian, I would expect mean and standard deviation as the
>> parameters, not min/max/threshold.
>
> Yes... and no:-) The aim is to draw an integer primary key from a table, so it
> must be in a specified range. This is approximated by drawing a double value with
> the expected distribution (gaussian or exponential) and project it carefully onto
> integers. If it is out of range, there is a loop and another value is drawn. The
> minimal threshold constraint (2.0) ensures that the probability of looping is low.
>
>> * How about setting the variable as a float instead of integer? Would seem more
>> natural to me. At least as an option.
>
> Which variable? The values set by setrandom are mostly used for primary keys. We
> really want integers in a range.
Oh, I see. He said about documents.

+ Moreover, set gaussian or exponential with threshold interger value,
+ we can get gaussian or exponential random in integer value between
+ <replaceable>min</> and <replaceable>max</> bounds inclusive.

Collectry,
+ Moreover, set gaussian or exponential with threshold double value,
+ we can get gaussian or exponential random in integer value between
+ <replaceable>min</> and <replaceable>max</> bounds inclusive.

And I am going to fix the document more easily understanding for user.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

Hi Heikki-san,

(2014/03/17 14:39), KONDO Mitsumasa wrote:
> (2014/03/15 15:53), Fabien COELHO wrote:
>>
>> Hello Heikki,
>>
>>> A couple of comments:
>>>
>>> * There should be an explicit "\setrandom ... uniform" option too, even though
>>> you get that implicitly if you don't specify the distribution
Fix. We can use "\setrandom val min max uniform" without error messages.

>>> * What exactly does the "threshold" mean? The docs informally explain that "the
>>> larger the thresold, the more frequent values close to the middle of the
>>> interval are drawn", but that's pretty vague.
>>
>> There are explanations and computations as comments in the code. If it is about
>> the documentation, I'm not sure that a very precise mathematical definition will
>> help a lot of people, and might rather hinder understanding, so the doc focuses
>> on an intuitive explanation instead.
Add more detail information in the document. Is it OK? Please confirm it.

>>> * Does min and max really make sense for gaussian and exponential
>>> distributions? For gaussian, I would expect mean and standard deviation as the
>>> parameters, not min/max/threshold.
>>
>> Yes... and no:-) The aim is to draw an integer primary key from a table, so it
>> must be in a specified range. This is approximated by drawing a double value with
>> the expected distribution (gaussian or exponential) and project it carefully onto
>> integers. If it is out of range, there is a loop and another value is drawn. The
>> minimal threshold constraint (2.0) ensures that the probability of looping is low.
It make sense. Please see the attached picutre in last day.

>>> * How about setting the variable as a float instead of integer? Would seem more
>>> natural to me. At least as an option.
>>
>> Which variable? The values set by setrandom are mostly used for primary keys. We
>> really want integers in a range.
> Oh, I see. He said about documents.
The document was mistaken.
Threshold parameter must be double and fix the document.

By the way, you seem to want to remove --gaussian=NUM and --exponential=NUM
command options. Can you tell me the objective reason? I think pgbench is the
benchmark test on PostgreSQL and default benchmark is TPC-B-like benchmark.
It is written in documents, and default benchmark wasn't changed by my patch.
So we need not remove command options, and they are one of the variety of
benchmark options. Maybe you have something misunderstanding about my patch...

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

On 03/15/2014 08:53 AM, Fabien COELHO wrote:
>> >* Does min and max really make sense for gaussian and exponential
>> >distributions? For gaussian, I would expect mean and standard deviation as
>> >the parameters, not min/max/threshold.
> Yes... and no:-) The aim is to draw an integer primary key from a table,
> so it must be in a specified range.

Well, I don't agree with that aim. It's useful for choosing a primary
key, as in the pgbench TPC-B workload, but a gaussian distributed random
number could be used for many other things too. For example:

\setrandom foo ... gaussian

select * from cheese where weight > :foo

And :foo should be a float, not an integer. That's what I was trying to
say earlier, when I said that the variable should be a float. If you
need an integer, just cast or round it in the query.

I realize that the current \setrandom sets the variable to an integer,
so gaussian/exponential would be different. But so what? An option to
generate uniformly distributed floats would be handy too, though.

> This is approximated by drawing a
> double value with the expected distribution (gaussian or exponential) and
> project it carefully onto integers. If it is out of range, there is a loop
> and another value is drawn. The minimal threshold constraint (2.0) ensures
> that the probability of looping is low.

Well, that's one way to do constraint it to the given range, but there
are many other ways to do it. Like, clamp it to the min/max if it's out
of range. I don't think we need to choose any particular method, you can
handle that in the test script.

- Heikki

On 03/17/2014 10:40 AM, KONDO Mitsumasa wrote:
> By the way, you seem to want to remove --gaussian=NUM and --exponential=NUM
> command options. Can you tell me the objective reason? I think pgbench is the
> benchmark test on PostgreSQL and default benchmark is TPC-B-like benchmark.
> It is written in documents, and default benchmark wasn't changed by my patch.
> So we need not remove command options, and they are one of the variety of
> benchmark options. Maybe you have something misunderstanding about my patch...

There is an infinite number of variants of the TPC-B test that we could
include in pgbench. If we start adding every one of them, we're quickly
going to have hundreds of options to choose the workload. I'd like to
keep pgbench simple. These two new test variants, gaussian and
exponential, are not that special that they'd deserve to be included in
the program itself.

pgbench already has a mechanism for running custom scripts, in which you
can specify whatever workload you want. Let's use that. If it's missing
something you need to specify the workload you want, let's enhance the
script language.

The features we're missing, which makes it difficult to write the
gaussian and exponential variants as custom scripts, is the capability
to create random numbers with a non-uniform distribution. That's the
feature we should include in pgbench.

(Actually, you could do the Box-Muller transformation as part of the
query, to convert the uniform random variable to a gaussian one. Then
you wouldn't need any changes to pgbench. But I agree that "\setrandom
... gaussian" would be quite handy)

- Heikki

(2014/03/17 17:46), Heikki Linnakangas wrote:
> On 03/15/2014 08:53 AM, Fabien COELHO wrote:
>>> >* Does min and max really make sense for gaussian and exponential
>>> >distributions? For gaussian, I would expect mean and standard deviation as
>>> >the parameters, not min/max/threshold.
>> Yes... and no:-) The aim is to draw an integer primary key from a table,
>> so it must be in a specified range.
>
> Well, I don't agree with that aim. It's useful for choosing a primary key, as in
> the pgbench TPC-B workload, but a gaussian distributed random number could be
> used for many other things too. For example:
>
> \setrandom foo ... gaussian
>
> select * from cheese where weight > :foo
>
> And :foo should be a float, not an integer. That's what I was trying to say
> earlier, when I said that the variable should be a float. If you need an integer,
> just cast or round it in the query.
>
> I realize that the current \setrandom sets the variable to an integer, so
> gaussian/exponential would be different. But so what? An option to generate
> uniformly distributed floats would be handy too, though.
Well, it seems new feature. If you want to realise it as double, add
'\setrandomd' as a double random generator in pgbebch. I will agree with that.

>> This is approximated by drawing a
>> double value with the expected distribution (gaussian or exponential) and
>> project it carefully onto integers. If it is out of range, there is a loop
>> and another value is drawn. The minimal threshold constraint (2.0) ensures
>> that the probability of looping is low.
>
> Well, that's one way to do constraint it to the given range, but there are many
> other ways to do it. Like, clamp it to the min/max if it's out of range.
It's too heavy method.. Client calculation must be light.

> I don't
> think we need to choose any particular method, you can handle that in the test
> script.
I think our implementation is the best way to realize it.
It is fast and robustness for the probability of looping is low.

If you have better idea, please teach us.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

(2014/03/17 18:02), Heikki Linnakangas wrote:
> On 03/17/2014 10:40 AM, KONDO Mitsumasa wrote:
>> By the way, you seem to want to remove --gaussian=NUM and --exponential=NUM
>> command options. Can you tell me the objective reason? I think pgbench is the
>> benchmark test on PostgreSQL and default benchmark is TPC-B-like benchmark.
>> It is written in documents, and default benchmark wasn't changed by my patch.
>> So we need not remove command options, and they are one of the variety of
>> benchmark options. Maybe you have something misunderstanding about my patch...
>
> There is an infinite number of variants of the TPC-B test that we could include
> in pgbench. If we start adding every one of them, we're quickly going to have
> hundreds of options to choose the workload. I'd like to keep pgbench simple.
> These two new test variants, gaussian and exponential, are not that special that
> they'd deserve to be included in the program itself.
Well, I add only two options, and they are major distribution that are seen in
real database system than uniform distiribution. I'm afraid, I think you are too
worried and it will not be added hundreds of options. And pgbench is still simple.

> pgbench already has a mechanism for running custom scripts, in which you can
> specify whatever workload you want. Let's use that. If it's missing something you
> need to specify the workload you want, let's enhance the script language.
I have not seen user who is using pgbench custom script very much. And gaussian
and exponential distribution are much better to measure the real system
perfomance, so I'd like to use it command option. In now pgbench, we can only
measure about database size, but it isn't realistic situation. We want to forcast
the required system from calculating the size of hot spot or distirbution of
access pettern.

I'd realy like to include it on my heart:) Please...

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

On Mon, Mar 17, 2014 at 7:07 PM, KONDO Mitsumasa
<kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> (2014/03/17 18:02), Heikki Linnakangas wrote:
>>
>> On 03/17/2014 10:40 AM, KONDO Mitsumasa wrote:
>>>
>>> By the way, you seem to want to remove --gaussian=NUM and
>>> --exponential=NUM
>>> command options. Can you tell me the objective reason? I think pgbench is
>>> the
>>> benchmark test on PostgreSQL and default benchmark is TPC-B-like
>>> benchmark.
>>> It is written in documents, and default benchmark wasn't changed by my
>>> patch.
>>> So we need not remove command options, and they are one of the variety of
>>> benchmark options. Maybe you have something misunderstanding about my
>>> patch...
>>
>>
>> There is an infinite number of variants of the TPC-B test that we could
>> include
>> in pgbench. If we start adding every one of them, we're quickly going to
>> have
>> hundreds of options to choose the workload. I'd like to keep pgbench
>> simple.
>> These two new test variants, gaussian and exponential, are not that
>> special that
>> they'd deserve to be included in the program itself.
>
> Well, I add only two options, and they are major distribution that are seen
> in real database system than uniform distiribution. I'm afraid, I think you
> are too worried and it will not be added hundreds of options. And pgbench is
> still simple.
>
>
>> pgbench already has a mechanism for running custom scripts, in which you
>> can
>> specify whatever workload you want. Let's use that. If it's missing
>> something you
>> need to specify the workload you want, let's enhance the script language.
>
> I have not seen user who is using pgbench custom script very much. And
> gaussian and exponential distribution are much better to measure the real
> system perfomance, so I'd like to use it command option. In now pgbench, we
> can only measure about database size, but it isn't realistic situation. We
> want to forcast the required system from calculating the size of hot spot or
> distirbution of access pettern.
>
> I'd realy like to include it on my heart:) Please...

I have no strong opinion about the command-line option for gaussian,
but I think that we should focus on \setrandom gaussian first. Even
after that's committed, we can implement that commnand-line option
later if many people think that's necessary.

Regards,

--
Fujii Masao

KONDO Mitsumasa <kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> writes:
> (2014/03/17 18:02), Heikki Linnakangas wrote:
>> On 03/17/2014 10:40 AM, KONDO Mitsumasa wrote:
>> There is an infinite number of variants of the TPC-B test that we could include
>> in pgbench. If we start adding every one of them, we're quickly going to have
>> hundreds of options to choose the workload. I'd like to keep pgbench simple.
>> These two new test variants, gaussian and exponential, are not that special that
>> they'd deserve to be included in the program itself.

> Well, I add only two options, and they are major distribution that are seen in
> real database system than uniform distiribution. I'm afraid, I think you are too
> worried and it will not be added hundreds of options. And pgbench is still simple.

FWIW, I concur with Heikki on this. Adding new versions of \setrandom is
useful functionality. Embedding them in the "standard" test is not,
because that just makes it (even) less standard. And pgbench has too darn
many switches already.

regards, tom lane

On Sat, Mar 15, 2014 at 4:50 AM, Mitsumasa KONDO
<kondo(dot)mitsumasa(at)gmail(dot)com> wrote:
>> There are explanations and computations as comments in the code. If it is
>> about the documentation, I'm not sure that a very precise mathematical
>> definition will help a lot of people, and might rather hinder understanding,
>> so the doc focuses on an intuitive explanation instead.
>
> Yeah, I think that we had better to only explain necessary infomation for
> using this feature. If we add mathematical theory in docs, it will be too
> difficult for user. And it's waste.

Well, if you *don't* include at least *some* mathematical description
of what the feature does in the documentation, then users who need to
understand it will have to read the source code to figure it out,
which is going to be even more difficult.

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

(2014/03/17 22:37), Tom Lane wrote:
> KONDO Mitsumasa <kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> writes:
>> (2014/03/17 18:02), Heikki Linnakangas wrote:
>>> On 03/17/2014 10:40 AM, KONDO Mitsumasa wrote:
>>> There is an infinite number of variants of the TPC-B test that we could include
>>> in pgbench. If we start adding every one of them, we're quickly going to have
>>> hundreds of options to choose the workload. I'd like to keep pgbench simple.
>>> These two new test variants, gaussian and exponential, are not that special that
>>> they'd deserve to be included in the program itself.
>
>> Well, I add only two options, and they are major distribution that are seen in
>> real database system than uniform distiribution. I'm afraid, I think you are too
>> worried and it will not be added hundreds of options. And pgbench is still simple.
>
> FWIW, I concur with Heikki on this. Adding new versions of \setrandom is
> useful functionality. Embedding them in the "standard" test is not,
> because that just makes it (even) less standard. And pgbench has too darn
> many switches already.
Hmm, I cooled down and see the pgbench option. I can understand his arguments,
there are many sitches already and it will become more largear options unless we
stop adding new option. However, I think that the man who added the option in
the past thought the option will be useful for PostgreSQL performance
improvement. But now, they are disturb the new option such like my feature which
can create more real system benchmark distribution. I think it is very
unfortunate and also tending to stop progress of improvement of PostgreSQL
performance, not only pgbench. And if we remove command line option, I think new
feature will tend to reject. It is not also good.

By the way, if we remove command line option, it is difficult to understand
distirbution of gaussian, because threshold parameter is very sensitive and it is
also very useful feature. It is difficult and taking labor that analyzing and
visualization pgbench_history using SQL.

What do you think about this problem? This is not disscussed yet.

> [mitsu-ko(at)pg-rex31 pgbench]$ ./pgbench --gaussian=2
> ~
> access probability of top 20%, 10% and 5% records: 0.32566 0.16608 0.08345
> ~
> [mitsu-ko(at)pg-rex31 pgbench]$ ./pgbench --gaussian=4
> ~
> access probability of top 20%, 10% and 5% records: 0.57633 0.31086 0.15853
> ~
> [mitsu-ko(at)pg-rex31 pgbench]$ ./pgbench --gaussian=10
> ~
> access probability of top 20%, 10% and 5% records: 0.95450 0.68269 0.38292
> ~

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

(2014/03/17 23:29), Robert Haas wrote:
> On Sat, Mar 15, 2014 at 4:50 AM, Mitsumasa KONDO
> <kondo(dot)mitsumasa(at)gmail(dot)com> wrote:
>>> There are explanations and computations as comments in the code. If it is
>>> about the documentation, I'm not sure that a very precise mathematical
>>> definition will help a lot of people, and might rather hinder understanding,
>>> so the doc focuses on an intuitive explanation instead.
>>
>> Yeah, I think that we had better to only explain necessary infomation for
>> using this feature. If we add mathematical theory in docs, it will be too
>> difficult for user. And it's waste.
>
> Well, if you *don't* include at least *some* mathematical description
> of what the feature does in the documentation, then users who need to
> understand it will have to read the source code to figure it out,
> which is going to be even more difficult.
I had fixed this problem. Please see the v12 patch. I think it doesn't includ
mathematical
description, but user will be able to understand intuitive from the explanation
of document.
Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

And I find new useful point of this feature. Under following results are
'--gaussian=20' case and '--gaussian=2' case, and postgresql setting is same.

> [mitsu-ko(at)pg-rex31 pgbench]$ ./pgbench -c8 -j4 --gaussian=20 -T30 -P 5
> starting vacuum...end.
> progress: 5.0 s, 4285.8 tps, lat 1.860 ms stddev 0.425
> progress: 10.0 s, 4249.2 tps, lat 1.879 ms stddev 0.372
> progress: 15.0 s, 4230.3 tps, lat 1.888 ms stddev 0.430
> progress: 20.0 s, 4247.3 tps, lat 1.880 ms stddev 0.400
> LOG: checkpoints are occurring too frequently (12 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> progress: 25.0 s, 4269.0 tps, lat 1.870 ms stddev 0.427
> progress: 30.0 s, 4318.1 tps, lat 1.849 ms stddev 0.415
> transaction type: Gaussian distribution TPC-B (sort of)
> scaling factor: 10
> standard deviation threshold: 20.00000
> access probability of top 20%, 10% and 5% records: 0.99994 0.95450 0.68269
> query mode: simple
> number of clients: 8
> number of threads: 4
> duration: 30 s
> number of transactions actually processed: 128008
> latency average: 1.871 ms
> latency stddev: 0.412 ms
> tps = 4266.266374 (including connections establishing)
> tps = 4267.312022 (excluding connections establishing)

> [mitsu-ko(at)pg-rex31 pgbench]$ ./pgbench -c8 -j4 --gaussian=2 -T30 -P 5
> starting vacuum...end.
> LOG: checkpoints are occurring too frequently (13 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (1 second apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> progress: 5.0 s, 3927.9 tps, lat 2.030 ms stddev 0.691
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (1 second apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> progress: 10.0 s, 4045.8 tps, lat 1.974 ms stddev 0.835
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (1 second apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> progress: 15.0 s, 4042.5 tps, lat 1.976 ms stddev 0.613
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> progress: 20.0 s, 4103.9 tps, lat 1.946 ms stddev 0.540
> LOG: checkpoints are occurring too frequently (1 second apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> progress: 25.0 s, 4003.1 tps, lat 1.995 ms stddev 0.526
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (1 second apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> LOG: checkpoints are occurring too frequently (2 seconds apart)
> HINT: Consider increasing the configuration parameter "checkpoint_segments".
> progress: 30.0 s, 4025.5 tps, lat 1.984 ms stddev 0.568
> transaction type: Gaussian distribution TPC-B (sort of)
> scaling factor: 10
> standard deviation threshold: 2.00000
> access probability of top 20%, 10% and 5% records: 0.32566 0.16608 0.08345
> query mode: simple
> number of clients: 8
> number of threads: 4
> duration: 30 s
> number of transactions actually processed: 120752
> latency average: 1.984 ms
> latency stddev: 0.638 ms
> tps = 4024.823433 (including connections establishing)
> tps = 4025.777787 (excluding connections establishing)

In '--gaussian=2' benchmark, checkpoint is frequently happen than '--gaussian=20'
benchmark. Because former update large range of records
so that fullpage write WALs are bigger than later. Former distribution updates
large range of records, so that fullpage-write WALs are bigger than later
distribution. Such benchmark was not able to come out by the past pgbench at all.

I think that this feature will be also useful for survey new buffer-replace
algorithm and checkpoint strategy, so on. If we remove this option, it is really
dissapointed..

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

On 03/18/2014 11:57 AM, KONDO Mitsumasa wrote:
> I think that this feature will be also useful for survey new buffer-replace
> algorithm and checkpoint strategy, so on.

Sure. No doubt about that.

> If we remove this option, it is really dissapointed..

As long as we get the \setrandom changes in, you can easily do these
tests using a custom script. There's nothing wrong with using a custom
script, it will be just as useful for exploring buffer replacement
algorithms, checkpoints etc. as a built-in option.

- Heikki

Please find attached an updated version "v13" for this patch.

I have (I hope) significanlty improved the documentation, including not so
helpful mathematical explanation about the actual meaning of the threshold
value. If a native English speaker could check the documentation, it would
be nice!

I have improved the implementation of the exponential distribution so as
to avoid a loop, which allows to lift the minimum threshold value
constraint, and the exponential pgbench summary displays decile and
first/last percent drawing probabilities. However, the same simplification
cannot be applied on the gaussian distribution part which must rely on a
loop, thus needs a minimal threshold for performance. I have also checked
(see the 4 attached scripts) the actual distribution against the computed
probabilities.

I disagree with the suggestion to remove the included gaussian &
exponential tests variants, because (1) it would mean removing the
specific summaries as well, which are essential to help feel how the
feature works; (2) the corresponding code in the source is rather
straightforward; (3) the tests correspond to the schema and data created
with -i, so it makes sense that they are stored in pgbench; (4) in order
for this feature to be used, it is best that it is available directly and
simply from pgbench, and not to be thought for elsewhere.

If this is a commit blocker, then the embedded script will have to be
removed, but I really think that they add a significant value to pgbench
and its "non uniform" features because they make it easy to test.

If Mitsumasa-san aggrees with these proposed changes, I would suggest to
apply this patch.

--
Fabien