Re: Not HOT enough

On Wed, Nov 23, 2011 at 3:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Nov 23, 2011 at 1:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> What I think might make more sense is to keep two variables,
>>> RecentGlobalXmin with its current meaning and RecentDatabaseWideXmin
>>> which considers only xmins of transactions in the current database.
>>> Then HOT cleanup could select the appropriate cutoff depending on
>>> whether it's working on a shared or non-shared relation.
>
>> Unfortunately, that would have the effect of lengthening the time for
>> which ProcArrayLock is held, and as benchmark results from Pavan's
>> patch in that area show, that makes a very big difference to total
>> throughput on write-heavy workloads.
>
> [ shrug... ]  Simon's patch already adds nearly as many cycles in the
> hot spot as would be required to do what I suggest.

Well, that's a point in favor of your idea as compared with Simon's, I
suppose, but it's not making me feel entirely sanguine about either
approach.

I've wondered a few times whether we could get rid of the
RecentGlobalXmin computation from GetSnapshotData() altogether. We
think that it's cheap to do it there because we already hold
ProcArrayLock in exclusive mode, but Pavan's work suggests that it
really isn't that cheap. Instead of updating RecentGlobalXmin every
time we take a snapshot (which is likely to be a waste in many cases,
since even in a busy system many snapshots are very short lived and
therefore unlikely to trigger a HOT cleanup) maybe we should only
update it "on demand" - e.g. if heap_page_prune_opt sees a
page-prune-hint XID that is older than TransactionXmin and newer than
the last-computed value of RecentGlobalXmin, there's hope that a
recomputation might yield a new RecentGlobalXmin value new enough to
allow a HOT cleanup, so if we haven't recomputed it "lately", then we
should.

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