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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
