Re: Speed up Clog Access by increasing CLOG buffers

On 09/28/2016 05:39 PM, Robert Haas wrote:
> On Tue, Sep 27, 2016 at 5:15 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>> So, I got the results from 3.10.101 (only the pgbench data), and it looks
>> like this:
>>
>> 3.10.101 1 8 16 32 64 128 192
>> --------------------------------------------------------------------
>> granular-locking 2582 18492 33416 49583 53759 53572 51295
>> no-content-lock 2580 18666 33860 49976 54382 54012 51549
>> group-update 2635 18877 33806 49525 54787 54117 51718
>> master 2630 18783 33630 49451 54104 53199 50497
>>
>> So 3.10.101 performs even better tnan 3.2.80 (and much better than 4.5.5),
>> and there's no sign any of the patches making a difference.
>
> I'm sure that you mentioned this upthread somewhere, but I can't
> immediately find it. What scale factor are you testing here?
>

300, the same scale factor as Dilip.

>
> It strikes me that the larger the scale factor, the more
> CLogControlLock contention we expect to have. We'll pretty much do
> one CLOG access per update, and the more rows there are, the more
> chance there is that the next update hits an "old" row that hasn't
> been updated in a long time. So a larger scale factor also
> increases the number of active CLOG pages and, presumably therefore,
> the amount of CLOG paging activity.
>

So, is 300 too little? I don't think so, because Dilip saw some benefit
from that. Or what scale factor do we think is needed to reproduce the
benefit? My machine has 256GB of ram, so I can easily go up to 15000 and
still keep everything in RAM. But is it worth it?

regards

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