Re: [Patch][WiP] Tweaked LRU for shared buffers

On 2/17/19 2:14 PM, Едигарьев, Иван Григорьевич wrote:
> Hi there. I was responsible for the benchmarks, and I would be glad to
> make clear that part for you.
>
> On Sat, 16 Feb 2019 at 02:30, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>> Interesting. Where do these numbers (5/8 and 1/8) come from?
>
> The first number came from MySQL realization of LRU algorithm
> <https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html>
> and the second from simple tuning, we've tried to change 1/8 a little,
> but it didn't change metrics significantly.
>
>> That TPS chart looks a bit ... wild. How come the master jumps so much
>> up and down? That's a bit suspicious, IMHO.
>
> Yes, it is. It would be great if someone will try to reproduce those results.
>

I'll try.

>> How do I reproduce this benchmark? I'm aware of pg_ycsb, but maybe
>> you've used some other tools?
>
> Yes, we used pg_ycsb, but nothing more than that and pgbench, it's
> just maybe too simple. I attach the example of the sh script that has
> been used to generate database and measure each point on the chart.
>

OK. I see the measurement script is running plain select-only test (so
with uniform distribution). So how did you do the zipfian test?

Also, I see the test is resetting all stats regularly - that may not be
quite a good thing, because it also resets stats used by autovacuum for
example. It's better to query the values before the run, and compute the
delta. OTOH it should affect all tests about the same, I guess.

> Build was generated without any additional debug flags in
> configuration. Database was mad by initdb with --data-checksums
> enabled and generated by initialization step in pgbench with
> --scale=11000.
>

Sounds reasonable.

>> Also, have you tried some other benchmarks (like, regular TPC-B as
>> implemented by pgbench, or read-only pgbench)? We need such benchmarks
>> with a range of access patterns to check for regressions.
>
> Yes, we tried all builtin pgbench benchmarks and YCSB-A,B,C from
> pg_ycsb with unoform and zipfian distribution. I also attach some
> other charts that we did, they are not as statistically significant as
> could because we used less time in them but hope that it will help.
>

OK. Notice that the random_zipfian function is quite expensive in terms
of CPU, and it may significantly skew the results particularly with
large data sets / short runs. I've posted a message earlier, as I ran
into the issue while trying to reproduce the behavior.

>> BTW what do you mean by "sampling"?
>
> I meant that we measure tps and hit rate on several virtual machines
> for our and master build in order to neglect the influence that came
> from the difference between them.
>

OK

>>> We used this config: [2]
>>>
>>
>> That's only half the information - it doesn't say how many clients were
>> running the benchmark etc.
>
> Yes, sorry for that missing, we've had virtual machines with
> configuration mentioned in the initial letter, with 16 jobs and 16
> clients in pgbench configuration.
>
> [0] Scripts https://yadi.sk/d/PHICP0N6YrN5Cw
> [1] Measurements for other workloads https://yadi.sk/d/6G0e09Drf0ygag
>
> I will be looking forward if you have any other questions about
> measurement or code. Please note me if you have them.
>

Thanks. I'll try running some tests on my machines, I'll see if I manage
to reproduce the suspicious behavior.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services