Re: Speed up Clog Access by increasing CLOG buffers

Hi,

On 09/19/2016 09:10 PM, Robert Haas wrote:
>
> It's possible that the effect of this patch depends on the number of
> sockets. EDB test machine cthulhu as 8 sockets, and power2 has 4
> sockets. I assume Dilip's tests were run on one of those two,
> although he doesn't seem to have mentioned which one. Your system is
> probably 2 or 4 sockets, which might make a difference. Results
> might also depend on CPU architecture; power2 is, unsurprisingly, a
> POWER system, whereas I assume you are testing x86. Maybe somebody
> who has access should test on hydra.pg.osuosl.org, which is a
> community POWER resource. (Send me a private email if you are a known
> community member who wants access for benchmarking purposes.)
>

Yes, I'm using x86 machines:

1) large but slightly old
- 4 sockets, e5-4620 (so a bit old CPU, 32 cores in total)
- kernel 3.2.80

2) smaller but fresh
- 2 sockets, e5-2620 v4 (newest type of Xeons, 16 cores in total)
- kernel 4.8.0

> Personally, I find the results so far posted on this thread
> thoroughly unimpressive. I acknowledge that Dilip's results appear
> to show that in a best-case scenario these patches produce a rather
> large gain. However, that gain seems to happen in a completely
> contrived scenario: astronomical client counts, unlogged tables, and
> a test script that maximizes pressure on CLogControlLock. If you
> have to work that hard to find a big win, and tests under more
> reasonable conditions show no benefit, it's not clear to me that it's
> really worth the time we're all spending benchmarking and reviewing
> this, or the risk of bugs, or the damage to the SLRU abstraction
> layer. I think there's a very good chance that we're better off
> moving on to projects that have a better chance of helping in the
> real world.

I'm posting results from two types of workloads - traditional r/w
pgbench and Dilip's transaction. With synchronous_commit on/off.

Full results (including script driving the benchmark) are available
here, if needed:

https://bitbucket.org/tvondra/group-clog-benchmark/src

It'd be good if someone could try reproduce this on a comparable
machine, to rule out my stupidity.

2 x e5-2620 v4 (16 cores, 32 with HT)
=====================================

On the "smaller" machine the results look like this - I have only tested
up to 64 clients, as higher values seem rather uninteresting on a
machine with only 16 physical cores.

These are averages of 5 runs, where the min/max for each group are
within ~5% in most cases (see the "spread" sheet). The "e5-2620" sheet
also shows the numbers as % compared to master.

dilip / sync=off 1 4 8 16 32 64
----------------------------------------------------------------------
master 4756 17672 35542 57303 74596 82138
granular-locking 4745 17728 35078 56105 72983 77858
no-content-lock 4646 17650 34887 55794 73273 79000
group-update 4582 17757 35383 56974 74387 81794

dilip / sync=on 1 4 8 16 32 64
----------------------------------------------------------------------
master 4819 17583 35636 57437 74620 82036
granular-locking 4568 17816 35122 56168 73192 78462
no-content-lock 4540 17662 34747 55560 73508 79320
group-update 4495 17612 35474 57095 74409 81874

pgbench / sync=off 1 4 8 16 32 64
----------------------------------------------------------------------
master 3791 14368 27806 43369 54472 62956
granular-locking 3822 14462 27597 43173 56391 64669
no-content-lock 3725 14212 27471 43041 55431 63589
group-update 3895 14453 27574 43405 56783 62406

pgbench / sync=on 1 4 8 16 32 64
----------------------------------------------------------------------
master 3907 14289 27802 43717 56902 62916
granular-locking 3770 14503 27636 44107 55205 63903
no-content-lock 3772 14111 27388 43054 56424 64386
group-update 3844 14334 27452 43621 55896 62498

There's pretty much no improvement at all - most of the results are
within 1-2% of master, in both directions. Hardly a win.

Actually, with 1 client there seems to be ~5% regression, but it might
also be noise and verifying it would require further testing.

4 x e5-4620 v1 (32 cores, 64 with HT)
=====================================

These are averages of 10 runs, and there are a few strange things here.

Firstly, for Dilip's workload the results get much (much) worse between
64 and 128 clients, for some reason. I suspect this might be due to
fairly old kernel (3.2.80), so I'll reboot the machine with 4.5.x kernel
and try again.

Secondly, the min/max differences get much larger than the ~5% on the
smaller machine - with 128 clients, the (max-min)/average is often
>100%. See the "spread" or "spread2" sheets in the attached file.

But for some reason this only affects Dilip's workload, and apparently
the patches make it measurably worse (master is ~75%, patches ~120%). If
you look at tps for individual runs, there's usually 9 runs with almost
the same performance, and then one or two much faster runs. Again, the
pgbench seems not to have this issue.

I have no idea what's causing this - it might be related to the kernel,
but I'm not sure why it should affect the patches differently. Let's see
how the new kernel affects this.

dilip / sync=off 16 32 64 128 192
--------------------------------------------------------------
master 26198 37901 37211 14441 8315
granular-locking 25829 38395 40626 14299 8160
no-content-lock 25872 38994 41053 14058 8169
group-update 26503 38911 42993 19474 8325

dilip / sync=on 16 32 64 128 192
--------------------------------------------------------------
master 26138 37790 38492 13653 8337
granular-locking 25661 38586 40692 14535 8311
no-content-lock 25653 39059 41169 14370 8373
group-update 26472 39170 42126 18923 8366

pgbench / sync=off 16 32 64 128 192
--------------------------------------------------------------
master 23001 35762 41202 31789 8005
granular-locking 23218 36130 42535 45850 8701
no-content-lock 23322 36553 42772 47394 8204
group-update 23129 36177 41788 46419 8163

pgbench / sync=on 16 32 64 128 192
--------------------------------------------------------------
master 22904 36077 41295 35574 8297
granular-locking 23323 36254 42446 43909 8959
no-content-lock 23304 36670 42606 48440 8813
group-update 23127 36696 41859 46693 8345

So there is some improvement due to the patches for 128 clients (+30% in
some cases), but it's rather useless as 64 clients either give you
comparable performance (pgbench workload) or way better one (Dilip's
workload).

Also, pretty much no difference between synchronous_commit on/off,
probably thanks to running on unlogged tables.

I'll repeat the test on the 4-socket machine with a newer kernel, but
that's probably the last benchmark I'll do for this patch for now. I
agree with Robert that the cases the patch is supposed to improve are a
bit contrived because of the very high client counts.

IMHO to continue with the patch (or even with testing it), we really
need a credible / practical example of a real-world workload that
benefits from the patches. The closest we have to that is Amit's
suggestion someone hit the commit lock when running HammerDB, but we
have absolutely no idea what parameters they were using, except that
they were running with synchronous_commit=off. Pgbench shows no such
improvements (at least for me), at least with reasonable parameters.

regards

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