Re: CLOG contention

On Wed, Dec 21, 2011 at 10:51 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Any thoughts on what makes most sense here?  I find it fairly
>> tempting to just crank up NUM_CLOG_BUFFERS and call it good,
>
> The only thought I have to add to discussion so far is that the need
> to do anything may be reduced significantly by any work to write
> hint bits more aggressively.  We only consult CLOG for tuples on
> which hint bits have not yet been set, right?  What if, before
> writing a page, we try to set hint bits where we can?  When
> successful, it would not only prevent one or more later writes of
> the page, but could also prevent having to load old CLOG pages.
> Perhaps the hint bit issue should be addressed first, and *then* we
> check whether we still have a problem with CLOG.

There may be workloads where that will help, but it's definitely not
going to cover all cases. Consider my trusty
pgbench-at-scale-factor-100 test case: since the working set fits
inside shared buffers, we're only writing pages at checkpoint time.
The contention happens because we randomly select rows from the table,
and whatever row we select hasn't been examined since it was last
updated, and so it's unhinted. But we're not reading the page in:
it's already in shared buffers, and has never been written out. I
don't see any realistic way to avoid the CLOG lookups in that case:
nobody else has had any reason to touch that page in any way since the
tuple was first written.

So I think we need a more general solution.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company