Re: Speed up Clog Access by increasing CLOG buffers

On Fri, Oct 7, 2016 at 3:02 PM, Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
> I got access to a large machine with 72/144 cores (thanks to Oleg and
> Alexander from Postgres Professional), and I'm running the tests on that
> machine too.
>
> Results from Dilip's workload (with scale 300, unlogged tables) look like
> this:
>
> 32 64 128 192 224 256 288
> master 104943 128579 72167 100967 66631 97088 63767
> granular-locking 103415 141689 83780 120480 71847 115201 67240
> group-update 105343 144322 92229 130149 81247 126629 76638
> no-content-lock 103153 140568 80101 119185 70004 115386 66199
>
> So there's some 20-30% improvement for >= 128 clients.
>

So here we see performance improvement starting at 64 clients, this is
somewhat similar to what Dilip saw in his tests.

> But what I find much more intriguing is the zig-zag behavior. I mean, 64
> clients give ~130k tps, 128 clients only give ~70k but 192 clients jump up
> to >100k tps again, etc.
>

No clear answer.

> FWIW I don't see any such behavior on pgbench, and all those tests were done
> on the same cluster.
>
>>> With 4.5.5, results for the same benchmark look like this:
>>>
>>> 64 128 192
>>> ------------------------------------------------
>>> master 35693 39822 42151
>>> granular-locking 35370 39409 41353
>>> no-content-lock 36201 39848 42407
>>> group-update 35697 39893 42667
>>>
>>> That seems like a fairly bad regression in kernel, although I have not
>>> identified the feature/commit causing it (and it's also possible the
>>> issue
>>> lies somewhere else, of course).
>>>
>>> With regular pgbench, I see no improvement on any kernel version. For
>>> example on 3.19 the results look like this:
>>>
>>> 64 128 192
>>> ------------------------------------------------
>>> master 54661 61014 59484
>>> granular-locking 55904 62481 60711
>>> no-content-lock 56182 62442 61234
>>> group-update 55019 61587 60485
>>>
>>
>> Are the above results with synchronous_commit=off?
>>
>
> No, but I can do that.
>
>>> I haven't done much more testing (e.g. with -N to eliminate
>>> collisions on branches) yet, let's see if it changes anything.
>>>
>>
>> Yeah, let us see how it behaves with -N. Also, I think we could try
>> at higher scale factor?
>>
>
> Yes, I plan to do that. In total, I plan to test combinations of:
>
> (a) Dilip's workload and pgbench (regular and -N)
> (b) logged and unlogged tables
> (c) scale 300 and scale 3000 (both fits into RAM)
> (d) sync_commit=on/off
>

sounds sensible.

Thanks for doing the tests.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com