Re: [WIP] Zipfian distribution in pgbench

Hello Alik,

> PostgreSQL shows very bad results in YCSB Workload A (50% SELECT and 50%
> UPDATE of random row by PK) on benchmarking with big number of clients
> using Zipfian distribution. MySQL also has decline but it is not
> significant as it is in PostgreSQL. MongoDB does not have decline at
> all. And if pgbench would have Zipfian distribution random number
> generator, everyone will be able to make research on this topic without
> using YCSB.

Your description is not very precise. What version of Postgres is used? If
there is a decline, compared to which version? Is there a link to these
results?

> This is the reason why I am currently working on random_zipfian function.

> The bottleneck of algorithm that I use is that it calculates zeta
> function (it has linear complexity -
> https://en.wikipedia.org/wiki/Riemann_zeta_function). It my cause
> problems on generating huge amount of big numbers.

Indeed, the function computation is over expensive, and the numerical
precision of the implementation is doubtful.

If there is no better way to compute this function, ISTM that it should be
summed in reverse order to accumulate small values first, from (1/n)^s +
... + (1/2)^ s. As 1/1 == 1, the corresponding term is 1, no point in
calling pow for this one, so it could be:

double ans = 0.0;
for (i = n; i >= 2; i--)
ans += pow(1. / i, theta);
return 1.0 + ans;

> That’s why I added caching for zeta value. And it works good for cases
> when random_zipfian called with same parameters in script. For example:

> That’s why I have a question: should I implement support of caching zeta
> values for calls with different parameters, or not?

I do not envision the random_zipfian function to be used widely, but if it
is useful to someone this is fine with me. Could an inexpensive
exponential distribution be used instead for the same benchmarking
purpose?

If the functions when actually used is likely to be called with different
parameters, then some caching beyond the last value would seem in order.
Maybe a small fixed size array?

However, it should be somehow thread safe, which does not seem to be the
case with the current implementation. Maybe a per-thread cache? Or use a
lock only to update a shared cache? At least it should avoid locking to
read values...

> P.S. I attaching patch and script - analogue of YCSB Workload A.
> Run benchmark with command:
> $ pgbench -f ycsb_read_zipf.sql -f ycsb_update_zipf.sql

Given the explanations, the random draw mostly hits values at the
beginning of the interval, so when the number of client goes higher one
just get locking contention on the updated row?

ISTM that also having the tps achieved with a flat distribution would
allow to check this hypothesis.

> On scale = 10(1 million rows) it gives following results on machine with
> 144 cores(with synchronous_commit=off):

> nclients tps
> 1 8842.401870
> 2 18358.140869
> 4 45999.378785
> 8 88713.743199
> 16 170166.998212
> 32 290069.221493
> 64 178128.030553
> 128 88712.825602
> 256 38364.937573
> 512 13512.765878
> 1000 6188.136736

--
Fabien.