Re: Not HOT enough

On Wed, Nov 23, 2011 at 8:45 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I've wondered a few times whether we could get rid of the
> RecentGlobalXmin computation from GetSnapshotData() altogether.

You have to calculate an xmin, so its unavoidable.

My patch actually improves the speed of snapshots, rather than slowing
them as Tom's would.

>  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.

When we prune a page while running an UPDATE if we see that the page
is left with less freespace than average row length for that relation
AND page sees a RecentlyDead xid we could then re-derive a later
db-local cutoff value and re-prune the page.

That increases page lock time, but pages are locked for longer if we
do non-HOT updates anyway, so it would still be a win.

What % of non-HOT updates do you see in your recent benchmarks?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services