Re: Speed up Clog Access by increasing CLOG buffers - Mailing list pgsql-hackers

From Tomas Vondra
Subject Re: Speed up Clog Access by increasing CLOG buffers
Date
Msg-id e654de76-0aaa-2873-e105-b6a358e59894@2ndquadrant.com
Whole thread Raw
In response to Re: Speed up Clog Access by increasing CLOG buffers  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: Speed up Clog Access by increasing CLOG buffers
List pgsql-hackers
On 09/28/2016 05:39 PM, Robert Haas wrote:
> On Tue, Sep 27, 2016 at 5:15 PM, Tomas Vondra
> <tomas.vondra@2ndquadrant.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



pgsql-hackers by date:

Previous
From: David Steele
Date:
Subject: Re: Fix checkpoint skip logic on idle systems by tracking LSN progress
Next
From: Jeff Janes
Date:
Subject: Re: Better tracking of free space during SP-GiST index build