Re: How much do the hint bits help?

On Wed, Dec 22, 2010 at 4:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Regarding the contention which Tom expects: the extra load on the CLOG
>> would be 100% reads, no?  If it's *all* reads, why would we have any
>> more contention than we have now?
>
> Read involves sharelock which still causes contention.  Those bufmgr
> contention storms we saw before were completely independent of whether
> the pages were accessed for read or for write.
>
> Another thing to keep in mind is that the current clog access code is
> designed on the assumption that there's considerable locality of access
> to pg_clog, ie, you usually only need to consult it for recent XIDs
> because older ones have been hinted.  Turn off hint bits, that behavior
> goes out the window.

That's not always going to be the case though. In olap-ish
environments you will see cases of scans over many records that come
from a single transaction. This is also the case where hint bits can
really drill you -- you insert a bunch of records, log the bits,
delete, log the bits, and vacuum eventually. I started investigating
this on behalf of a friend who is experiencing basically the worst
case with regularity.

merlin